Meta: Ad Set Metrics Unpivoted Into Long Format
Ads reporting stores one wide row per ad set per day, a column per metric.
- Difficulty: Medium
- Topics: Aggregation, Subqueries, Dates
- Dialect: MySQL
- Problem: #756
Problem statement
Ads reporting stores one wide row per ad set per day, a column per metric. A new dashboard charting library wants long format instead: one row per (ad set, metric) with the metric's name as a value. A NULL means the metric is not tracked for that ad set on that day — it is not zero.
For September 2025 (2025-09-01 to 2025-09-30 inclusive), return ad_set_id, metric (one of impressions, clicks, leads, spend) and total (the sum of that metric's non-NULL values). Return a row only when the metric has at least one non-NULL value for the ad set in September — a total of 0 from real zeros is still returned. Order the rows by ad_set_id, then by metric in the fixed order impressions, clicks, leads, spend.
A practice scenario set at Meta: the tables and data are invented, and this is not a question from, or endorsed by, Meta.
Tables
Table: AdSetDaily
| Column | Type |
|---|---|
| ad_set_id | int |
| stat_date | date |
| impressions | int |
| clicks | int |
| leads | int |
| spend | int |
Primary key: ad_set_id, stat_date.
One row per ad set per day of delivery. spend is in rupees; any metric can be NULL when it was not tracked.
Examples
Example 1
AdSetDaily
| ad_set_id | stat_date | impressions | clicks | leads | spend |
|---|---|---|---|---|---|
| 1 | 2025-09-01 | 1000 | 40 | NULL | 500 |
| 1 | 2025-09-02 | 1500 | 60 | NULL | 700 |
| 2 | 2025-09-01 | 800 | 10 | 3 | 300 |
| 2 | 2025-08-31 | 5000 | 100 | 9 | 900 |
| 3 | 2025-09-05 | 0 | 0 | 0 | 0 |
| 3 | 2025-09-06 | 200 | NULL | 1 | 100 |
| 4 | 2025-10-01 | 100 | 1 | 0 | 50 |
Output
| ad_set_id | metric | total |
|---|---|---|
| 1 | impressions | 2500 |
| 1 | clicks | 100 |
| 1 | spend | 1200 |
| 2 | impressions | 800 |
| 2 | clicks | 10 |
| 2 | leads | 3 |
| 2 | spend | 300 |
| 3 | impressions | 200 |
| 3 | clicks | 0 |
| 3 | leads | 1 |
| 3 | spend | 100 |
How to solve Meta: Ad Set Metrics Unpivoted Into Long Format
Unpivoting turns columns into rows. MySQL has no UNPIVOT, so each metric column gets its own SELECT that emits the ad set, the metric's name as a string literal and the column's value, and UNION ALL stacks the four results into one tall table of (ad set, metric, value). UNION ALL matters: plain UNION would collapse two days with the same value into one row and undercount the sums. Each branch carries a sort number, because the requested order — impressions, clicks, leads, spend — is the funnel's order, not the alphabet's.
Once the data is tall, the rest is an ordinary aggregation. Filtering val IS NOT NULL before grouping removes untracked days, so a metric that is NULL on every September day of an ad set forms no group and is not returned, while a metric whose values are real zeros still forms a group with total 0. The date filter in every branch keeps August and October out.
The alternative unpivots with a CROSS JOIN to a four-row metric list and a CASE that picks the column for each metric; HAVING COUNT(…) > 0 plays the role of the NULL filter, since COUNT of an expression skips NULLs. Both read the table four times' worth of values, which is the size of the result before grouping.
Reference solution (MySQL)
WITH tall AS (
SELECT ad_set_id, 1 AS pos, 'impressions' AS metric, impressions AS val FROM AdSetDaily WHERE stat_date BETWEEN '2025-09-01' AND '2025-09-30'
UNION ALL
SELECT ad_set_id, 2, 'clicks', clicks FROM AdSetDaily WHERE stat_date BETWEEN '2025-09-01' AND '2025-09-30'
UNION ALL
SELECT ad_set_id, 3, 'leads', leads FROM AdSetDaily WHERE stat_date BETWEEN '2025-09-01' AND '2025-09-30'
UNION ALL
SELECT ad_set_id, 4, 'spend', spend FROM AdSetDaily WHERE stat_date BETWEEN '2025-09-01' AND '2025-09-30'
)
SELECT ad_set_id, metric, SUM(val) AS total
FROM tall
WHERE val IS NOT NULL
GROUP BY ad_set_id, pos, metric
ORDER BY ad_set_id, posAnother way
SELECT a.ad_set_id, m.metric,
SUM(CASE m.pos WHEN 1 THEN a.impressions WHEN 2 THEN a.clicks WHEN 3 THEN a.leads ELSE a.spend END) AS total
FROM AdSetDaily a
CROSS JOIN (SELECT 1 AS pos, 'impressions' AS metric UNION ALL SELECT 2, 'clicks' UNION ALL SELECT 3, 'leads' UNION ALL SELECT 4, 'spend') m
WHERE a.stat_date BETWEEN '2025-09-01' AND '2025-09-30'
GROUP BY a.ad_set_id, m.pos, m.metric
HAVING COUNT(CASE m.pos WHEN 1 THEN a.impressions WHEN 2 THEN a.clicks WHEN 3 THEN a.leads ELSE a.spend END) > 0
ORDER BY a.ad_set_id, m.pos← Meta: Quest Store Apps Rated by at Least Three Players · Meta: Last-Click Attributed Revenue by Campaign →