Drill 9 of 12 in Food delivery questions
0

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…