Interview Question Bank · Aggregate each fact table separately · join on driver and week · COALESCE
7. Driver earnings per online hour, by week
HardRide-sharingQuestion #286
Concept: Aggregate each fact table separately · join on driver and week · COALESCEshow
Trips and online sessions are two fact tables about the same driver, so joining them row to row pairs every trip with every session and inflates both sums. Aggregate each one to a single row per driver per week in its own CTE (date_trunc('week', ...) gives the Monday), then join the two summaries on driver and week. LEFT JOIN from the hours side keeps weeks with no completed trips, and COALESCE makes their earnings 0.
Example (different data):
WITH w AS (
SELECT nurse_id, date_trunc('week', shift_start)::date AS wk,
SUM(EXTRACT(EPOCH FROM shift_end - shift_start)) / 3600 AS hours
FROM nurse_shifts GROUP BY 1, 2
),
v AS (
SELECT nurse_id, date_trunc('week', seen_at)::date AS wk, COUNT(*) AS patients
FROM patient_visits GROUP BY 1, 2
)
SELECT w.nurse_id, w.wk, COALESCE(v.patients, 0) / w.hours AS patients_per_hour
FROM w
LEFT JOIN v ON v.nurse_id = w.nurse_id AND v.wk = w.wk;Your task
Corvo Rides' driver team wants to see, week by week in June 2025, what each driver earned for every hour they were logged in. Earnings are the driver_payout of completed trips. A session counts toward the week it started in, and a trip toward the week it was requested in.
For every driver and week (Monday start) with at least one session in rt_driver_sessions, return driver (name), week_start (a date), online_hours (total session hours, rounded to 2), earnings (sum of driver_payout from that week's completed trips, 0 if none) and earnings_per_hour (earnings divided by the unrounded hours, rounded to 2). Sort by week_start, then driver.
Starting Postgres in your browser…
Checking your access…