Drill 1 of 3 in Ride-sharing questions
0

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…