Write a query to return each purchase event in app_events with its event_id and tag_count from the payload’s tags array.
Count array entries, including repeated entries. Return 0 for an empty array and NULL when the tags key is absent. Include only purchases.
Ordering: Sort by event_id in ascending order.
Returnevent_idtag_count
About this problem
You practise measuring a JSON array without expanding it, and keeping the difference between an empty array and an absent key. Each purchase in app_events may have a tags array, and you return its length, 0 for an empty array and NULL when the key is not there at all. The skill is recognising that the function already draws the distinction you need, so the work is mostly in not destroying it.
The reflex answer is to unnest the array and count the rows, and it goes wrong twice over. An empty array produces no rows and a missing key produces no rows, so both events vanish from the result entirely – the counts you do get are right, but two of the four purchases are simply gone, and a GROUP BY cannot invent a row for something it never saw. The other reflex is coalesce(..., 0), which keeps all the rows and collapses the one distinction the prompt spells out. Both produce output that passes a glance; the first is missing rows and the second is missing meaning.
Read the lesson: JSON & Semi-structured (P14) →