Monthly Completed Trips, GMV and Average Fare

Medium
311.

Monthly Completed Trips, GMV and Average Fare

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

ColumnTypeValues
ride_idint
rider_idint
requested_atdatetime
statusenumcompleted, cancelled
fareint

Primary key: ride_id.

fare is in rupees; a cancelled ride may still carry the fare quoted at booking.

Example

The rows must come back in the order shown.

Ride

ride_idrider_idrequested_atstatusfare
1412024-01-05 09:00:00completed240
2422024-01-18 19:30:00completed410
3412024-01-31 23:59:59cancelled300
4432024-02-01 00:00:10completed180
5442024-02-14 21:10:00completed520
6422024-02-20 08:45:00completed330
7452024-03-03 12:00:00cancelled260

Output

monthcompleted_tripsgmvavg_fare
2024-012650325
2024-0231030343.33
2024-0300NULL
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
ride_idint
rider_idint
requested_atdatetime
statusenum(completed, cancelled)
fareint

Primary key: ride_id.

fare is in rupees; a cancelled ride may still carry the fare quoted at booking.

Examples

Example 1

Ride

ride_idrider_idrequested_atstatusfare
1412024-01-05 09:00:00completed240
2422024-01-18 19:30:00completed410
3412024-01-31 23:59:59cancelled300
4432024-02-01 00:00:10completed180
5442024-02-14 21:10:00completed520
6422024-02-20 08:45:00completed330
7452024-03-03 12:00:00cancelled260

Output

monthcompleted_tripsgmvavg_fare
2024-012650325
2024-0231030343.33
2024-0300NULL

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 month

Another 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 →