Interview Question Bank · COUNT(*) FILTER · share of a group
1. Share of trips with surge pricing
EasyRide-sharingQuestion #121
Concept: COUNT(*) FILTER · share of a groupshow
To get the share of rows that meet a condition, count the matching rows and divide by the count of all rows in the group. COUNT(*) FILTER (WHERE condition) counts only the matching rows, while COUNT(*) counts them all. Decide what the denominator is before you write it: a NULL in the tested column fails the condition, but the row still belongs in the total.
Example (different data):
SELECT warehouse, COUNT(*) AS shipments, COUNT(*) FILTER (WHERE days_late > 0) AS late, ROUND(100.0 * COUNT(*) FILTER (WHERE days_late > 0) / COUNT(*), 1) AS late_pct FROM shipments GROUP BY warehouse;
Your task
The pricing team at Rydora, a ride-hailing startup, wants to know how often riders in each city actually pay a surge fare.
For completed trips only, return city, completed_trips, surge_trips (completed trips with surge_multiplier above 1.00) and surge_pct (surge_trips as a percentage of completed_trips, ROUND(..., 1)). A NULL surge_multiplier means no surge was applied. Sort by surge_pct highest first, then city.
Starting Postgres in your browser…
Checking your access…