Quick Overview

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.

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).

Overview: 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.

Read the full Meta Data Engineer interview experience this question came from

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

Tables

daily_metrics(date DATE, content_id VARCHAR(50), daily_value BIGINT)

cumulative_metrics(date DATE, content_id VARCHAR(50), cumulative_value BIGINT)

Hints

  1. Filter each table to the relevant date in a subquery or CTE before joining.
  2. Use COALESCE on both the content_id and the value columns to handle rows that exist in only one of the tables.

Loading coding console...