Coding questions · Meta
Compute High-Call Usage Rates
This question evaluates a candidate's ability to perform advanced data manipulation and analytical SQL tasks, including joins, aggregations, timezone-aware date handling, rolling window metrics, distinct user counting, and counting user participations across roles.
You are given two tables for a voice-calling product:
users
user_idBIGINTcountry_codeSTRING
calls
call_idBIGINTcaller_idBIGINTrecipient_idBIGINTstarted_atTIMESTAMP
calls.caller_id and calls.recipient_id both reference users.user_id.
Assume:
- Every row in
callsis one valid call event. - All timestamps are stored in UTC.
- Any date-based metric should be computed in the
Europe/Londontimezone. report_dateis a calendar date inEurope/London.
Write SQL to compute the following metrics for each report_date:
-
Percentage of callers with at least 20 calls in the last 7 days
- Use the 7-day window ending on
report_date, inclusive. - The numerator is the number of distinct users who initiated at least 20 calls in that 7-day window.
- The denominator is the number of distinct users who initiated at least 1 call in that same 7-day window.
- Use the 7-day window ending on
-
Percentage of Great Britain DAU with at least 50 total calls
- Restrict to users with
country_code = 'GB'. - Define daily active users as distinct GB users who either placed or received at least one call on
report_date. - For each such user, count total call participations on
report_date, where participations include both outgoing calls ascaller_idand incoming calls asrecipient_id. - The numerator is the number of GB DAU with at least 50 total call participations on
report_date. - The denominator is all GB DAU on
report_date.
- Restrict to users with
Return one row per report_date with these columns:
report_datepct_callers_20plus_last_7dpct_gb_dau_50plus_calls
Compute High-Call Usage Rates
Table users
| column | type | constraints |
|---|---|---|
| user_id | BIGINT | PRIMARY KEY, NOT NULL |
| country_code | VARCHAR(2) | NOT NULL |
Sample data:
| country_code | user_id |
|---|---|
| GB | 1 |
| GB | 2 |
| US | 3 |
| GB | 4 |
| FR | 5 |
Table calls
| column | type | constraints |
|---|---|---|
| call_id | BIGINT | PRIMARY KEY, NOT NULL |
| caller_id | BIGINT | NOT NULL, FOREIGN KEY users(user_id) |
| recipient_id | BIGINT | NOT NULL, FOREIGN KEY users(user_id) |
| started_at | TIMESTAMP | NOT NULL |
Sample data:
| call_id | caller_id | recipient_id | started_at |
|---|---|---|---|
| 1001 | 1 | 3 | 2023-01-07 10:00:00 |
| 1002 | 1 | 3 | 2023-01-07 10:01:00 |
| 1003 | 1 | 3 | 2023-01-07 10:02:00 |
| 1004 | 1 | 3 | 2023-01-07 10:03:00 |
| 1005 | 1 | 3 | 2023-01-07 10:04:00 |
| 1006 | 1 | 3 | 2023-01-07 10:05:00 |
| 1007 | 1 | 3 | 2023-01-07 10:06:00 |
| 1008 | 1 | 3 | 2023-01-07 10:07:00 |
| 1009 | 1 | 3 | 2023-01-07 10:08:00 |
| 1010 | 1 | 3 | 2023-01-07 10:09:00 |
| 1011 | 1 | 3 | 2023-01-07 10:10:00 |
| 1012 | 1 | 3 | 2023-01-07 10:11:00 |
| 1013 | 1 | 3 | 2023-01-07 10:12:00 |
| 1014 | 1 | 3 | 2023-01-07 10:13:00 |
| 1015 | 1 | 3 | 2023-01-07 10:14:00 |
| 1016 | 1 | 3 | 2023-01-07 10:15:00 |
| 1017 | 1 | 3 | 2023-01-07 10:16:00 |
| 1018 | 1 | 3 | 2023-01-07 10:17:00 |
| 1019 | 1 | 3 | 2023-01-07 10:18:00 |
| 1020 | 1 | 3 | 2023-01-07 10:19:00 |
| 1021 | 1 | 3 | 2023-01-07 10:20:00 |
| 1022 | 1 | 3 | 2023-01-07 10:21:00 |
| 1023 | 1 | 3 | 2023-01-07 10:22:00 |
| 1024 | 1 | 3 | 2023-01-07 10:23:00 |
| 1025 | 1 | 3 | 2023-01-07 10:24:00 |
| 1026 | 1 | 3 | 2023-01-07 10:25:00 |
| 1027 | 1 | 3 | 2023-01-07 10:26:00 |
| 1028 | 1 | 3 | 2023-01-07 10:27:00 |
| 1029 | 1 | 3 | 2023-01-07 10:28:00 |
| 1030 | 1 | 3 | 2023-01-07 10:29:00 |
| 1031 | 1 | 3 | 2023-01-07 10:30:00 |
| 1032 | 1 | 3 | 2023-01-07 10:31:00 |
| 1033 | 1 | 3 | 2023-01-07 10:32:00 |
| 1034 | 1 | 3 | 2023-01-07 10:33:00 |
| 1035 | 1 | 3 | 2023-01-07 10:34:00 |
| 1036 | 1 | 3 | 2023-01-07 10:35:00 |
| 1037 | 1 | 3 | 2023-01-07 10:36:00 |
| 1038 | 1 | 3 | 2023-01-07 10:37:00 |
| 1039 | 1 | 3 | 2023-01-07 10:38:00 |
| 1040 | 1 | 3 | 2023-01-07 10:39:00 |
| 1041 | 1 | 3 | 2023-01-07 10:40:00 |
| 1042 | 1 | 3 | 2023-01-07 10:41:00 |
| 1043 | 1 | 3 | 2023-01-07 10:42:00 |
| 1044 | 1 | 3 | 2023-01-07 10:43:00 |
| 1045 | 1 | 3 | 2023-01-07 10:44:00 |
| 1046 | 1 | 3 | 2023-01-07 10:45:00 |
| 1047 | 1 | 3 | 2023-01-07 10:46:00 |
| 1048 | 1 | 3 | 2023-01-07 10:47:00 |
| 1049 | 1 | 3 | 2023-01-07 10:48:00 |
| 1050 | 1 | 3 | 2023-01-07 10:49:00 |
| 2001 | 2 | 3 | 2023-01-02 09:00:00 |
| 2002 | 2 | 5 | 2023-01-02 09:05:00 |
| 2003 | 2 | 3 | 2023-01-02 09:10:00 |
| 2004 | 2 | 5 | 2023-01-02 09:15:00 |
| 2005 | 2 | 3 | 2023-01-03 09:00:00 |
| 2006 | 2 | 5 | 2023-01-03 09:05:00 |
| 2007 | 2 | 3 | 2023-01-03 09:10:00 |
| 2008 | 2 | 5 | 2023-01-03 09:15:00 |
| 2009 | 2 | 3 | 2023-01-04 09:00:00 |
| 2010 | 2 | 5 | 2023-01-04 09:05:00 |
| 2011 | 2 | 3 | 2023-01-04 09:10:00 |
| 2012 | 2 | 5 | 2023-01-04 09:15:00 |
| 2013 | 2 | 3 | 2023-01-05 09:00:00 |
| 2014 | 2 | 5 | 2023-01-05 09:05:00 |
| 2015 | 2 | 3 | 2023-01-05 09:10:00 |
| 2016 | 2 | 5 | 2023-01-05 09:15:00 |
| 2017 | 2 | 3 | 2023-01-06 09:00:00 |
| 2018 | 2 | 5 | 2023-01-06 09:05:00 |
| 2019 | 2 | 3 | 2023-01-06 09:10:00 |
| 3001 | 3 | 5 | 2023-01-03 10:30:00 |
| 4001 | 5 | 3 | 2023-01-04 10:00:00 |
| 4002 | 5 | 3 | 2023-01-04 10:05:00 |
| 4003 | 5 | 3 | 2023-01-04 10:10:00 |
| 4004 | 5 | 3 | 2023-01-04 10:15:00 |
| 4005 | 5 | 3 | 2023-01-04 10:20:00 |
| 8001 | 2 | 3 | 2023-01-08 08:00:00 |
| 8002 | 2 | 3 | 2023-01-08 08:01:00 |
| 8003 | 2 | 3 | 2023-01-08 08:02:00 |
| 8004 | 2 | 3 | 2023-01-08 08:03:00 |
| 8005 | 2 | 3 | 2023-01-08 08:04:00 |
| 8006 | 2 | 3 | 2023-01-08 08:05:00 |
| 8007 | 2 | 3 | 2023-01-08 08:06:00 |
| 8008 | 2 | 3 | 2023-01-08 08:07:00 |
| 8009 | 2 | 3 | 2023-01-08 08:08:00 |
| 8010 | 2 | 3 | 2023-01-08 08:09:00 |
| 8011 | 2 | 3 | 2023-01-08 08:10:00 |
| 8012 | 2 | 3 | 2023-01-08 08:11:00 |
| 8013 | 2 | 3 | 2023-01-08 08:12:00 |
| 8014 | 2 | 3 | 2023-01-08 08:13:00 |
| 8015 | 2 | 3 | 2023-01-08 08:14:00 |
| 8016 | 2 | 3 | 2023-01-08 08:15:00 |
| 8017 | 2 | 3 | 2023-01-08 08:16:00 |
| 8018 | 2 | 3 | 2023-01-08 08:17:00 |
| 8019 | 2 | 3 | 2023-01-08 08:18:00 |
| 8020 | 2 | 3 | 2023-01-08 08:19:00 |
| 8021 | 2 | 3 | 2023-01-08 08:20:00 |
| 8022 | 2 | 3 | 2023-01-08 08:21:00 |
| 8023 | 2 | 3 | 2023-01-08 08:22:00 |
| 8024 | 2 | 3 | 2023-01-08 08:23:00 |
| 8025 | 2 | 3 | 2023-01-08 08:24:00 |
| 8026 | 2 | 3 | 2023-01-08 08:25:00 |
| 8027 | 2 | 3 | 2023-01-08 08:26:00 |
| 8028 | 2 | 3 | 2023-01-08 08:27:00 |
| 8029 | 2 | 3 | 2023-01-08 08:28:00 |
| 8030 | 2 | 3 | 2023-01-08 08:29:00 |
| 8031 | 2 | 3 | 2023-01-08 08:30:00 |
| 8032 | 2 | 3 | 2023-01-08 08:31:00 |
| 8033 | 2 | 3 | 2023-01-08 08:32:00 |
| 8034 | 2 | 3 | 2023-01-08 08:33:00 |
| 8035 | 2 | 3 | 2023-01-08 08:34:00 |
| 8036 | 2 | 3 | 2023-01-08 08:35:00 |
| 8037 | 2 | 3 | 2023-01-08 08:36:00 |
| 8038 | 2 | 3 | 2023-01-08 08:37:00 |
| 8039 | 2 | 3 | 2023-01-08 08:38:00 |
| 8040 | 2 | 3 | 2023-01-08 08:39:00 |
| 8041 | 2 | 3 | 2023-01-08 08:40:00 |
| 8042 | 2 | 3 | 2023-01-08 08:41:00 |
| 8043 | 2 | 3 | 2023-01-08 08:42:00 |
| 8044 | 2 | 3 | 2023-01-08 08:43:00 |
| 8045 | 2 | 3 | 2023-01-08 08:44:00 |
| 8046 | 2 | 3 | 2023-01-08 08:45:00 |
| 8047 | 2 | 3 | 2023-01-08 08:46:00 |
| 8048 | 2 | 3 | 2023-01-08 08:47:00 |
| 8049 | 2 | 3 | 2023-01-08 08:48:00 |
| 8050 | 2 | 3 | 2023-01-08 08:49:00 |
Expected output
One row per report_date with the two requested percentage metrics. Percentages are returned as decimal fractions between 0 and 1.
| pct_callers_20plus_last_7d | pct_gb_dau_50plus_calls | report_date |
|---|---|---|
| 0.25 | 1 | 2023-01-07 |
| 0.5 | 1 | 2023-01-08 |
Hints
- Derive report_date as DATE(started_at). For January dates, Europe/London is aligned with UTC, so no offset is needed.
- Build the 7-day inclusive window as [report_date - 6 days, report_date] and aggregate outgoing calls per caller over that window.
- Compute GB DAU by unioning caller and recipient user_ids per day, joining to users where country_code = 'GB', and counting participations per user.