Interview Question Bank · JSONB -> and ->> · casting · missing keys
8. Revenue from raw JSON events
MediumData engineeringQuestion #248
Concept: JSONB -> and ->> · casting · missing keysshow
In Postgres, payload -> 'key' returns JSONB and payload ->> 'key' returns text; chain them for nested fields, like payload -> 'cart' ->> 'item_count'. Cast the text to the type you need, such as (payload ->> 'total')::numeric, which works whether the JSON holds a number or a string. A key that isn't there comes back as NULL, so flags need a default.
Example (different data):
SELECT payload ->> 'country' AS country, SUM((payload -> 'order' ->> 'qty')::int) AS units FROM app_logs WHERE NOT COALESCE((payload ->> 'is_bot')::boolean, false) GROUP BY 1;
Your task
Fernhill's checkout service writes raw JSON events into de_raw_events. Finance wants May 2025 revenue per currency straight from the payloads, without the QA team's test orders.
For checkout_completed events received in May 2025 that aren't test orders ("is_test": true; most payloads have no is_test key, which means a real order), return currency, orders, revenue (sum of total, rounded to 2 decimals) and avg_items (average cart item_count, rounded to 1 decimal). Sort by currency.
Starting Postgres in your browser…
Checking your access…