Uber-style Company ChallengeHardVerified answerSQLite live

Trip Distance Segmentation

For completed trips, what are the count, average fare, and total fare in each distance tier?

  • Aggregation
  • CASE expressions
  • Numeric functions
  • Filtering
  • Sorting

Challenge brief

Understand the request

Pricing Strategy needs trip volume and revenue metrics for short, medium, and long distance bands.

Segment trips into Short, Medium, and Long tiers using CASE WHEN on distance_miles.

Return

  • distance_tier
  • trip_count
  • avg_fare (rounded 2)
  • total_fare (rounded 2)

Constraints

  • Classify completed trips as Short below 8 miles, Medium from 8 up to 14 miles, and Long at 14 miles or more
  • Use only completed trips
  • Order by average fare descending, then distance tier

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

trips

  • trip_idINTEGER
  • distance_milesREAL
  • fare_amountREAL
  • trip_statusVARCHAR(20)

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

All data is in trips. Use CASE WHEN on distance_miles to assign each trip to a tier. GROUP BY that CASE expression and aggregate COUNT, AVG, SUM.

Hint 2

CASE WHEN distance_miles < 8 THEN 'Short' WHEN distance_miles < 14 THEN 'Medium' ELSE 'Long' END AS distance_tier. WHERE completed. GROUP BY distance_tier. COUNT(*), ROUND(AVG(fare_amount),2), ROUND(SUM(fare_amount),2). ORDER BY avg_fare DESC.

Hint 3

Derive the business distance label for each completed trip, aggregate by that label, and sort the resulting tiers deterministically.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT CASE WHEN distance_miles < 8 THEN 'Short (<8 mi)' WHEN distance_miles < 14 THEN 'Medium (8-14 mi)' ELSE 'Long (14+ mi)' END AS distance_tier, COUNT(*) AS trip_count, ROUND(AVG(fare_amount), 2) AS avg_fare, ROUND(SUM(fare_amount), 2) AS total_fare FROM trips WHERE trip_status = 'completed' GROUP BY distance_tier ORDER BY avg_fare DESC, distance_tier

Why this works

CASE WHEN evaluates top-down: < 8 mi catches short trips, < 14 mi catches medium (only reached when first condition failed), ELSE catches long. Trips pair evenly: 5.2+6.8=Short, 8.5+12.3=Medium, 14.8+18.2=Long.

Success check

3 tiers, ordered Long, Medium, then Short by average fare

Expected result

Use this output to verify values, aliases, ordering, and row count.

distance_tiertrip_countavg_faretotal_fare
Long (14+ mi)250100
Medium (8-14 mi)231.563
Short (<8 mi)220.2540.5

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Explore related company challenges

SQL Interview Practice →

Return to the complete interview preparation experience.