Uber-style Company ChallengeEasyVerified answerSQLite live

Total Fare by City

What is the total fare revenue collected in each city for completed trips?

  • Joins
  • Aggregation
  • Filtering
  • Sorting

Challenge brief

Understand the request

City Operations is comparing revenue performance across markets to allocate driver incentive budgets.

Calculate total fare per city from completed trips, ordered by revenue descending.

Return

  • city_name
  • total_fare

Constraints

  • Include only trips whose status is 'completed'
  • Order by total fare descending, then city ID

Data you will use

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

cities

  • city_idINTEGER
  • city_nameVARCHAR(100)

trips

  • trip_idINTEGER
  • city_idINTEGER
  • fare_amountREAL
  • trip_statusVARCHAR(20)

Hints, when you need them

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

Hint 1

Trip fares are in trips. City names are in cities. The trips table has a city_id column. JOIN on city_id, filter to completed trips, SUM fare_amount per city.

Hint 2

INNER JOIN cities to trips on city_id. WHERE trip_status = 'completed'. GROUP BY city. SUM(t.fare_amount) AS total_fare. ORDER BY total_fare DESC.

Hint 3

Filter trip facts before grouping them by city, then compute the city revenue total and add a stable tie-break.

Verified SQL answer

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

Reveal solution and explanation
SELECT c.city_name, SUM(t.fare_amount) AS total_fare FROM cities c INNER JOIN trips t ON c.city_id = t.city_id WHERE t.trip_status = 'completed' GROUP BY c.city_id, c.city_name ORDER BY total_fare DESC, c.city_id

Why this works

San Francisco has 3 trips (1001, 1002, 1006) totalling $85.50. New York has 2 trips (1003, 1004) totalling $83. LA has 1 trip (1005) at $35.

Success check

3 cities — San Francisco leads ($85.50), then New York ($83), then LA ($35)

Expected result

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

city_nametotal_fare
San Francisco85.5
New York83
Los Angeles35

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.