Write SQL for Pixel Signal Metrics

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

Bucket advertiser-day pixel signal quality and ad performance

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.

Table advertisers

column	type	constraints
advertiser_id	BIGINT	PRIMARY KEY
vertical	VARCHAR
created_at	TIMESTAMP

Sample data:

advertiser_id	created_at	vertical
1001	2025-01-01 00:00:00	ecommerce
1002	2025-06-15 00:00:00	gaming
1003	2024-11-20 00:00:00	travel

Table pixels

column	type	constraints
pixel_id	BIGINT	PRIMARY KEY
advertiser_id	BIGINT	FOREIGN KEY REFERENCES advertisers(advertiser_id)
installed_at	TIMESTAMP

Sample data:

advertiser_id	installed_at	pixel_id
1001	2025-01-15 00:00:00	501
1001	2025-03-10 00:00:00	502
1002	2025-07-01 00:00:00	601
1003	2025-12-12 00:00:00	701
1003	2026-01-05 00:00:00	702

Table pixel_signal_daily

column	type	constraints
ds	DATE
pixel_id	BIGINT	FOREIGN KEY REFERENCES pixels(pixel_id)
events_received	BIGINT
valid_event_rate	DOUBLE PRECISION
match_rate	DOUBLE PRECISION

Sample data:

ds	events_received	match_rate	pixel_id	valid_event_rate
2026-05-04	100	0.6	501	0.8
2026-05-04	300	0.7	502	0.7
2026-05-04	200	0.7	601	0.9
2026-05-05	50	0.92	701	0.95
2026-05-05	150	0.95	702	0.9

Table ad_performance_daily

column	type	constraints
ds	DATE
advertiser_id	BIGINT	FOREIGN KEY REFERENCES advertisers(advertiser_id)
impressions	BIGINT
clicks	BIGINT
spend_usd	DOUBLE PRECISION
attributed_conversions	BIGINT
attributed_revenue_usd	DOUBLE PRECISION

Sample data:

advertiser_id	attributed_conversions	attributed_revenue_usd	clicks	ds	impressions	spend_usd
1001	20	800	1000	2026-05-04	50000	500
1002	25	600	500	2026-05-04	30000	300
1003	10	150	200	2026-05-05	10000	100

Expected output

Task 2 output: one row per quality bucket across the last 30 complete calendar days, aggregating advertiser-day quality and ad performance. CVR = total_conversions / total_clicks. ROAS = total_revenue / total_spend. Buckets: low (<0.60), medium (>=0.60 and <0.85), high (>=0.85).

advertiser_day_count	avg_quality_score	cvr	quality_bucket	roas	total_clicks	total_conversions	total_spend_usd
1	0.48749999999999993	0.02	low	1.6	1000	20	500
1	0.63	0.05	medium	2	500	25	300
1	0.8597499999999999	0.05	high	1.5	200	10	100

Hints

1. Filter to the last 30 complete days with ds >= current_date - 30 and ds < current_date.
2. Active pixels are those with events_received > 0 on that day.
3. Event-weighted averages: sum(rate * events_received) / nullif(sum(events_received), 0).
4. Pixel day quality score is valid_event_rate * match_rate; average it event-weighted at advertiser-day level.
5. Bucket advertiser_day_quality using CASE into 'low', 'medium', 'high'.
6. Aggregate by bucket and compute CVR and ROAS from summed metrics, not by averaging per-day ratios.
7. Use NULLIF to avoid division-by-zero errors for CVR and ROAS.
8. Only reference tables by the exact names: advertisers, pixels, pixel_signal_daily, ad_performance_daily.