Identify Top 10 Users by Average Call Duration

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a Medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Onsite rounds at Meta.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Meta during technical interviews.

Question

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.

PracHub · Accepted Answer

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.

Quick Overview

Identify Top 10 Users by Average Call Duration