Urban Company mid FILTERConditional AggregationCOUNTAVG

For each partner_name, return total_jobs, completed_jobs, cancelled_jobs, unrated_jobs (completed jobs whose rating IS NULL) and avg_rating (average of the ratings that exist, rounded to 2 decimals). Use the aggregate FILTER (WHERE ...) clause for the conditional counts so that a partner with no matching rows gets 0, not NULL. Order by partner_name.

Urban Company · Home Services Ops Analytics — practice this real-world SQL scenario live in your browser.

📖 Story Urban Company · Home Services Ops Analytics

Every month Urban Company sends each service partner a one-line scorecard: how many jobs they were assigned, how many they completed, how many got cancelled, how many completed jobs the customer never rated, and their average rating. The current report uses SUM(CASE WHEN ... THEN 1 END) for the status counts, and the partner app has started showing a blank cancellation figure for the two partners with a spotless record — the app treats blank as 'data missing' and flags their accounts for review, when the real answer is a perfectly good zero. Ratings are optional, so a completed job with no rating has rating = NULL, meaning 'the customer never rated it', which is not the same as a zero-star review.

🎯 Your Mission

📋 Table Structure

🗂 uc_bookings

booking_id INTEGER 1

customer_name TEXT Ananya

partner_name TEXT Ravi

service TEXT Home Cleaning

booked_at TEXT 2026-03-01 09:15

scheduled_at TEXT 2026-03-03 10:00

completed_at TEXT 2026-03-03 13:30

status TEXT completed

rating INTEGER 5

⚡ Step-by-Step Walkthrough

See each partner's jobs, statuses and ratings

query.sql

SELECT partner_name, booking_id, status, rating
FROM uc_bookings
ORDER BY partner_name, booking_id;

💡 Explanation

Four partners share the 15 bookings: Meena has 3, Ravi 5, Salma 3 and Venkat 4 — these are the total_jobs numbers the final scorecard has to reproduce.
Salma and Venkat have no 'cancelled' row at all; their correct cancelled_jobs is 0, and this is exactly the group the CASE WHEN version is about to get wrong.
rating is NULL on every cancelled and in_progress row, which is expected, but also on two completed jobs (booking 3 for Salma and booking 13 for Meena) — those are the genuinely unrated jobs the report has to count.
Reading the raw rows first matters because conditional aggregation hides the rows behind it — once the numbers are collapsed, a NULL that should be a 0 is very hard to spot from the result alone.

The trap: SUM(CASE WHEN ... THEN 1 END) returns NULL, not 0

query.sql

SELECT partner_name,
       COUNT(*) AS total_jobs,
       SUM(CASE WHEN status = 'completed' THEN 1 END) AS completed_jobs,
       SUM(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled_jobs
FROM uc_bookings
GROUP BY partner_name
ORDER BY partner_name;

💡 Explanation

Meena and Ravi correctly show cancelled_jobs = 1, but Salma and Venkat show NULL — the two partners with the best record are exactly the ones whose scorecard comes out blank.
A CASE WHEN with no ELSE yields NULL for every row that doesn't match, so for Salma every input to SUM is NULL, and SUM over only NULLs returns NULL — SQL treats it as 'nothing to add up', not 'added up to zero'.
completed_jobs looks fine here only because every partner happens to have at least one completed job; the same bug is sitting in that column and would show up for a new partner whose jobs are all still in progress.
The common patch is SUM(CASE WHEN ... THEN 1 ELSE 0 END), which works, but it puts the 'what do I count' logic inside a CASE expression; the FILTER clause in the next step says the same thing more directly and cannot forget the ELSE.

Fix it: COUNT(*) FILTER (WHERE ...) for every conditional count

query.sql

SELECT partner_name,
       COUNT(*) AS total_jobs,
       COUNT(*) FILTER (WHERE status = 'completed') AS completed_jobs,
       COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled_jobs,
       COUNT(*) FILTER (WHERE status = 'completed' AND rating IS NULL) AS unrated_jobs,
       ROUND(AVG(rating), 2) AS avg_rating
FROM uc_bookings
GROUP BY partner_name
ORDER BY partner_name;

💡 Explanation

FILTER (WHERE ...) is attached to one aggregate and decides which rows that single aggregate sees, while the rest of the SELECT still sees the whole group — so total_jobs, completed_jobs and cancelled_jobs are computed side by side in one pass over the table.
COUNT always returns a number, so when the filter lets zero rows through the answer is 0: Salma and Venkat now show cancelled_jobs = 0, and nothing downstream mistakes them for missing data.
unrated_jobs = COUNT(*) FILTER (WHERE status = 'completed' AND rating IS NULL) picks up booking 3 for Salma and booking 13 for Meena (1 each); Ravi and Venkat rated every completed job, so they get 0.
avg_rating uses plain AVG(rating), which skips the NULLs and averages only real ratings: Salma's (5 + 3) / 2 = 4.0, not (0 + 5 + 3) / 3 = 2.67 — treating 'never rated' as zero stars would wrongly punish her for a customer who simply didn't tap the rating screen.

▶ Practice this live ← Browse all questions