Trip stats by city

Ops wants a one-line health check per city: how busy it is, how often riders get let down, and what a finished trip pays.

A trip’s status is either 'completed' or 'cancelled'. Cancelled trips have NULL fare, ended_at and distance_km.

Return one row per city with:

column	type	meaning
city	text	the city
trips	integer	every trip in that city, cancelled or not
cancelled	integer	trips with status 'cancelled'
cancel_pct	decimal	cancelled / trips * 100, rounded to 1 decimal
avg_fare	decimal	average fare of completed trips, rounded to 2 decimals

A cancelled trip has no fare, so it must not drag the average down. If a city has no completed trips, avg_fare is NULL.

Row order doesn’t matter.

Tables: trips.

Approach

One pass over trips, grouped by city, with a different aggregate for each column.

SELECT city,
       COUNT(*) AS trips,
       COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled,
       ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'cancelled') / COUNT(*), 1) AS cancel_pct,
       ROUND(AVG(fare), 2) AS avg_fare
FROM trips
GROUP BY city;

Why it works

FILTER (WHERE ...) limits one aggregate to some rows without dropping those rows from the others. A WHERE status = 'completed' on the whole query would make trips and cancelled wrong.

AVG(fare) ignores NULL inputs, and cancelled trips are exactly the NULL fares. So the average is over completed trips with no extra filter. AVG(COALESCE(fare, 0)) would count each cancellation as a free ride and pull the number down. In a city with only cancellations, AVG sees no values and returns NULL, which is the honest answer.

Under the hood

DuckDB keeps one hash-table entry per city. Each entry holds a few running states: a row count, a filtered count, and a sum plus a non-NULL count for AVG. Every row updates all of them in one pass. Adding more aggregates to a GROUP BY costs a few bytes per group, not another scan.