Items Bought on iOS
Hard · JSON
The containment operator @> checks whether a jsonb value includes another one, at any depth: payload @> '{"device": {"os": "ios"}}'. To turn a JSON array into rows, use jsonb_array_elements(payload->'items') in the FROM clause. ->> reads a field as text, which you then cast.
Each event's payload has a type, a device object and (for purchases) an items array of {"sku", "qty", "price"}. Look only at events with type "purchase" made on a device with os "ios".
Return sku, units (total qty) and revenue (total qty * price, as numeric rounded to 2 decimals), sorted by revenue descending, then sku.