Uber-style Company ChallengeMediumVerified answerSQLite live

Peak Hour Analysis

How many trips are completed each hour of the day, and what is the average fare for each hour?

  • Aggregation
  • Date analysis
  • Numeric functions
  • Filtering
  • Sorting

Challenge brief

Understand the request

Supply Planning is building a driver scheduling model and needs to understand which hours of day see the most trip activity.

Group completed trips by hour using strftime, showing trip count and average fare.

Return

  • hour_of_day (HH format)
  • trip_count
  • avg_fare (rounded 2)

Constraints

  • Use only trips whose status is 'completed'
  • Group trips by pickup hour
  • Order by trip count descending, then hour descending

Data you will use

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

trips

  • trip_idINTEGER
  • pickup_datetimeDATETIME
  • 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. strftime('%H', pickup_datetime) extracts the 2-digit hour (08, 09, ..., 14). Filter to completed trips, GROUP BY the hour expression, COUNT and AVG.

Hint 2

WHERE trip_status = 'completed'. GROUP BY strftime('%H', pickup_datetime). COUNT(trip_id), ROUND(AVG(fare_amount), 2). ORDER BY trip_count DESC, hour_of_day DESC.

Hint 3

Extract the pickup hour from qualifying trip timestamps, aggregate at that hour grain, and apply both requested sort keys.

Verified SQL answer

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

Reveal solution and explanation
SELECT strftime('%H', pickup_datetime) AS hour_of_day, COUNT(trip_id) AS trip_count, ROUND(AVG(fare_amount), 2) AS avg_fare FROM trips WHERE trip_status = 'completed' GROUP BY strftime('%H', pickup_datetime) ORDER BY trip_count DESC, hour_of_day DESC;

Why this works

strftime('%H', ...) is SQLite's hour extractor — it returns zero-padded strings like '08', '09'. All 6 trips each occur in a distinct hour so every hour has count = 1. The secondary sort by hour_of_day DESC ensures deterministic output.

Success check

6 hours — each hour from 08:00 to 14:00 has exactly 1 trip in the dataset

Expected result

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

hour_of_daytrip_countavg_fare
14122
12135
11155
10128
09145
08118.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.