← Meta Interview Insights

Meta·Data Scientist·Technical Phone Screen·Senior

Senior
Apr 2026

Summary

Meta DS interview, SQL round focused on pixel event data. One question, pretty well-scoped, but the NULL handling edge case is where people probably slip up.

Questions Asked (1)

Q1

Given a table of daily pixel event counts broken down by signal type, write a SQL query that computes, for each pixel, the percentage of observed events that were invalid yesterday. Exclude missing events from the denominator, and handle the case where a pixel has zero observed events.

Product Analytics & MetricsData Modeling
Author's notes

The core aggregation is straightforward enough: sum invalid and total observed per pixel, divide.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, clarify the table schema and definitions of 'observed' vs 'missing' events and 'invalid' signal types. Then, write a SQL query that filters to yesterday's data, aggregates counts by pixel, and computes the invalid percentage using conditional aggregation, ensuring the denominator excludes missing events and handles zero observed events with a CASE statement or NULLIF.

Pro tip: Always confirm the definition of 'invalid' and 'missing' with the interviewer, as these terms can vary by context; also, explicitly state how you handle division by zero to avoid runtime errors.

1. Clarify requirements and schema

Ask about the table structure, column names, and precise definitions of 'observed', 'missing', and 'invalid' events. Confirm that 'yesterday' refers to a specific date and that missing events should be excluded from the denominator.

2. Filter and aggregate data

Write a subquery or CTE to filter rows for yesterday's date and group by pixel. Use conditional aggregation (e.g., SUM(CASE WHEN signal_type = 'invalid' THEN event_count ELSE 0 END)) to count invalid events, and sum only observed events for the denominator.

3. Compute percentage with zero handling

Calculate the percentage as invalid_count * 100.0 / observed_count. Use NULLIF or a CASE statement to return 0 or NULL when observed_count is zero, depending on business preference.

4. Finalize and validate query

Combine the steps into a single query, ensuring proper aliasing and formatting. Optionally, add a check for edge cases like pixels with no data.

Key Points to Mention

  • Use of conditional aggregation to separate invalid and observed counts
  • Exclusion of missing events from the denominator
  • Handling division by zero with NULLIF or CASE
  • Filtering for yesterday's date using appropriate date functions
  • Grouping by pixel to compute per-pixel percentages
  • Potential need to join with a pixel dimension table if pixels with zero events should appear

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