Revenue by Tag Without Double Counting

Write a query to return a revenue report for purchase events in app_events:

One row per distinct tag. events counts purchases carrying that tag and revenue sums their amounts. Repeating a tag inside one event counts the event once for that tag.

One row labelled untagged for purchases with an empty or absent tags array.

One final row labelled ALL, counting every purchase and its amount exactly once.

Treat an unreadable amount as 0. An event with several distinct tags contributes to each tag, so adding tag revenues can exceed the true ALL total. Calculate the overall row from purchases themselves.

Ordering: Sort by tag in ascending order, with the ALL row last.

Returntageventsrevenue

Columns

app_events4 columns

columntype

event_idVARCHAR

user_idVARCHAR

event_typeVARCHAR

payloadVARCHAR

Sample data & expected output

8 rows · 4 columns

event_iduser_idevent_typepayload

ev_1u_1purchase{"amount":900,"tags":["new","promo"]}

ev_2u_2purchase{"amount":1000,"tags":["promo"]}

ev_3u_3purchase{"amount":120,"tags":[]}

ev_4u_1purchase{"amount":50}

ev_5u_4purchase{"amount":"750.00","tags":["vip","promo","vip"]}

ev_6u_2purchase{"amount":"n/a","tags":["new"]}

ev_7u_5signup{"amount":5000,"tags":["ignored"]}

ev_8u_6purchase{"amount":"n/a","tags":["solo"]}

If you get stuck

Show hint · 3 hints Walkthrough Reveal answer

About this problem

You practise the arithmetic of a one-to-many expansion: what a per-tag figure means, and what happens to a total when every row is counted once per tag. Across the purchase events you produce revenue by tag, a row for the untagged purchases, and a true total that counts each event once. The skill is keeping the two grains apart in one query and showing both, so the report cannot be misread.

Expanding an array changes what a row means, and almost every mistake here follows from forgetting that. Summing the tag rows gives 4470.00 against a real total of 2820.00, and the number is not wrong so much as it is an answer to “how much revenue is attributed across all tags”, which nobody asked. Because it is larger rather than smaller, it looks like growth rather than like a bug. Two smaller versions of the same thing sit alongside it: the duplicate vip on ev_5 inflates one tag unless you deduplicate on event and tag, and the two untagged purchases vanish entirely unless you count them outside the expansion, which quietly removes 170.00 from a report that still adds up internally.

Read the lesson: JSON & Semi-structured (P14) →

Your query · runs in your browser⌘ Enter to run

Loading editor…

Output · run to see rows