Coding questions · Meta
Compute cumulative metrics with full joins
This question evaluates proficiency in SQL data manipulation and data engineering competencies, focusing on merging per-day and cumulative metrics, join semantics, and null-value handling in the Data Manipulation (SQL/Python) domain.
Tables:
- daily_metrics(date DATE, content_id STRING, daily_value BIGINT)
- cumulative_metrics(date DATE, content_id STRING, cumulative_value BIGINT) — contains yesterday’s cumulative only (date = D - INTERVAL '1' DAY) Task: For a target date D, write SQL that produces today’s cumulative per content_id as COALESCE(y.cumulative_value, 0) + COALESCE(d.daily_value, 0) for every content_id that appears in either table. Use a FULL OUTER JOIN on content_id between daily_metrics filtered to date = D (alias d) and cumulative_metrics filtered to date = D - 1 (alias y). Return (date = D, content_id, cumulative_value).
Compute daily cumulative metrics with FULL OUTER JOIN
You are given two tables:
- daily_metrics(date, content_id, daily_value): per-day value per content item
- cumulative_metrics(date, content_id, cumulative_value): cumulative value per content item as of that date
The cumulative_metrics table contains only the cumulative values up to the previous day. For example, for a target date D, cumulative_metrics will have data up to D - INTERVAL '1' DAY.
Assume the target date D is '2025-06-01'. Using a FULL OUTER JOIN on content_id between:
- daily_metrics filtered to date = '2025-06-01' (alias d), and
- cumulative_metrics filtered to date = '2025-05-31' (alias y),
write an SQL query that produces today's cumulative value per content_id for D = '2025-06-01', computed as:
cumulative_value = COALESCE(y.cumulative_value, 0) + COALESCE(d.daily_value, 0)
Return one row for every content_id that appears in either table (for those dates), with the following columns:
- date (always '2025-06-01')
- content_id
- cumulative_value
Table daily_metrics
| column | type | constraints |
|---|---|---|
| date | DATE | NOT NULL |
| content_id | VARCHAR(50) | NOT NULL |
| daily_value | BIGINT | NOT NULL |
Sample data:
| content_id | daily_value | date |
|---|---|---|
| video_1 | 10 | 2025-06-01 |
| video_2 | 5 | 2025-06-01 |
| video_4 | 7 | 2025-06-01 |
| video_1 | 3 | 2025-05-31 |
| video_3 | 8 | 2025-05-31 |
Table cumulative_metrics
| column | type | constraints |
|---|---|---|
| date | DATE | NOT NULL |
| content_id | VARCHAR(50) | NOT NULL |
| cumulative_value | BIGINT | NOT NULL |
Sample data:
| content_id | cumulative_value | date |
|---|---|---|
| video_1 | 100 | 2025-05-31 |
| video_2 | 40 | 2025-05-31 |
| video_3 | 20 | 2025-05-31 |
| video_1 | 97 | 2025-05-30 |
| video_99 | 999 | 2025-06-01 |
Expected output
For D = '2025-06-01', return one row per content_id that appears either in daily_metrics on '2025-06-01' or in cumulative_metrics on '2025-05-31'. The cumulative_value is yesterday's cumulative plus today's daily value, using 0 when either side is missing.
| content_id | cumulative_value | date |
|---|---|---|
| video_1 | 110 | 2025-06-01 |
| video_2 | 45 | 2025-06-01 |
| video_3 | 20 | 2025-06-01 |
| video_4 | 7 | 2025-06-01 |
Hints
- Filter each table to the relevant date in a subquery or CTE before joining.
- Use COALESCE on both the content_id and the value columns to handle rows that exist in only one of the tables.