Urban Company mid juliandayDate ArithmeticAVGGROUP BY

For every service, using only jobs that have actually finished (completed_at IS NOT NULL), return service, completed_jobs (how many finished jobs it has) and avg_hours — the average time from scheduled_at to completed_at in hours, computed with julianday() and rounded to 2 decimals. Order by avg_hours descending.

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

📖 Story Urban Company · Home Services Ops Analytics

Urban Company's operations team wants to publish an internal "how long does a job actually take on-site" benchmark per service, so the scheduling engine can block realistic time slots for its partners. The timestamps live as TEXT ('2026-03-03 10:00'), and a junior analyst's first attempt simply subtracted completed_at minus scheduled_at — which came back as a neat column of zeros for every single job. On top of that, the table also holds cancelled bookings and jobs that are still in progress, whose completed_at is genuinely NULL because the job hasn't finished yet, and those must not be counted as jobs in the benchmark.

🎯 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

Look at every job's scheduled and completed timestamps

query.sql

SELECT booking_id, service, scheduled_at, completed_at, status
FROM uc_bookings
ORDER BY service, booking_id;

💡 Explanation

All 15 bookings come back, grouped visually by service — 4 AC Repair, 5 Home Cleaning and 6 Salon at Home rows — so you can see exactly which rows will feed each service's average.
Both timestamp columns are TEXT in 'YYYY-MM-DD HH:MM' form; SQLite has no separate DATETIME storage type, so to SQL these are just strings until a date function interprets them.
Four rows have completed_at = NULL: bookings 5 and 10 were cancelled, and bookings 9 and 12 are still in_progress — the NULL genuinely means 'this job has no finish time yet', not 'finished in zero time'.
Some jobs cross into a different hour and minute (booking 8 runs 10:00 to 14:00, booking 11 runs 09:00 to 11:45), so any correct method has to read minutes as well as hours, not just the date part.

The trap: subtracting TEXT timestamps directly

query.sql

SELECT booking_id, service,
       completed_at - scheduled_at AS naive_diff,
       status
FROM uc_bookings
ORDER BY service, booking_id;

💡 Explanation

Every finished job shows naive_diff = 0, even booking 8 which clearly ran four hours — the query raises no error and no warning, it just quietly returns a wrong number.
The minus operator forces both strings into numbers, and SQLite converts a string by reading only its leading numeric prefix: '2026-03-10 14:00' becomes 2026, and so does '2026-03-10 10:00', so 2026 - 2026 = 0.
The four NULL-completed_at rows (5, 9, 10, 12) return NULL rather than 0, because any arithmetic with NULL is NULL — so even in the broken version, the unfinished jobs are the only rows that are honestly 'unknown'.
Averaging this column per service would produce a benchmark of 0 hours for every service, which looks like a suspiciously perfect result but would let the scheduler pack jobs back to back with no on-site time at all.

Fix it: julianday() for real time math, finished jobs only

query.sql

SELECT service,
       COUNT(*) AS completed_jobs,
       ROUND(AVG((julianday(completed_at) - julianday(scheduled_at)) * 24), 2) AS avg_hours
FROM uc_bookings
WHERE completed_at IS NOT NULL
GROUP BY service
ORDER BY avg_hours DESC;

💡 Explanation

julianday() parses the full date-and-time string into a fractional day count, so the difference of two julianday values is in days (booking 8 gives 0.1667), and multiplying by 24 turns it into hours (4.0).
Result: Home Cleaning 3 jobs at 3.42 hours, AC Repair 3 jobs at 2.19 hours, Salon at Home 5 jobs at 1.32 hours — Home Cleaning tops the list because bookings 1, 8 and 11 each took between 2.75 and 4 hours.
Remove the WHERE completed_at IS NOT NULL line and avg_hours stays exactly the same (AVG already skips the NULL differences), but completed_jobs silently inflates to 5, 4 and 6 — the cancelled and in-progress bookings get counted as finished jobs.
That is why the filter belongs in WHERE rather than relying on AVG's NULL-skipping: COUNT(*) counts rows, not non-NULL values, so the NULL completed_at has to be removed before grouping for both columns to describe the same set of jobs.

▶ Practice this live ← Browse all questions