Coding questions · Meta
Analyze Group Call Adoption Using SQL Queries
This question evaluates SQL data-manipulation and analytical skills, including aggregation, conditional counting, distinct counts, date-based grouping, and percentage calculations on event logs.
CALL_LOGS
| call_id | user_id | call_start | call_end | is_group_call | participant_cnt |
| 101 | 12 | 2023-08-01 10:00:00 | 2023-08-01 10:15:00 | 0 | 2 |
| 102 | 34 | 2023-08-01 10:05:00 | 2023-08-01 10:30:00 | 1 | 4 |
| 103 | 56 | 2023-08-01 11:00:00 | 2023-08-01 11:20:00 | 1 | 3 |
| 104 | 12 | 2023-08-02 09:00:00 | 2023-08-02 09:10:00 | 0 | 2 |
| 105 | 78 | 2023-08-02 09:15:00 | 2023-08-02 09:40:00 | 1 | 5 |
Scenario
A messaging app is launching a new "group call" feature. You have access to historical call data and need to quantify early adoption of the feature.
Question
Using the call_logs table, write SQL to:
For each calendar day, return (a) total calls, (b) total group calls, (c) unique callers who initiated a group call, and (d) percentage of calls that are group calls.
List all days where group-call percentage is below 10%.
Hints
DATE(call_start), COUNT(*), SUM(is_group_call), COUNT(DISTINCT CASE WHEN is_group_call=1 THEN user_id END)
Daily Group Call Adoption Metrics
Table CALL_LOGS
| column | type | constraints |
|---|---|---|
| call_id | INTEGER | PRIMARY KEY, NOT NULL |
| user_id | INTEGER | NOT NULL |
| call_start | TIMESTAMP | NOT NULL |
| call_end | TIMESTAMP | NOT NULL |
| is_group_call | INTEGER | NOT NULL, CHECK (is_group_call IN (0,1)) |
| participant_cnt | INTEGER | NOT NULL, CHECK (participant_cnt >= 2) |
Sample data:
| call_end | call_id | call_start | is_group_call | participant_cnt | user_id |
|---|---|---|---|---|---|
| 2023-08-01 10:15:00 | 101 | 2023-08-01 10:00:00 | 0 | 2 | 12 |
| 2023-08-01 10:30:00 | 102 | 2023-08-01 10:05:00 | 1 | 4 | 34 |
| 2023-08-01 11:20:00 | 103 | 2023-08-01 11:00:00 | 1 | 3 | 56 |
| 2023-08-02 09:10:00 | 104 | 2023-08-02 09:00:00 | 0 | 2 | 12 |
| 2023-08-02 09:40:00 | 105 | 2023-08-02 09:15:00 | 1 | 5 | 78 |
Expected output
One row per day with call_date, total_calls, total_group_calls, unique_group_callers, and group_call_pct (0–100, rounded to 2 decimals), filtered to days where group_call_pct < 10. With the provided data: 2023-08-01 has 3 total calls and 2 group calls (66.67%); 2023-08-02 has 2 total calls and 1 group call (50.00%). No day is below 10%, so the result set is empty.
Hints
- Group by DATE(call_start) to aggregate per day.
- Use SUM(is_group_call) to count group calls if is_group_call is 0/1.
- Use COUNT(DISTINCT CASE WHEN is_group_call = 1 THEN user_id END) for unique group callers.
- Compute the percentage as SUM(is_group_call) * 100.0 / COUNT(*), then filter by < 10.