Current-row snapshot audit

Return country and paid_rate, the fraction of that country's orders whose status is paid, formatted to 2 decimal places as text with printf('%.2f', ...). Group by buyer country, replacing a missing country with 'unknown'. Order by the paid fraction descending, then country ascending.

Multiply by 1.0 to force decimals, then format: printf('%.2f', 1.0 * SUM(CASE WHEN o.status = 'paid' THEN 1 ELSE 0 END) / COUNT(*)).

Sample input

ordersPer country: unknown 2/2 paid, GB 3/5 paid, US 1/3 paid, IN 0/2 paid.

order_id	buyer_id	status
1001	1	paid
1004	1	shipped
1009	1	pending
1005	2	paid
1011	2	paid

customersbuyer_id joins to country; buyer 2 has a NULL country (becomes unknown).

customer_id	country
1	US
2	null
3	GB
4	IN
6	GB

Expected output

Expected outputDecimal-safe share of paid orders per country, formatted to 2dp text with printf, highest first.

country	paid_rate
unknown	1.00
GB	0.60
US	0.33
IN	0.00

Constraints

JOIN orders to customers on customers.customer_id = orders.buyer_id. Force decimal math with 1.0 * SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) / COUNT(*) so it does not floor to 0, then wrap it in printf('%.2f', ...). Group by COALESCE(country, 'unknown'). Order by the numeric paid fraction DESC, then country ASC.

Expected skills

Safe ratio math (avoiding integer division), formatting a rate to fixed decimals, and grouping on a COALESCE expression.

SQL

AI evaluation

Run the SQL query to inspect preview rows.