Interview country revenue contract

Return country_bucket, paid_orders, and paid_revenue for January paid orders. Use unknown for missing country and order by paid_revenue DESC, country_bucket ASC.

Start by naming the output grain, then build CTEs that can be inspected one at a time.

Sample input

orders + customersPaid January orders are bucketed by buyer country; missing country becomes unknown.

order_id	buyer_id	status	total_amount	country
1001	1	paid	80	US
1002	3	paid	120	GB
1005	2	paid	540	null
1010	6	paid	640	GB

Expected output

Expected outputOne row per country_bucket, ordered by paid_revenue descending.

country_bucket	paid_orders	paid_revenue
GB	3	855
unknown	1	540
US	1	80

Constraints

State the output contract first, use readable CTE layers, return the exact columns requested by the prompt, and use deterministic ordering.

Expected skills

Interview clarification, SQL structure, edge-case handling, and final-answer narration.

SQL

AI evaluation

Run the SQL query to inspect preview rows.