Monthly Completed Trips, GMV and Average Fare — SQL Problem
The finance team's monthly dashboard groups ride requests by the month they were made (requested_at as 'YYYY-MM').
- Difficulty: Medium
- Topics: Dates, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #311
Problem statement
The finance team's monthly dashboard groups ride requests by the month they were made (requested_at as 'YYYY-MM'). Every month with at least one request appears, even if nothing was completed.
Return month, completed_trips (completed requests), gmv (the sum of fare over completed requests, 0 if none) and avg_fare (the average completed fare rounded to 2 decimals, NULL if none). Cancelled requests count in no column. Order by month ascending.
Tables
Table: Ride
| Column | Type |
|---|---|
| ride_id | int |
| rider_id | int |
| requested_at | datetime |
| status | enum(completed, cancelled) |
| fare | int |
Primary key: ride_id.
fare is in rupees; a cancelled ride may still carry the fare quoted at booking.
Examples
Example 1
Ride
| ride_id | rider_id | requested_at | status | fare |
|---|---|---|---|---|
| 1 | 41 | 2024-01-05 09:00:00 | completed | 240 |
| 2 | 42 | 2024-01-18 19:30:00 | completed | 410 |
| 3 | 41 | 2024-01-31 23:59:59 | cancelled | 300 |
| 4 | 43 | 2024-02-01 00:00:10 | completed | 180 |
| 5 | 44 | 2024-02-14 21:10:00 | completed | 520 |
| 6 | 42 | 2024-02-20 08:45:00 | completed | 330 |
| 7 | 45 | 2024-03-03 12:00:00 | cancelled | 260 |
Output
| month | completed_trips | gmv | avg_fare |
|---|---|---|---|
| 2024-01 | 2 | 650 | 325 |
| 2024-02 | 3 | 1030 | 343.33 |
| 2024-03 | 0 | 0 | NULL |
How to solve Monthly Completed Trips, GMV and Average Fare
Bucket every ride by DATE_FORMAT(requested_at, '%Y-%m') and group by it. Because the statement wants months with only cancellations too, group all rides and decide per column what counts, instead of filtering cancelled rows in WHERE (which would delete such a month).
Conditional aggregation does the rest. SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) counts completed rides; the same with fare in place of 1 sums GMV and yields 0 for a month without completions. For the average, leave out the ELSE: the CASE is NULL for cancelled rides, AVG skips NULLs, and a month with no completed ride averages nothing and gives NULL — exactly the rule asked for. The quoted fare on a cancelled ride is thus ignored everywhere.
The alternative lists the months first and LEFT JOINs the completed rides to them, then needs COALESCE for the GMV. The single-pass CASE version is one scan and one group-by.
Reference solution (MySQL)
SELECT DATE_FORMAT(requested_at, '%Y-%m') AS month,
SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_trips,
SUM(CASE WHEN status = 'completed' THEN fare ELSE 0 END) AS gmv,
ROUND(AVG(CASE WHEN status = 'completed' THEN fare END), 2) AS avg_fare
FROM Ride
GROUP BY DATE_FORMAT(requested_at, '%Y-%m')
ORDER BY monthAnother way
SELECT m.month, COUNT(c.ride_id) AS completed_trips, COALESCE(SUM(c.fare), 0) AS gmv, ROUND(AVG(c.fare), 2) AS avg_fare
FROM (SELECT DISTINCT LEFT(requested_at, 7) AS month FROM Ride) m
LEFT JOIN Ride c ON LEFT(c.requested_at, 7) = m.month AND c.status = 'completed'
GROUP BY m.month ORDER BY m.month← Latest Tracking Scan for Each Parcel · Fleet Utilisation by Vehicle Type for One Week →