Analyze Group Call Adoption Using SQL Queries
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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)
Overview: This question evaluates SQL data-manipulation and analytical skills, including aggregation, conditional counting, distinct counts, date-based grouping, and percentage calculations on event logs.
Using the CALL_LOGS table, write a query that, for each calendar day, returns: (a) total calls, (b) total group calls, (c) unique callers who initiated a group call, and (d) the percentage of calls that are group calls (0–100, rounded to 2 decimals). Then return only the days where the group-call percentage is below 10%.
Tables
CALL_LOGS(call_id INTEGER, user_id INTEGER, call_start TIMESTAMP, call_end TIMESTAMP, is_group_call INTEGER, participant_cnt INTEGER)
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.