ASAmol Shukla

20. Real life: ride-hailing

Step 1 of 4 · Lesson

Lesson 20 of 21

Real life database practice: ride-hailing (50 SQL problems)

Practise 50 SQL problems on a 2,000-trip ride-hailing database — drivers, riders, fares, surge and city performance, checked in your browser.

200 min 3 min read 50 problems · 0 solved City Rides (2,000 trips)

In short

What does the ride-hailing practice database contain?

200 drivers, 500 riders and 2,000 trips across ten cities. Each trip has a rider, an optional driver, distance, fare, status and surge multiplier. Revenue means SUM(fare) over completed trips only — the other statuses store 0. The database is already selected in every problem.

By the end of this lesson you can

  • Handle optional relationships such as a missing driver
  • Separate completed revenue from cancelled and unmatched trips
  • Report per city, vehicle type and month
  • Use window functions for rankings and month-over-month change

The database you are practising on

City Rides is a fictional ride-hailing service: 200 drivers, 500 riders and 2,000 trips. A trip records the rider, the driver (or NULL when nobody accepted), distance, fare, status, payment mode and surge multiplier. Cancellations and no-driver trips carry a fare of 0, so revenue questions must filter them out.

The whole set is fictional but shaped like the real thing: keys, foreign keys, dates, statuses and 2,000 rows in the busiest table. You do not import anything to start — practice mode loads the same rows into your browser. When you want the same data in MySQL Workbench, download the script (3 tables, 20 columns) and run it there.

What one row means

Read the grain before you write a join. Joining two tables without knowing what one row of each means is the single most common cause of doubled totals.

Table One row is Columns you will use
drivers one driver driver_id, driver_name, city, vehicle_type, rating, joined_on
riders one rider rider_id, rider_name, city, signup_date, wallet_balance
trips one requested trip trip_id, rider_id, driver_id, trip_date, distance_km, fare, status, payment_mode, surge_multiplier

Your database is already selected

Every problem below runs against this database only. The code arena preselects it and does not show a database dropdown, so you never see MySQL's ERROR 1046: No database selected here.

You write one query, press Check answer, and it is compared row by row with the reference answer — same rows, same columns, any valid query wins. Use Hint first, Compare with the reference answer once you have tried, and Reset database if you changed rows while experimenting.

Common mistakes

Wrong

AVG(fare) over all trips

Fix

Filter status = 'completed' first, or use AVG(CASE WHEN status = 'completed' THEN fare END).

Why: Cancelled and unmatched trips have fare 0, so the average is pulled down and no longer means 'average completed fare'.

Wrong

Counting trips after joining drivers with LEFT JOIN and then COUNT(driver_id)

Fix

Use COUNT(*) to count trips, or COUNT(driver_id) only when you mean 'trips with a driver'.

Why: COUNT(column) skips NULLs, so unmatched trips disappear from the count.

Wrong

Dividing fare by distance without guarding zero

Fix

Filter to completed trips (all have a positive distance) or wrap the division in NULLIF.

Why: A zero distance would make the query fail or return NULL.

Check yourself

  1. Can you say in one sentence what one row of drivers means?
  2. Can you write a query that returns exactly one number from a 2,000-row table without scrolling the result?
  3. Can you explain why two of your joins could double a total, and how you avoided it?

Answer out loud or in your notebook. If one is hard, reread that section before practising.

Frequently asked questions

Do I need to install MySQL and import this data?

No. The code arena loads the rides database in your browser and preselects it for every problem. Download the .sql script to run the same queries in Workbench.

Are the answers checked automatically?

Yes. Your query and the reference answer run on fresh copies of the same rows and the results are compared row by row, so any correct query is accepted.

Is the ride data real?

No. It is fictional and generated from a fixed seed so every learner sees the same rows and the same expected answers.

Quick quiz — 3 questions

Quick Check: Real life: ride-hailing

1 / 3
Why can driver_id be NULL on a trip?

Answers — Real life: ride-hailing

  1. Easy. List drivers rated 4.5 or higher. Target: 68 rows · driver_id, driver_name, rating · (22, Arjun Verma, 5); (165, Ira Reddy, 5) …

    SELECT driver_id, driver_name, rating FROM drivers WHERE rating >= 4.5 ORDER BY rating DESC, driver_id;

  2. Easy. Show the 20 longest completed trips. Target: 20 rows · trip_id, distance_km, fare · (915, 39.98, 615.71); (640, 39.96, 679.36) …

    SELECT trip_id, distance_km, fare FROM trips WHERE status = 'completed' ORDER BY distance_km DESC, trip_id LIMIT 20;

  3. Easy. List riders with a wallet balance above 1,000. Target: 20 rows · rider_id, rider_name, wallet_balance · (205, Nisha Kaur, 1988.52); (384, Simran Rao, 1987.32) …

    SELECT rider_id, rider_name, wallet_balance FROM riders WHERE wallet_balance > 1000 ORDER BY wallet_balance DESC, rider_id LIMIT 20;

  4. Easy. List each vehicle type once. Target: 5 rows · vehicle_type · (auto); (bike) …

    SELECT DISTINCT vehicle_type FROM drivers ORDER BY vehicle_type;

  5. Easy. List drivers who joined before 2021. Target: 65 rows · driver_name, joined_on · (Varun Kaur, 2019-01-10); (Aditi Reddy, 2019-01-14) …

    SELECT driver_name, joined_on FROM drivers WHERE joined_on < '2021-01-01' ORDER BY joined_on, driver_id;

  6. Easy. Show the 20 most expensive completed trips. Target: 20 rows · trip_id, fare, distance_km · (504, 1238.44, 33.29); (1807, 1135.82, 32.23) …

    SELECT trip_id, fare, distance_km FROM trips WHERE status = 'completed' ORDER BY fare DESC, trip_id LIMIT 20;

  7. Easy. List trips where no driver was found. Target: 20 rows · trip_id, rider_id, trip_date · (32, 348, 2025-07-18); (53, 277, 2025-12-15) …

    SELECT trip_id, rider_id, trip_date FROM trips WHERE status = 'no_driver' ORDER BY trip_id LIMIT 20;

  8. Easy. List riders who live in Mumbai. Target: 20 rows · rider_id, rider_name · (2, Simran Das); (4, Zara Bhat) …

    SELECT rider_id, rider_name FROM riders WHERE city = 'Mumbai' ORDER BY rider_id LIMIT 20;

  9. Easy. Count all trips. Target: 1 row · trips · (2000)

    SELECT COUNT(*) AS trips FROM trips;

  10. Easy. Show the first 20 trips paid by card. Target: 20 rows · trip_id, trip_date, fare · (6, 2024-02-20, 62.9); (9, 2024-09-14, 271.75) …

    SELECT trip_id, trip_date, fare FROM trips WHERE payment_mode = 'card' ORDER BY trip_id LIMIT 20;

  11. Medium. List completed trips between 10 and 20 km. Target: 20 rows · trip_id, distance_km, fare · (978, 10.01, 170.13); (758, 10.03, 210.51) …

    SELECT trip_id, distance_km, fare FROM trips WHERE status = 'completed' AND distance_km BETWEEN 10 AND 20 ORDER BY distance_km, trip_id LIMIT 20;

  12. Medium. List SUV and premium drivers. Target: 90 rows · driver_id, driver_name, vehicle_type · (7, Pranav Das, premium); (9, Kabir Khan, suv) …

    SELECT driver_id, driver_name, vehicle_type FROM drivers WHERE vehicle_type IN ('suv', 'premium') ORDER BY driver_id;

  13. Medium. List riders whose name starts with A. Target: 20 rows · rider_id, rider_name · (7, Aarav Reddy); (10, Aarav Khan) …

    SELECT rider_id, rider_name FROM riders WHERE rider_name LIKE 'A%' ORDER BY rider_id LIMIT 20;

  14. Medium. List trips from the first quarter of 2025. Target: 20 rows · trip_id, trip_date · (1415, 2025-01-01); (1421, 2025-01-01) …

    SELECT trip_id, trip_date FROM trips WHERE trip_date >= '2025-01-01' AND trip_date < '2025-04-01' ORDER BY trip_date, trip_id LIMIT 20;

  15. Medium. Show fare per kilometre for the first 20 completed trips. Target: 20 rows · trip_id, fare, distance_km, fare_per_km · (2, 372.64, 30.24, 12.32); (3, 88.7, 4.87, 18.21) …

    SELECT trip_id, fare, distance_km, ROUND(fare / distance_km, 2) AS fare_per_km FROM trips WHERE status = 'completed' ORDER BY trip_id LIMIT 20;

  16. Medium. Label each completed trip as Short, Medium or Long. Target: 20 rows · trip_id, distance_km, band · (2, 30.24, Long); (3, 4.87, Short) …

    SELECT trip_id, distance_km, CASE WHEN distance_km < 5 THEN 'Short' WHEN distance_km < 15 THEN 'Medium' ELSE 'Long' END AS band FROM trips WHERE status = 'completed' ORDER BY trip_id LIMIT 20;

  17. Medium. List completed trips with a surge multiplier above 1. Target: 20 rows · trip_id, surge_multiplier, fare · (131, 2, 511.3); (141, 2, 901.84) …

    SELECT trip_id, surge_multiplier, fare FROM trips WHERE status = 'completed' AND surge_multiplier > 1 ORDER BY surge_multiplier DESC, trip_id LIMIT 20;

  18. Medium. List riders who signed up during 2024. Target: 20 rows · rider_id, rider_name, signup_date · (12, Vihaan Mehta, 2024-05-13); (30, Kavya Mehta, 2024-07-30) …

    SELECT rider_id, rider_name, signup_date FROM riders WHERE signup_date >= '2024-01-01' AND signup_date < '2025-01-01' ORDER BY rider_id LIMIT 20;

  19. Medium. Show each driver's city in capitals. Target: 20 rows · driver_name, city · (Riya Verma, PUNE); (Sneha Verma, BENGALURU) …

    SELECT driver_name, UPPER(city) AS city FROM drivers ORDER BY driver_id LIMIT 20;

  20. Medium. Count how many different cities riders come from. Target: 1 row · cities · (10)

    SELECT COUNT(DISTINCT city) AS cities FROM riders;

  21. Medium. Show each rider's name and its character length. Target: 20 rows · rider_name, name_length · (Kavya Nair, 10); (Simran Das, 10) …

    SELECT rider_name, LENGTH(rider_name) AS name_length FROM riders ORDER BY rider_id LIMIT 20;

  22. Medium. List trips with no driver assigned. Target: 20 rows · trip_id, rider_id, status · (32, 348, no_driver); (53, 277, no_driver) …

    SELECT trip_id, rider_id, status FROM trips WHERE driver_id IS NULL ORDER BY trip_id LIMIT 20;

  23. Hard. Count trips by status. Target: 3 rows · status, trips · (completed, 1776); (cancelled, 133) …

    SELECT status, COUNT(*) AS trips FROM trips GROUP BY status ORDER BY trips DESC, status;

  24. Hard. Show completed revenue per vehicle type. Target: 5 rows · vehicle_type, revenue · (bike, 143939.9); (suv, 135792.31) …

    SELECT d.vehicle_type, SUM(t.fare) AS revenue FROM drivers d JOIN trips t ON t.driver_id = d.driver_id WHERE t.status = 'completed' GROUP BY d.vehicle_type ORDER BY revenue DESC, d.vehicle_type;

  25. Hard. Show the average completed fare per driver city. Target: 10 rows · city, avg_fare · (Bengaluru, 341.08); (Ludhiana, 340.85) …

    SELECT d.city, ROUND(AVG(t.fare), 2) AS avg_fare FROM drivers d JOIN trips t ON t.driver_id = d.driver_id WHERE t.status = 'completed' GROUP BY d.city ORDER BY avg_fare DESC, d.city;

  26. Hard. List drivers with more than 15 completed trips. Target: 7 rows · driver_id, driver_name, trips · (3, Ishaan Singh, 17); (53, Pooja Bose, 17) …

    SELECT d.driver_id, d.driver_name, COUNT(*) AS trips FROM drivers d JOIN trips t ON t.driver_id = d.driver_id WHERE t.status = 'completed' GROUP BY d.driver_id, d.driver_name HAVING COUNT(*) > 15 ORDER BY trips DESC, d.driver_id LIMIT 20;

  27. Hard. Show the 5 cities with the most riders. Target: 5 rows · city, riders · (Mumbai, 67); (Ahmedabad, 59) …

    SELECT city, COUNT(*) AS riders FROM riders GROUP BY city ORDER BY riders DESC, city LIMIT 5;

  28. Hard. Show completed revenue per month of 2025. Target: 12 rows · month, revenue · (2025-01, 24555.38); (2025-02, 25060.49) …

    SELECT DATE_FORMAT(trip_date, '%Y-%m') AS month, SUM(fare) AS revenue FROM trips WHERE status = 'completed' AND trip_date >= '2025-01-01' AND trip_date < '2026-01-01' GROUP BY month ORDER BY month;

  29. Hard. Show the average driver rating per vehicle type. Target: 5 rows · vehicle_type, avg_rating · (auto, 4.36); (bike, 4.3) …

    SELECT vehicle_type, ROUND(AVG(rating), 2) AS avg_rating FROM drivers GROUP BY vehicle_type ORDER BY avg_rating DESC, vehicle_type;

  30. Hard. Count trips per month of 2025. Target: 12 rows · month, trips · (2025-01, 78); (2025-02, 87) …

    SELECT DATE_FORMAT(trip_date, '%Y-%m') AS month, COUNT(*) AS trips FROM trips WHERE trip_date >= '2025-01-01' AND trip_date < '2026-01-01' GROUP BY month ORDER BY month;

  31. Hard. List drivers with no trips at all. Target: 0 rows · driver_id, driver_name · 

    SELECT d.driver_id, d.driver_name FROM drivers d LEFT JOIN trips t ON t.driver_id = d.driver_id WHERE t.trip_id IS NULL ORDER BY d.driver_id LIMIT 20;

  32. Hard. Show the average distance by trip status. Target: 3 rows · status, avg_distance · (no_driver, 21.6); (cancelled, 19.92) …

    SELECT status, ROUND(AVG(distance_km), 2) AS avg_distance FROM trips GROUP BY status ORDER BY avg_distance DESC, status;

  33. Hard. Show the cancellation rate per rider city. Target: 10 rows · city, cancelled_pct · (Pune, 10.8); (Chennai, 8.1) …

    SELECT r.city, ROUND(100 * SUM(CASE WHEN t.status = 'cancelled' THEN 1 ELSE 0 END) / COUNT(*), 1) AS cancelled_pct FROM riders r JOIN trips t ON t.rider_id = r.rider_id GROUP BY r.city ORDER BY cancelled_pct DESC, r.city;

  34. Hard. List riders with more than 5 completed trips. Target: 20 rows · rider_id, rider_name, trips · (97, Rahul Gill, 9); (440, Harpreet Rao, 9) …

    SELECT r.rider_id, r.rider_name, COUNT(*) AS trips FROM riders r JOIN trips t ON t.rider_id = r.rider_id WHERE t.status = 'completed' GROUP BY r.rider_id, r.rider_name HAVING COUNT(*) > 5 ORDER BY trips DESC, r.rider_id LIMIT 20;

  35. Hard. Show vehicle types with an average completed fare above 400. Target: 0 rows · vehicle_type, avg_fare · 

    SELECT d.vehicle_type, ROUND(AVG(t.fare), 2) AS avg_fare FROM drivers d JOIN trips t ON t.driver_id = d.driver_id WHERE t.status = 'completed' GROUP BY d.vehicle_type HAVING AVG(t.fare) > 400 ORDER BY avg_fare DESC, d.vehicle_type;

  36. Hard. Show completed revenue by payment mode. Target: 4 rows · payment_mode, trips, revenue · (upi, 813, 258264.2); (cash, 532, 175749.19) …

    SELECT payment_mode, COUNT(*) AS trips, SUM(fare) AS revenue FROM trips WHERE status = 'completed' GROUP BY payment_mode ORDER BY revenue DESC, payment_mode;

  37. Professional. Show the 10 drivers with the highest completed revenue. Target: 10 rows · driver_id, driver_name, vehicle_type, revenue · (53, Pooja Bose, suv, 6430.71); (75, Rohan Bose, sedan, 6141.64) …

    SELECT d.driver_id, d.driver_name, d.vehicle_type, SUM(t.fare) AS revenue FROM drivers d JOIN trips t ON t.driver_id = d.driver_id WHERE t.status = 'completed' GROUP BY d.driver_id, d.driver_name, d.vehicle_type ORDER BY revenue DESC, d.driver_id LIMIT 10;

  38. Professional. Rank drivers by completed revenue. Target: 20 rows · driver_name, revenue, rnk · (Pooja Bose, 6430.71, 1); (Rohan Bose, 6141.64, 2) …

    WITH per AS (SELECT d.driver_id, d.driver_name, SUM(t.fare) AS revenue FROM drivers d JOIN trips t ON t.driver_id = d.driver_id WHERE t.status = 'completed' GROUP BY d.driver_id, d.driver_name) SELECT driver_name, revenue, RANK() OVER (ORDER BY revenue DESC) AS rnk FROM per ORDER BY rnk, driver_id LIMIT 20;

  39. Professional. Show 2025 monthly revenue with a running total. Target: 12 rows · month, revenue, running_total · (2025-01, 24555.38, 24555.38); (2025-02, 25060.49, 49615.87) …

    WITH m AS (SELECT DATE_FORMAT(trip_date, '%Y-%m') AS month, SUM(fare) AS revenue FROM trips WHERE status = 'completed' AND trip_date >= '2025-01-01' AND trip_date < '2026-01-01' GROUP BY month) SELECT month, revenue, SUM(revenue) OVER (ORDER BY month) AS running_total FROM m ORDER BY month;

  40. Professional. Show the month-over-month change in trips for 2025. Target: 12 rows · month, trips, prev_trips, change · (2025-01, 78, NULL, NULL); (2025-02, 87, 78, 9) …

    WITH m AS (SELECT DATE_FORMAT(trip_date, '%Y-%m') AS month, COUNT(*) AS trips FROM trips WHERE trip_date >= '2025-01-01' AND trip_date < '2026-01-01' GROUP BY month) SELECT month, trips, LAG(trips) OVER (ORDER BY month) AS prev_trips, trips - LAG(trips) OVER (ORDER BY month) AS change FROM m ORDER BY month;

  41. Professional. List riders who never completed a trip. Target: 16 rows · rider_id, rider_name · (29, Varun Mehta); (39, Nisha Rao) …

    SELECT r.rider_id, r.rider_name FROM riders r WHERE NOT EXISTS (SELECT 1 FROM trips t WHERE t.rider_id = r.rider_id AND t.status = 'completed') ORDER BY r.rider_id LIMIT 20;

  42. Professional. Show each rider's longest completed trip. Target: 20 rows · rider_id, trip_id, trip_date, distance_km · (1, 593, 2025-05-01, 37.87); (2, 1312, 2025-01-11, 38.9) …

    WITH r AS (SELECT rider_id, trip_id, trip_date, distance_km, ROW_NUMBER() OVER (PARTITION BY rider_id ORDER BY distance_km DESC, trip_id) AS rn FROM trips WHERE status = 'completed') SELECT rider_id, trip_id, trip_date, distance_km FROM r WHERE rn = 1 ORDER BY rider_id LIMIT 20;

  43. Professional. Show each vehicle type's share of completed revenue. Target: 5 rows · vehicle_type, revenue, pct · (bike, 143939.9, 24.9); (suv, 135792.31, 23.5) …

    SELECT d.vehicle_type, SUM(t.fare) AS revenue, ROUND(100 * SUM(t.fare) / (SELECT SUM(fare) FROM trips WHERE status = 'completed'), 1) AS pct FROM drivers d JOIN trips t ON t.driver_id = d.driver_id WHERE t.status = 'completed' GROUP BY d.vehicle_type ORDER BY revenue DESC, d.vehicle_type;

  44. Professional. List drivers whose revenue beats their city's average. Target: 20 rows · driver_name, city, revenue, city_avg · (Pooja Bose, Delhi, 6430.71, 3364.74); (Rohan Bose, Chennai, 6141.64, 3138.75) …

    WITH per AS (SELECT d.driver_id, d.driver_name, d.city, SUM(t.fare) AS revenue FROM drivers d JOIN trips t ON t.driver_id = d.driver_id WHERE t.status = 'completed' GROUP BY d.driver_id, d.driver_name, d.city), city_avg AS (SELECT city, AVG(revenue) AS avg_revenue FROM per GROUP BY city) SELECT p.driver_name, p.city, p.revenue, ROUND(c.avg_revenue, 2) AS city_avg FROM per p JOIN city_avg c ON c.city = p.city WHERE p.revenue > c.avg_revenue ORDER BY p.revenue DESC, p.driver_id LIMIT 20;

  45. Professional. Build a 2025 monthly dashboard: trips, revenue and average distance. Target: 12 rows · month, trips, revenue, avg_distance · (2025-01, 78, 24555.38, 21.01); (2025-02, 87, 25060.49, 20.18) …

    SELECT DATE_FORMAT(trip_date, '%Y-%m') AS month, COUNT(*) AS trips, SUM(fare) AS revenue, ROUND(AVG(distance_km), 2) AS avg_distance FROM trips WHERE trip_date >= '2025-01-01' AND trip_date < '2026-01-01' GROUP BY month ORDER BY month;

  46. Professional. Show the first completed trip of every driver. Target: 20 rows · driver_id, trip_id, trip_date · (1, 1502, 2024-07-15); (2, 955, 2024-02-20) …

    WITH r AS (SELECT driver_id, trip_id, trip_date, ROW_NUMBER() OVER (PARTITION BY driver_id ORDER BY trip_date, trip_id) AS rn FROM trips WHERE status = 'completed') SELECT driver_id, trip_id, trip_date FROM r WHERE rn = 1 ORDER BY driver_id LIMIT 20;

  47. Professional. List riders with more than 2 cancelled trips. Target: 1 row · rider_id, rider_name, cancelled · (56, Tanvi Khan, 3)

    SELECT r.rider_id, r.rider_name, COUNT(*) AS cancelled FROM riders r JOIN trips t ON t.rider_id = r.rider_id WHERE t.status = 'cancelled' GROUP BY r.rider_id, r.rider_name HAVING COUNT(*) > 2 ORDER BY cancelled DESC, r.rider_id LIMIT 20;

  48. Professional. List riders whose total spend beats the average rider spend. Target: 20 rows · rider_id, rider_name, spend · (173, Ishaan Sharma, 3136.1); (102, Yash Mehta, 3034.51) …

    WITH per AS (SELECT rider_id, SUM(fare) AS spend FROM trips WHERE status = 'completed' GROUP BY rider_id) SELECT r.rider_id, r.rider_name, p.spend FROM per p JOIN riders r ON r.rider_id = p.rider_id WHERE p.spend > (SELECT AVG(spend) FROM per) ORDER BY p.spend DESC, r.rider_id LIMIT 20;

  49. Professional. Build a city dashboard: trips, completed revenue and average driver rating. Target: 10 rows · city, trips, revenue, avg_surge · (Mumbai, 261, 76802.69, 1.1); (Ahmedabad, 227, 69980.73, 1.11) …

    SELECT r.city, COUNT(*) AS trips, SUM(CASE WHEN t.status = 'completed' THEN t.fare ELSE 0 END) AS revenue, ROUND(AVG(t.surge_multiplier), 2) AS avg_surge FROM riders r JOIN trips t ON t.rider_id = r.rider_id GROUP BY r.city ORDER BY revenue DESC, r.city;

  50. Professional. Show trips and completed revenue by weekday. Target: 7 rows · weekday, trips, revenue · (Monday, 299, 86354.24); (Saturday, 292, 85957.59) …

    SELECT DAYNAME(trip_date) AS weekday, COUNT(*) AS trips, SUM(CASE WHEN status = 'completed' THEN fare ELSE 0 END) AS revenue FROM trips GROUP BY weekday ORDER BY revenue DESC, weekday;

Up next · Lesson 21

Real life: streaming service

200 min · 50 practice problems

Start next lesson
Real life: college records Real life: streaming service