Write SQL for Pixel Signal Metrics
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You are working on Meta Ads Pixel analytics. Assume all timestamps are stored in UTC, and analyze the last 30 complete calendar days.
Tables
1. advertisers
- advertiser_id BIGINT
- vertical STRING
- created_at TIMESTAMP
2. pixels
- pixel_id BIGINT
- advertiser_id BIGINT
- installed_at TIMESTAMP
3. pixel_signal_daily
- ds DATE
- pixel_id BIGINT
- events_received BIGINT
- valid_event_rate DOUBLE -- share of received events that pass validation, from 0 to 1
- match_rate DOUBLE -- share of valid events that can be matched back to ad traffic, from 0 to 1
4. ad_performance_daily
- ds DATE
- advertiser_id BIGINT
- impressions BIGINT
- clicks BIGINT
- spend_usd DOUBLE
- attributed_conversions BIGINT
- attributed_revenue_usd DOUBLE
Relationships
- advertisers has a 1:N relationship with pixels
- pixels has a 1:N relationship with pixel_signal_daily
- advertisers has a 1:N relationship with ad_performance_daily
Task 1: Write a SQL query that returns one row per advertiser per day with the following columns:
- ds
- advertiser_id
- active_pixels -- number of the advertiser's pixels that received at least 1 event that day
- total_events_received
- avg_valid_event_rate -- event-weighted average valid_event_rate across the advertiser's active pixels that day
Task 2: Write a SQL query to study the relationship between Pixel signal data quality and ads performance.
Use the following definitions:
- pixel_day_quality_score = valid_event_rate * match_rate
- advertiser_day_quality = event-weighted average pixel_day_quality_score across all active pixels for that advertiser on that day
- CVR = attributed_conversions / NULLIF(clicks, 0)
- ROAS = attributed_revenue_usd / NULLIF(spend_usd, 0)
Bucket each advertiser-day into:
- 'low' if advertiser_day_quality < 0.60
- 'medium' if advertiser_day_quality >= 0.60 and advertiser_day_quality < 0.85
- 'high' if advertiser_day_quality >= 0.85
Return one row per quality bucket for the 30-day window with these output columns:
- quality_bucket
- advertiser_day_count
- avg_quality_score
- total_spend_usd
- total_clicks
- total_conversions
- cvr
- roas
Overview: This question evaluates SQL-based data manipulation and analytical competencies, specifically joining and aggregating event and ad performance tables, computing event-weighted metrics and composite data-quality scores (such as validity and match-rate based measures) across a 30-day window.
Read the full Meta Data Scientist interview experience this question came from
Write a PostgreSQL query for the sample data. Use the latest ds present in pixel_signal_daily as the end date, and use the inclusive 30-day window from max(ds) - 29 days through max(ds). First compute advertiser-day pixel metrics across active pixels, where active pixels have events_received > 0: active_pixels, total_events_received, event-weighted avg_valid_event_rate, and advertiser_day_quality as the event-weighted average of valid_event_rate * match_rate. Then join advertiser-day quality to ad_performance_daily on advertiser_id and ds, bucket advertiser-days into low (< 0.60), medium (>= 0.60 and < 0.85), and high (>= 0.85), and return the final bucket-level aggregate rows. Output columns: quality_bucket, advertiser_day_count, avg_quality_score, total_spend_usd, total_clicks, total_conversions, cvr, and roas. Compute cvr as total_conversions / total_clicks and roas as total attributed revenue / total spend, using summed metrics within each bucket.
Tables
advertisers(advertiser_id BIGINT, vertical VARCHAR, created_at TIMESTAMP)
pixels(pixel_id BIGINT, advertiser_id BIGINT, installed_at TIMESTAMP)
pixel_signal_daily(ds DATE, pixel_id BIGINT, events_received BIGINT, valid_event_rate DOUBLE PRECISION, match_rate DOUBLE PRECISION)
ad_performance_daily(ds DATE, advertiser_id BIGINT, impressions BIGINT, clicks BIGINT, spend_usd DOUBLE PRECISION, attributed_conversions BIGINT, attributed_revenue_usd DOUBLE PRECISION)
Hints
- Filter to the last 30 complete days with ds >= current_date - 30 and ds < current_date.
- Active pixels are those with events_received > 0 on that day.