Interview Question Bank · Aggregate before joining · fan-out · COALESCE
9. Courier earnings per hour online
HardFood deliveryQuestion #237
Concept: Aggregate before joining · fan-out · COALESCEshow
When two child tables hang off the same parent, joining both at once multiplies rows: 2 sessions × 5 jobs gives 10 rows, and every SUM is inflated. Aggregate each child to one row per parent first, in its own CTE, then join the totals. A LEFT JOIN keeps parents with no rows in one child, and COALESCE turns their NULL total into 0.
Example (different data):
WITH h AS (SELECT tutor_id, SUM(hours) AS hours FROM shifts GROUP BY tutor_id),
p AS (SELECT tutor_id, SUM(amount) AS paid FROM payouts GROUP BY tutor_id)
SELECT h.tutor_id, h.hours, COALESCE(p.paid, 0) AS paid
FROM h
LEFT JOIN p ON p.tutor_id = h.tutor_id;Your task
Brightbowl's courier team wants to see what couriers really earned per hour online over the weekend of 2025-03-14, counting delivery fees plus tips.
For every courier with at least one session in fd_courier_sessions, return courier (name), jobs (rows in fd_courier_jobs, 0 if none), hours_online (total session hours, ROUND(..., 2)), earnings (sum of fee + tip, a missing tip counting as 0; 0 if no jobs) and earnings_per_hour (earnings divided by the unrounded hours, ROUND(..., 2)). Sort by earnings_per_hour descending, then courier.
Starting Postgres in your browser…
Checking your access…