Quick Overview

This question evaluates the ability to aggregate time-series event data and compute proportions per entity, specifically summing events across signal types, filtering by a recent date window, and dealing with zero or missing denominators; it is in the Data Manipulation (SQL/Python) category for a Data Scientist role.

Compute invalid event percentage by pixel

Company: Meta

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: easy

Interview Round: Technical Screen

## Context You work on an ads pixel instrumentation platform. Each pixel emits events throughout the day; some events are **missing** (not observed) and some are **invalid** (observed but fails validation). ## Tables ### `pixel_event_daily` One row per `(event_date, pixel_id, signal_type)`. - `event_date` DATE — calendar date in UTC - `pixel_id` STRING - `signal_type` STRING — e.g., `page_view`, `purchase` - `valid_event_count` BIGINT — number of valid events recorded that day - `invalid_event_count` BIGINT — number of invalid events recorded that day - `missing_event_count` BIGINT — number of expected-but-missing events that day Assumptions: - Treat `valid_event_count`, `invalid_event_count`, `missing_event_count` as non-negative integers. - “Yesterday” means the most recent complete UTC day. ## Task For **yesterday**, compute the **invalid event percentage** for each `pixel_id`. - Define: - `total_observed = valid_event_count + invalid_event_count` (exclude missing from the denominator) - `invalid_pct = invalid_event_count / total_observed` - If `total_observed = 0`, return `invalid_pct = NULL`. ## Output Return a result with: - `pixel_id` - `invalid_events` (sum over all signal types yesterday) - `total_observed_events` (sum over all signal types yesterday) - `invalid_pct` (a decimal between 0 and 1) Order by `invalid_pct` descending (NULLs last).

Overview: This question evaluates the ability to aggregate time-series event data and compute proportions per entity, specifically summing events across signal types, filtering by a recent date window, and dealing with zero or missing denominators; it is in the Data Manipulation (SQL/Python) category for a Data Scientist role.

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

Invalid event percentage by pixel for a specific day

You are given a daily aggregated table of pixel signal events. Each row records how many events of a given status occurred for a pixel on a date. Statuses can be: - 'VALID' - 'INVALID' - 'MISSING' For the date 2025-05-31, compute the invalid event percentage for each pixel, defined as: invalid_percentage = 100 * (INVALID event_count) / (VALID + INVALID + MISSING event_count) Return one row per pixel with columns: pixel_id, invalid_percentage (as a percentage, not a fraction).

Tables

pixel_events_daily(event_id INT, pixel_id INT, event_date DATE, status VARCHAR(10), event_count INT)

Hints

  1. Use conditional aggregation (SUM(CASE WHEN ... THEN ... END)).
  2. Use NULLIF in the denominator to avoid division by zero.

Show business impact by comparing ROI for high vs low quality pixels

You are given: 1) A daily aggregated table of pixel signal events (VALID/INVALID/MISSING). 2) A daily table of ad performance by pixel with spend and revenue. For the date range 2025-05-01 to 2025-05-31 (inclusive): Step A: Compute per-pixel rates over the whole period: - invalid_rate = INVALID / (VALID + INVALID + MISSING) - missing_rate = MISSING / (VALID + INVALID + MISSING) Step B: Classify each pixel as: - 'high_quality' if invalid_rate <= 0.05 AND missing_rate <= 0.10 - otherwise 'low_quality' Step C: Using the classification from Step B, aggregate ad performance over the same date range and return, per quality group: - quality_group - total_spend - total_revenue - roi = total_revenue / total_spend This comparison is a simple way to support the claim that higher-quality pixels are associated with better business outcomes.

Tables

pixel_events_daily(event_id INT, pixel_id INT, event_date DATE, status VARCHAR(10), event_count INT)

ad_performance_daily(perf_id INT, perf_date DATE, pixel_id INT, ad_id INT, spend DECIMAL(12,2), revenue DECIMAL(12,2))

Hints

  1. Compute per-pixel invalid/missing rates first, then classify into a quality bucket.
  2. Join the bucketed pixels to ad performance and aggregate spend/revenue by bucket.

Community answers

Answer by SS

SELECT pixel_id, invalid_events, total_observed_events, invalid_events * 1.0 / NULLIF(total_observed_events, 0) AS invalid_pct FROM ( SELECT pixel_id, SUM(invalid_event_count) AS invalid_events, SUM(invalid_event_count) + SUM(valid_event_count) AS total_observed_events FROM pixel_event_daily WHERE event_date = CURRENT_DATE - INTERVAL '1 day' GROUP BY pixel_id ) subquery ORDER BY invalid_pct DESC NULLS LAST

Loading coding console...