Compute cumulative metrics with full joins
Company: Meta
Role: Data Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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).
Quick Answer: 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.