Meta: Ad Set Metrics Unpivoted Into Long Format

Medium
756.

Meta: Ad Set Metrics Unpivoted Into Long Format

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

ColumnType
ad_set_idint
stat_datedate
impressionsint
clicksint
leadsint
spendint

Primary key (together): 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.

Example

The rows must come back in the order shown.

AdSetDaily

ad_set_idstat_dateimpressionsclicksleadsspend
12025-09-01100040NULL500
12025-09-02150060NULL700
22025-09-01800103300
22025-08-3150001009900
32025-09-050000
32025-09-06200NULL1100
42025-10-011001050

Output

ad_set_idmetrictotal
1impressions2500
1clicks100
1spend1200
2impressions800
2clicks10
2leads3
2spend300
3impressions200
3clicks0
3leads1
1 more row
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
ad_set_idint
stat_datedate
impressionsint
clicksint
leadsint
spendint

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_idstat_dateimpressionsclicksleadsspend
12025-09-01100040NULL500
12025-09-02150060NULL700
22025-09-01800103300
22025-08-3150001009900
32025-09-050000
32025-09-06200NULL1100
42025-10-011001050

Output

ad_set_idmetrictotal
1impressions2500
1clicks100
1spend1200
2impressions800
2clicks10
2leads3
2spend300
3impressions200
3clicks0
3leads1
3spend100

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, pos

Another 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 →