Driver Idle Time Between Rides

SQL practice question · Hard · Window Functions · Uber

For each driver, look at each trip and the driver's next trip (by `start_ts`). The idle gap is the minutes between the trip's `end_ts` and the next trip's `start_ts`. Only count gaps where the next trip starts on the same calendar day as the previous trip ended AND the gap is at most 120 minutes (longer gaps are shift breaks). Return `driver_id`, `idle_gaps` (number of qualifying gaps), `avg_idle_min` (ROUND 1). Drivers with no qualifying gap are excluded. Order by `avg_idle_min`, then `driver_id`.

Table: ride_trips

Schema of ride_trips
ColumnType
trip_idINTEGER
driver_idINTEGER
start_tsTIMESTAMP
end_tsTIMESTAMP
Sample rows of ride_trips
trip_iddriver_idstart_tsend_ts
1102026-04-01 08:00:002026-04-01 08:20:00
2102026-04-01 08:35:002026-04-01 09:00:00
3102026-04-01 09:10:002026-04-01 09:40:00
4102026-04-01 13:00:002026-04-01 13:30:00
5102026-04-02 07:00:002026-04-02 07:30:00
6112026-04-01 22:00:002026-04-01 23:50:00
7112026-04-02 00:10:002026-04-02 00:30:00
8112026-04-02 00:40:002026-04-02 01:00:00
Expected output on the sample data
driver_ididle_gapsavg_idle_min
11110
10212.5

Solve it in the live editor on this page — run on sample data, then submit against hidden tests.

‹ Driver Longest Active-Day Streak · Watch Sessions with 30-Minute Timeout ›

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Question Asked in Interview Blog
SQL Interview Practice

Company-wise SQL questions

{{ totalCount }} questions in the style asked at Amazon, Microsoft, Google, Meta, Flipkart and more — {{ hardPct }}% Hard. Write SQL in a real database engine in your browser, run it on sample data, then evaluate against hidden tests.

🏆 Weekly SQL Contest
{{ totalCount }}
Questions
{{ companyCount }}
Companies
{{ prog.total.solved }}
Solved
Bookmark Question Companies Difficulty Category Status
{{ r.n }} - {{ r.title }} {{ c }} {{ r.difficulty }} {{ r.category }} {{ r.statusIcon }}
No questions match these filters.
Showing {{ rangeLabel }} of {{ filteredCount }}

Company tags show where this type of question is commonly reported in interviews. Questions, data and solutions are written by TechTown.
← All questions

{{ cur.n }} - {{ cur.title }}

🏆 Contest {{ contestWeek }}
{{ cur.catLabel }} {{ cur.diffLabel }} {{ c }}
Embed this questionPaste on a blog, course page or LMS. Free to use — educator guide 
{{ embedCode }}

{{ cur.statement }}

Table: {{ tb.name }}
ColumnType {{ c.name }}{{ c.type }}
{{ h }} {{ v.text }}
Expected output on the sample data:
{{ h }} {{ v.text }}
  • {{ h }}

No submissions yet. Press Evaluate to submit your query.
{{ s.verdict }} {{ s.meta }}
The solution unlocks after you solve this question or after 3 evaluations that didn't pass ({{ failCount }}/3 so far). Trying first is the fastest way to learn. 
{{ solutionSql }}
  1. {{ e }}
⏱ {{ timerLabel }}
{{ fnHelpTitle }}
{{ fnHelpText }}
Writing {{ dialectName }} · Ctrl/Cmd + Enter runs the query · Tab indents · your code is saved automatically
Output {{ out.status }}
  • {{ w }}
    •
{{ out.verdict }}
{{ out.detail }}
 
{{ out.error }}
Expected (sample data)
{{ h }}{{ v.text }}
Your output (sample data)
{{ h }}{{ v.text }}
{{ h }} {{ v.text }}
Run your query to see the result here.
🔒

To Track Your Progress, Log In / Sign Up

You have solved your 3 free questions — nice work! Log in (free) to unlock all 150 questions, keep your streak and appear on the leaderboard.

Sign Up Free Log In