Identify Top 10 Users by Average Call Duration
MetaData Scientist · Onsite
Last updated
This question evaluates SQL-based data manipulation and time-series aggregation skills, specifically computing per-user metrics from timestamped event data such as call durations.
video_calls
| call_id | user_id | start_time | end_time |
|---|---|---|---|
| 1 | 101 | 2023-07-01 09:00:00 | 2023-07-01 09:45:00 |
| 2 | 102 | 2023-07-01 10:15:00 | 2023-07-01 10:40:00 |
| 3 | 101 | 2023-07-02 14:05:00 | 2023-07-02 14:25:00 |
| 4 | 103 | 2023-07-02 15:00:00 | 2023-07-02 16:30:00 |
| 5 | 102 | 2023-07-03 11:20:00 | 2023-07-03 12:00:00 |
Scenario
A video-calling product team wants to know which users had the longest average call duration over the last 7 days.
Question
Write a SQL query that returns the top 10 users by average call duration (in minutes) for calls started in the last 7 days.
Hints
Compute duration as TIMESTAMPDIFF, filter on start_time, use ORDER BY and LIMIT.
Solution walkthrough is for members
Get the full walkthrough with reference code and complexity analysis, plus Kitty Coach at 300 messages a day. Hints, the code runner and community answers stay free.