dst-join-analysis
Perform SQL joins and multi-table analysis on DST data in DuckDB. Use when research requires combining multiple tables on common dimensions (time, region). Provides patterns for common DST dimension joins and multi-table comparisons.
- Category: discovered
- Tags: github, community
- Version: 1.0.0
- Author: walid
- Downloads: 0
- Updated: 2025-11-22
Download the latest version (v1.0.0) from this page.
SKILL.md
---
name: dst-join-analysis
description: |
Perform SQL joins and multi-table analysis on DST data in DuckDB. Use when
research requires combining multiple tables on common dimensions (time, region).
Provides patterns for common DST dimension joins and multi-table comparisons.
---
# DST Multi-Table Join Analysis
Combine and analyze multiple Danmarks Statistik tables stored in DuckDB.
## Common DST Dimensions
DST tables often share these dimensions:
### 1. Time Dimensions
- `tid` - Time period (year, quarter, month)
- Common formats: '2023', '2023K1', '2023M01'
- All tables with time series can join on this
### 2. Regional Dimensions
- `område` - Geographic area (hele landet, regions, municipalities)
- `region` - Region codes
- `kommune` - Municipality codes
### 3. Demographic Dimensions
- `køn` - Gender (M/K/Total)
- `alder` - Age groups
- `herkomst` - Origin/ethnicity
## Join Patterns
### Pattern 1: Time-Series Join
When both tables have time dimension:
```sql
-- Example: Join population (FOLK1A) with births (FOD)
SELECT
f.tid AS year,
f.indhold AS population,
b.indhold AS births,
ROUND(b.indhold::FLOAT / f.indhold * 1000, 2) AS birth_rate_per_1000
FROM dst_folk1a f
INNER JOIN dst_fod b ON f.tid = b.tid
WHERE f.område = 'Hele landet'
AND b.område = 'Hele landet'
ORDER BY f.tid;
```
**Use when**: Comparing two time-series indicators
### Pattern 2: Regional Comparison
When both tables have regional breakdown:
```sql
-- Example: Compare population (FOLK1A) across regions
SELECT
t1.område AS region,
t1.tid AS year,
t1.indhold AS population_2020,
t2.indhold AS population_2023,
ROUND((t2.indhold::FLOAT - t1.indhold) / t1.indhold * 100, 2) AS growth_pct
FROM dst_folk1a t1
INNER JOIN dst_folk1a t2
ON t1.område = t2.område
WHERE t1.tid = '2020'
AND t2.tid = '2023'
AND t1.område != 'Hele landet'
ORDER BY growth_pct DESC;
```
**Use when**: Comparing regions across same indicator
### Pattern 3: Multi-Indicator Analysis
When joining different indicators by time and region:
```sql
-- Example: Correlate unemployment with business bankruptcies
SELECT
u.tid AS year,
u.indhold AS unemployment_rate,
b.indhold AS bankruptcies,
ROUND(b.indhold::FLOAT / u.indhold, 2) AS bankruptcies_per_unemployed
FROM dst_unemployment u
INNER JOIN dst_bankruptcies b
ON u.tid = b.tid
AND u.område = b.område
WHERE u.område = 'Hele landet'
ORDER BY u.tid;
```
**Use when**: Exploring relationships between different indicators
### Pattern 4: Aggregate Join
When one table is at higher granularity:
```sql
-- Example: Join total population with regional breakdown
SELECT
total.tid AS year,
total.indhold AS total_population,
region.område AS region,
region.indhold AS region_population,
ROUND(region.indhold::FLOAT / total.indhold * 100, 2) AS pct_of_total
FROM dst_folk1a total
INNER JOIN dst_folk1a region
ON total.tid = region.tid
WHERE total.område = 'Hele landet'
AND region.område != 'Hele landet'
AND region.område LIKE 'Region%'
ORDER BY total.tid, pct_of_total DESC;
```
**Use when**: Comparing parts to whole
## Data Type Handling
DST data stored as TEXT, convert for calculations:
```sql
-- Convert to numeric for math
SELECT
tid,
CAST(indhold AS INTEGER) AS value_int,
CAST(indhold AS FLOAT) AS value_float,
CAST(indhold AS DECIMAL(15,2)) AS value_decimal
FROM dst_table;
```
## Common Calculations
### Growth Rate
```sql
ROUND((new_value::FLOAT - old_value) / old_value * 100, 2) AS growth_pct
```
### Year-over-Year Change
```sql
SELECT
t1.tid AS year,
t1.indhold AS current_value,
LAG(t1.indhold) OVER (ORDER BY t1.tid) AS previous_value,
t1.indhold::FLOAT - LAG(t1.indhold) OVER (ORDER BY t1.tid) AS yoy_change
FROM dst_table t1;
```
### Moving Average
```sql
SELECT
tid,
indhold,
AVG(indhold::FLOAT) OVER (
ORDER BY tid
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3y
FROM dst_table;
```
### Correlation (Pearson)
```sql
-- DuckDB has built-in correlation
SELECT
CORR(t1.indhold::FLOAT, t2.indhold::FLOAT) AS correlation_coefficient
FROM dst_table1 t1
INNER JOIN dst_table2 t2 ON t1.tid = t2.tid;
```
## Handling Missing Data
DST tables may have:
- `NULL` values
- `".."` for unavailable data
- `"-"` for zero/not applicable
```sql
-- Filter out missing data
WHERE indhold IS NOT NULL
AND indhold NOT IN ('..', '-', '')
AND TRY_CAST(indhold AS INTEGER) IS NOT NULL
```
## Query Optimization Tips
1. **Filter early**: Apply WHERE before JOIN when possible
2. **Use indexes**: DuckDB auto-indexes, but column order matters
3. **Limit rows**: Add LIMIT for exploration, remove for final analysis
4. **Aggregate wisely**: Use GROUP BY only when necessary
## Example Multi-Table Research Query
```sql
-- Research question: "How does population growth correlate with housing starts?"
WITH pop_growth AS (
SELECT
t1.tid AS year,
(t2.indhold::FLOAT - t1.indhold) / t1.indhold * 100 AS pop_growth_pct
FROM dst_folk1a t1
INNER JOIN dst_folk1a t2 ON t1.tid = CAST(CAST(t2.tid AS INTEGER) - 1 AS VARCHAR)
WHERE t1.område = 'Hele landet' AND t2.område = 'Hele landet'
),
housing AS (
SELECT
tid AS year,
indhold::INTEGER AS housing_starts
FROM dst_housing_table
WHERE område = 'Hele landet'
)
SELECT
p.year,
p.pop_growth_pct,
h.housing_starts,
CORR(p.pop_growth_pct, h.housing_starts) OVER () AS correlation
FROM pop_growth p
INNER JOIN housing h ON p.year = h.year
ORDER BY p.year;
```
## Usage Guidelines
When dst-research-analyst invokes you:
1. **Identify join keys**: What dimensions do tables share?
2. **Choose pattern**: Which pattern fits the analysis?
3. **Handle data types**: Convert TEXT to numeric
4. **Filter missing data**: Remove NULL/".."/"-"
5. **Add calculations**: Growth rates, percentages, correlations
6. **Order results**: By time or magnitude
7. **Return SQL**: Provide query for dst-query skill to execute
Remember: You provide the SQL patterns, dst-query executes them.