cswork / libraryOpen practice workspace

Coding questions · Meta

Compute invalid event percentage by pixel

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.

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

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

Table pixel_events_daily

columntypeconstraints
event_idINTPRIMARY KEY
pixel_idINTNOT NULL
event_dateDATENOT NULL
statusVARCHAR(10)NOT NULL
event_countINTNOT NULL

Sample data:

event_countevent_dateevent_idpixel_idstatus
9502025-05-151101VALID
302025-05-152101INVALID
202025-05-153101MISSING
9002025-05-314101VALID
502025-05-315101INVALID
502025-05-316101MISSING
8002025-05-157102VALID
1502025-05-158102INVALID
502025-05-159102MISSING
7002025-05-3110102VALID
2002025-05-3111102INVALID
1002025-05-3112102MISSING
4502025-05-1513103VALID
102025-05-1514103INVALID
402025-05-1515103MISSING
4002025-05-3116103VALID
02025-05-3117103INVALID
1002025-05-3118103MISSING

Expected output

One row per pixel for 2025-05-31, with invalid events divided by total events (valid + invalid + missing), multiplied by 100.

invalid_percentagepixel_id
5101
20102
0103

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.

Table pixel_events_daily

columntypeconstraints
event_idINTPRIMARY KEY
pixel_idINTNOT NULL
event_dateDATENOT NULL
statusVARCHAR(10)NOT NULL
event_countINTNOT NULL

Sample data:

event_countevent_dateevent_idpixel_idstatus
9502025-05-151101VALID
302025-05-152101INVALID
202025-05-153101MISSING
9002025-05-314101VALID
502025-05-315101INVALID
502025-05-316101MISSING
8002025-05-157102VALID
1502025-05-158102INVALID
502025-05-159102MISSING
7002025-05-3110102VALID
2002025-05-3111102INVALID
1002025-05-3112102MISSING
4502025-05-1513103VALID
102025-05-1514103INVALID
402025-05-1515103MISSING
4002025-05-3116103VALID
02025-05-3117103INVALID
1002025-05-3118103MISSING

Table ad_performance_daily

columntypeconstraints
perf_idINTPRIMARY KEY
perf_dateDATENOT NULL
pixel_idINTNOT NULL
ad_idINTNOT NULL
spendDECIMAL(12,2)NOT NULL
revenueDECIMAL(12,2)NOT NULL

Sample data:

ad_idperf_dateperf_idpixel_idrevenuespend
2012025-05-151101700400
2012025-05-312101900600
2022025-05-153102600500
2022025-05-314102700700
2032025-05-155103350300
2032025-05-316103450500

Expected output

Two rows (high_quality and low_quality), with aggregated spend/revenue over 2025-05-01 to 2025-05-31 and ROI computed as total_revenue / total_spend.

quality_grouproitotal_revenuetotal_spend
high_quality1.616001000
low_quality1.0521002000

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.