Skip to content
Tutorials Center
My learning

Topics

Lesson 119 of 125 ยท Projects

Project: Ride Booking Analysis

Analyze a ride-booking service with SQL. Break down ride outcomes, find peak hours, compare peak and off-peak cancellations, define surge windows, rank driver revenue and study ratings.

When you open a ride-booking app and request a car, a lot can happen. A driver accepts and takes you there, or no driver is found, or you cancel while waiting, or the driver cancels on you. Every one of those outcomes costs the company money or trust, and the operations team watches them daily. Prices rise when demand outruns supply (surge pricing), and drivers get ratings that decide who the app promotes.

In this project you will analyze two weeks of requests from the rides sample database: 25 drivers, 90 riders and 718 ride requests from 3 to 16 November 2025, with pickup zone, status, distance, surge multiplier, fare and rating. You'll break down outcomes, find the peak hours, compare peak and off-peak cancellations, define surge windows, rank drivers by revenue and see how surge affects ratings.

You'll use joins, GROUP BY and HAVING, CASE, date and time functions, window functions and CTEs. If you've done the E-commerce Funnel Analytics project, a ride request is just a funnel with four possible endings.

The tables

Table One row is...
drivers a driver with their vehicle (sedan, SUV or van)
riders a rider account
rides one request: rider, driver (NULL if none was found), requested_at, pickup_zone, status, distance_km, surge, fare_cents and rating

The status column has four values: completed, cancelled_by_rider, cancelled_by_driver and no_driver. Only completed rides have a distance, a fare and a rating. A surge of 1.0 is normal pricing, 1.5 means the fare is multiplied by 1.5. Times are text in YYYY-MM-DD HH:MM:SS form, which sorts correctly and works with strftime to extract the hour or the weekday.

Stage 1: outcomes

The first question is always "how many requests actually became rides?" That completion rate is the health number of the whole business:

SQL Outcomes of every request ยท database: rides

Press Run (or Ctrl + Enter) to see the result here.

Of 718 requests, 568 were completed (79.1%). Riders cancelled 63 (8.8%), the system found no driver for 46 (6.4%) and drivers cancelled 41 (5.7%). So 150 requests (20.9%) ended without a ride, and the company can influence the last two groups, which are the supply side. The second query lists the three worst days; 11 November had the lowest completion rate, 69.4% of its 49 requests. A single bad day is a lead for the operations team: was it weather, a local event or a shortage of drivers?

Stage 2: the shape of a day

When do people request rides? Group by the hour of the request. SQLite has no REPEAT function, but replace(hex(zeroblob(n)), '00', '#') builds a string of n hash marks, which turns a result table into a quick text bar chart:

SQL Requests by hour of the day ยท database: rides

Press Run (or Ctrl + Enter) to see the result here.

There are two clear peaks: the morning commute (7:00 to 9:00, with 42, 46 and 35 requests) and the evening commute (16:00 to 19:00, topping out at 71 requests at 18:00, the busiest hour of the day), with a smaller bump at lunchtime (41 at 12:00). Between 2:00 and 4:00 there are almost no requests. Weekdays average 55.3 requests a day against 41.3 on weekends. Note that the second query divides by the number of days of each type: there are ten weekdays and four weekend days in the data, so comparing raw totals would be misleading. The %w format gives the weekday number from 0 (Sunday) to 6 (Saturday).

Stage 3: do peaks hurt reliability?

Intuition says that when demand is highest, more things go wrong. Test it by labeling each hour as peak (7 to 9 and 17 to 19) or off-peak and comparing the outcome mix. Each outcome becomes a percentage of the period's requests, using conditional aggregation:

SQL Peak versus off-peak outcomes ยท database: rides

Press Run (or Ctrl + Enter) to see the result here.

Intuition holds. Peak hours complete 74.0% of requests against 82.6% off-peak, and the gap is entirely on the supply side: no_driver more than doubles (9.9% against 4.0%) and cancelled_by_driver rises from 4.7% to 7.2%, while riders cancel at the same rate (8.9% and 8.7%). The CTE labels each row once, so the CASE is not repeated and the GROUP BY can use the label. The second query asks whether cancellations are concentrated in a few drivers. It joins to drivers, which automatically drops the no_driver rows (their driver_id is NULL), and uses HAVING COUNT(*) >= 20 so that a driver with 2 assignments and 1 cancellation doesn't show 50%. The worst driver cancels 15.4% of assigned rides (4 of 26), and each of the top four cancelled exactly 4 rides, so there is no outlier to remove: the problem is systemic, and a policy answer such as a cancellation fee fits better than singling someone out.

A SUM over a comparison such as SUM(status = 'completed') counts the rows where the comparison is true, because SQLite stores true as 1. In PostgreSQL write COUNT(*) FILTER (WHERE status = 'completed'), and in SQL Server or Oracle use SUM(CASE WHEN ... THEN 1 ELSE 0 END).

Stage 4: surge windows

Surge pricing raises fares when requests outnumber available drivers. A surge window is an hour (or an hour in a zone) where surge is the norm rather than the exception. Define it with two numbers: the share of rides with surge > 1 and the average multiplier, and keep only hours with enough requests to be meaningful.

SQL Surge windows by hour and by zone ยท database: rides

Press Run (or Ctrl + Enter) to see the result here.

Exactly six hours qualify: 7:00, 8:00, 9:00 and 17:00, 18:00, 19:00, the two commutes. In each of them at least 77% of the rides were surged and the average multiplier is between 1.32 and 1.39. The busiest hour, 18:00, has 89% of its rides surged. The second query finds hot spots: Downtown at 8:00 and 9:00 (an average multiplier of 1.56 in both), Downtown at 7:00 (1.47), and the evening peak in Eastside (1.41 at 18:00) and the morning peak in Northside (1.41 at 9:00). Both queries use HAVING to enforce a minimum size, because an hour with 3 rides and one surge would otherwise top the list.

A surge window answers "when" and "where". A pricing team would add "for how long" and compare it with driver supply, which this data doesn't have.

Stage 5: driver revenue

The next report ranks drivers by the revenue they brought in from completed rides. Start the aggregation from drivers with a LEFT JOIN so that a driver with no completed rides still appears (with zero), and use window functions for the rank and for a cumulative share that shows how concentrated the revenue is:

SQL Driver ranking and revenue concentration ยท database: rides

Press Run (or Ctrl + Enter) to see the result here.

Uma Jensen leads with 778.28 from 28 rides, followed by Yusuf Jensen (771.01) and Esme Vargas (688.45). The top five drivers together earn 26.3% of the revenue and the top six 31.2%, so out of 25 drivers the income is spread fairly evenly: no single driver or small group carries the business. Compare that with the e-commerce project, where a few customers drove most of the revenue. Two details matter in the query. The LEFT JOIN puts the status = 'completed' test in the join condition, not in WHERE; a WHERE filter would remove drivers with no completed rides and turn the join back into an inner join. And SUM(...) OVER (ORDER BY ... DESC, id) is a running total, with id as a tie-breaker so equal revenues don't share a running value.

Stage 6: ratings and surge

Do riders rate drivers lower when they paid a surge price? Only completed rides can have a rating, and riders rate roughly seven rides out of ten, so AVG(rating) quietly ignores the unrated ones (and COUNT(rating) tells you how many there were). Compare the two groups and add the full distribution:

SQL Do surge prices lower ratings? ยท database: rides

Press Run (or Ctrl + Enter) to see the result here.

Rides at normal prices average 4.24 stars (233 rated of 327 rides), while surged rides average 4.02 (165 of 241). The breakdown by multiplier shows the 1.5x group is the lowest at 3.80, so the effect is real in this data but not smooth: the 2x group, with only 36 rides, bounces back to 4.05, which is a reminder that small groups are noisy. In the distribution, 4 stars is the mode (222 ratings, 55.8%), 5 stars 117 and 3 stars 59; nobody gave 1 or 2 stars here. The SUM(COUNT(*)) OVER () pattern computes each rating's share of the total: the window function runs after the GROUP BY and sums the group counts.

An average rating hides how many people didn't rate. Always report the rated count beside it, and be careful about causes: surge rides happen at the commute hours, so lower ratings could reflect traffic and waiting times rather than the price. To separate the two, compare surged and normal rides within the same hour.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does the day-type query divide by COUNT(DISTINCT date(requested_at))?

    The data has ten weekdays and only four weekend days, so raw totals would make weekdays look busier for a simple counting reason. Requests per day compares like with like.
  2. 2. In the driver-revenue query the condition r.status = 'completed' is in the ON clause. What goes wrong if it is moved to WHERE?

    In a LEFT JOIN, a driver without matching rides has NULL in r.status. A WHERE test on r.status rejects those rows, which turns the LEFT JOIN into an inner join. Conditions on the right-hand table belong in ON.
  3. 3. What does HAVING COUNT(*) >= 20 protect against in the driver-cancellation query?

    A rate from very few rides is unreliable: one cancellation out of two rides is 50%. A minimum number of rides keeps the ranking meaningful.
  4. 4. Why does an average rating need the rated count beside it?

    AVG skips NULL values. If only a quarter of the rides were rated, the average describes that quarter, so readers need COUNT(rating) to judge how much to trust it.

Summary

  • Start with the outcome mix (completed, cancelled by rider or driver, no driver) and a daily completion rate; they show where to dig.
  • Group by strftime('%H', ...) for hours and %w for weekdays, and divide by the number of days when comparing day types.
  • Conditional aggregation (SUM(status = '...') or SUM(CASE ...)) turns one pass over the data into several percentages.
  • Define surge windows with a share and an average multiplier, and always add a minimum request count with HAVING.
  • Put conditions on the right-hand table of a LEFT JOIN in ON; use window functions for ranks and cumulative shares.
  • Report the number of ratings next to any average rating, and compare groups within the same hour before blaming price.

Next: SQL Cheat Sheet, a one-page summary of the statements you have been using.