Drill 8 of 12 in Data engineering questions
0

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…