Meta: Messenger Video Call Minutes by Day of the Week
The Messenger calling team wants to know on which days of the week people make video calls, to plan when to roll out a new video codec.
- Difficulty: Easy
- Topics: Dates, Aggregation
- Dialect: MySQL
- Problem: #753
Problem statement
The Messenger calling team wants to know on which days of the week people make video calls, to plan when to roll out a new video codec.
Considering video calls only, return one row per weekday that has at least one, with the columns day_name (the full English name, e.g. Monday), calls (the number of video calls started that day of the week) and total_minutes (their summed duration_minutes). Order the rows by the weekday, Monday first and Sunday last.
A practice scenario set at Meta: the tables and data are invented, and this is not a question from, or endorsed by, Meta.
Tables
Table: MessengerCall
| Column | Type |
|---|---|
| call_id | int |
| caller_id | int |
| call_type | enum(audio, video) |
| started_at | datetime |
| duration_minutes | int |
Primary key: call_id.
One row per connected call; duration_minutes is the call's length in whole minutes.
Examples
Example 1
MessengerCall
| call_id | caller_id | call_type | started_at | duration_minutes |
|---|---|---|---|---|
| 1 | 10 | video | 2025-06-02 20:10:00 | 25 |
| 2 | 11 | audio | 2025-06-02 21:00:00 | 40 |
| 3 | 12 | video | 2025-06-04 19:30:00 | 12 |
| 4 | 10 | video | 2025-06-08 11:00:00 | 60 |
| 5 | 13 | video | 2025-06-09 08:15:00 | 5 |
| 6 | 14 | audio | 2025-06-06 18:00:00 | 15 |
| 7 | 12 | video | 2025-06-07 22:45:00 | 33 |
Output
| day_name | calls | total_minutes |
|---|---|---|
| Monday | 2 | 30 |
| Wednesday | 1 | 12 |
| Saturday | 1 | 33 |
| Sunday | 1 | 60 |
How to solve Meta: Messenger Video Call Minutes by Day of the Week
Filter to video calls, then group by the day of the week each call started on and count and sum within each group. DAYNAME(started_at) gives the label the statement wants, so it is the grouping key.
The order is the catch. Sorting by day_name would be alphabetical — Friday, Monday, Saturday … — so the query sorts by a number instead. WEEKDAY() returns 0 for Monday up to 6 for Sunday, exactly the requested order; it is added to GROUP BY (it is determined by the day name anyway) so that it may be used in ORDER BY under strict grouping rules. Weekdays with no video call form no group and are simply absent.
The alternative builds the same number from DAYOFWEEK() (Sunday 1 … Saturday 7): MOD(DAYOFWEEK(d) + 5, 7) maps Monday to 0 and Sunday to 6, computed in a derived table so the outer query can group and sort by it. Both are a single scan.
Reference solution (MySQL)
SELECT DAYNAME(started_at) AS day_name, COUNT(*) AS calls, SUM(duration_minutes) AS total_minutes
FROM MessengerCall
WHERE call_type = 'video'
GROUP BY DAYNAME(started_at), WEEKDAY(started_at)
ORDER BY WEEKDAY(started_at)Another way
SELECT day_name, COUNT(*) AS calls, SUM(duration_minutes) AS total_minutes
FROM (SELECT DAYNAME(started_at) AS day_name, MOD(DAYOFWEEK(started_at) + 5, 7) AS wd, duration_minutes
FROM MessengerCall WHERE call_type = 'video') v
GROUP BY day_name, wd
ORDER BY wd← Meta: Threads Posts Cut Off in the Feed Preview · Meta: Moderation Reports Reviewed Past Their SLA →