task CTEs · hard
Capacity 3: the next delivery date and all ties
For each site, find the earliest delivery date on or after the snapshot date and sum every delivery tied on that date. Use NULL date and zero units when none exists. Order by site id.
what to return
- Columns
site_idnext_dateincoming_unitsin 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 | next_date | incoming_units |
|---|---|---|
| 1 | 2026-04-10 | 15 |
| 2 | 2026-04-30 | 5 |
| 3 | 2026-04-01 | 12 |
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.