← Meta Interview Insights

Meta·Software Engineer·Technical Phone Screen·Senior

SeniorPrefer not to say
Jul 2026Remote

Summary

Meta data engineer technical screen, one SQL question the whole time. Pretty focused on cumulative metrics logic which I wasn't expecting to be the entire interview.

Questions Asked (1)

Q1

Given a daily_metrics table and a cumulative_metrics table that only holds yesterday's data, write SQL to compute today's cumulative value per content_id for a target date D, covering content_ids that appear in either table.

Data ModelingProduct Analytics & Metrics
Author's notes

The FULL OUTER JOIN part is what tripped me up at first.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Use a FULL OUTER JOIN between daily_metrics filtered for date D and cumulative_metrics (which holds yesterday's cumulative values) on content_id, then compute today's cumulative as COALESCE(yesterday_cumulative, 0) + COALESCE(today_daily, 0). This ensures all content_ids from either table are included, with missing values treated as zero.

Pro tip: Mention that you'd validate the result by checking that the cumulative value for each content_id is non-decreasing over time and that the total sum matches the sum of daily metrics up to date D. Also, clarify assumptions about data freshness and time zones.

1. Understand the tables and requirements

Identify that daily_metrics contains per-day increments and cumulative_metrics contains yesterday's cumulative totals. The goal is to compute today's cumulative per content_id for date D, including all content_ids from either table.

2. Filter and prepare the data

Filter daily_metrics for date D to get today's increments. Use cumulative_metrics as-is since it holds yesterday's cumulative values. Ensure both tables are keyed by content_id.

3. Perform a FULL OUTER JOIN

Join the filtered daily_metrics and cumulative_metrics on content_id using a FULL OUTER JOIN to include content_ids that appear in either table.

4. Compute today's cumulative value

For each content_id, calculate today's cumulative as COALESCE(yesterday_cumulative, 0) + COALESCE(today_daily, 0). Use COALESCE to handle NULLs from the outer join.

5. Output and validate

Select content_id and the computed cumulative value. Optionally, validate by checking that the cumulative is non-decreasing and that the sum matches the total daily increments up to date D.

Key Points to Mention

  • Handling missing data with COALESCE or IFNULL to treat NULLs as zeros.
  • Using FULL OUTER JOIN to include all content_ids from both tables.
  • Ensuring the correct date filter: daily_metrics for date D, cumulative_metrics for yesterday (D-1).
  • Considering edge cases: content_id present only in daily_metrics (new content) or only in cumulative_metrics (no activity today).
  • Validating the result for data consistency and correctness.
  • Discussing performance implications and indexing on content_id and date.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.