task LEFT JOIN · medium
Capacity 2: retain missing and unmapped sites
Report every site, its region name (NULL for unmapped sites), and active capacity on the snapshot date. Missing active capacity is zero. Order by site id.
what to return
- Columns
site_idregionunitsin this order- Rows
- 4 rows · in the order asked
- Watch for
- Every stage runs on a fresh database; earlier queries do not carry forward.
- One unit is one compute unit. Snapshot date: 2026-04-01. Capacity intervals include valid_from and exclude valid_to; NULL valid_to means no end.
- Future supply is incremental capacity, available on delivery_date. The snapshot excludes same-day deliveries, so projections add them once.
- Skill
- Produce reproducible capacity reports with explicit units, dates and grain. Refresher: Multi-grain joins without fan-out →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result4 rows · in this order
| site_id | region | units |
|---|---|---|
| 1 | North | 40 |
| 2 | North | 40 |
| 3 | South | 0 |
first 3 of 4 rows
data you can query
Sample rows from each table. Each column shows its type underneath; → marks a link to another table.
capacity_regions2 columns · 3 rows
| idinteger · key | nametext |
|---|---|
| 1 | North |
| 2 | South |
| 3 | West |
all 3 rows
capacity_sites3 columns · 4 rows
| idinteger · key | region_idinteger → capacity_regions | nametext |
|---|---|---|
| 1 | 1 | Alder |
| 2 | 1 | Birch |
| 3 | 2 | Cedar |
| 4 | NULL | Drift |
all 4 rows
current_capacity5 columns · 5 rows
| idinteger · key | site_idinteger → capacity_sites | unitsinteger | valid_fromtext | valid_totext |
|---|---|---|---|---|
| 1 | 1 | 40 | 2026-01-01 | NULL |
| 2 | 2 | 40 | 2026-01-01 | NULL |
| 3 | 3 | 99 | 2025-01-01 | 2026-04-01 |
| 4 | 1 | 5 | 2026-05-01 | NULL |
| 5 | 4 | 10 | 2026-01-01 | NULL |
all 5 rows
future_supply4 columns · 6 rows
| idinteger · key | site_idinteger → capacity_sites | unitsinteger | delivery_datetext |
|---|---|---|---|
| 1 | 1 | 7 | 2026-04-10 |
| 2 | 1 | 8 | 2026-04-10 |
| 3 | 1 | 20 | 2026-05-01 |
| 4 | 2 | 5 | 2026-04-30 |
| 5 | 3 | 12 | 2026-04-01 |
showing 5 of 6 rows
solution.sql
Hints and the answer are always available. Using them counts the question as solved with help.