task Reconciliation & quality gates · hard
Capacity 5: project regional capacity at month end
Project every region’s capacity on April 30, 2026: sum capacity records valid on April 30 plus incremental deliveries arriving April 1 through April 30 inclusive. Exclude unmapped sites and include empty regions as zero. Assume deliveries remain available through the cutoff. Order by region id.
what to return
- Columns
region_idprojected_unitsin this order- Rows
- 3 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 result3 rows · in this order
| region_id | projected_units |
|---|---|
| 1 | 100 |
| 2 | 12 |
| 3 | 0 |
all 3 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.