Coding questions · Snapchat
Compute same-day acceptance metrics last week
This question evaluates proficiency in time-based data manipulation, SQL JOINs and aggregations, handling NULLs and missing reference rows, and computing acceptance and percentage metrics with careful UTC date boundary treatment.
Assume today is 2025-09-01; interpret 'last week' as 2025-08-25 through 2025-08-31 inclusive, using UTC dates. You have the following schema and sample data.
Schema:
- friendships(requester_id INT, addressee_id INT, requested_at TIMESTAMP, approved_at TIMESTAMP NULL)
- users(user_id INT PRIMARY KEY, spam BOOLEAN)
Sample tables (UTC timestamps):
Users
+---------+------+
| user_id | spam |
+---------+------+
| 1 | TRUE |
| 2 | FALSE|
| 3 | FALSE|
| 4 | TRUE |
| 5 | FALSE|
+---------+------+
Friendships
+--------------+--------------+---------------------+---------------------+
| requester_id | addressee_id | requested_at | approved_at |
+--------------+--------------+---------------------+---------------------+
| 2 | 1 | 2025-08-25 10:00:00 | 2025-08-25 18:00:00 |
| 3 | 5 | 2025-08-25 23:30:00 | 2025-08-26 00:05:00 |
| 4 | 2 | 2025-08-26 12:00:00 | NULL |
| 6 | 3 | 2025-08-27 09:00:00 | 2025-08-27 09:15:00 |
| 5 | 3 | 2025-08-27 22:59:00 | 2025-08-28 22:59:00 |
| 2 | 6 | 2025-08-31 01:00:00 | 2025-08-31 01:05:00 |
| 1 | 3 | 2025-08-24 11:00:00 | 2025-08-24 12:00:00 |
| 3 | 2 | 2025-08-30 23:50:00 | 2025-08-30 23:59:59 |
+--------------+--------------+---------------------+---------------------+
Tasks:
- Write a single SQL query that returns, for each date in 2025-08-25..2025-08-31 (UTC), the columns: day (DATE), same_day_accepts, requests, same_day_accept_rate. Define same-day accept as DATE(requested_at)=DATE(approved_at). Include days with zero requests (i.e., emit 7 rows).
- Write SQL to compute the percentage of friendship requests created last week where the requester is NOT spam (users.spam=false). Use only requests with requested_at in 2025-08-25..2025-08-31 (UTC). Report the percentage to two decimals.
- If users is not comprehensive (some requesters are missing from users), write SQL that returns three percentages for last week: (a) known_only_pct (exclude rows where requester not in users), (b) pessimistic_pct (treat all unknown requesters as spam), and (c) optimistic_pct (treat all unknown requesters as not spam). Also return the counts used for each denominator.
- List at least three edge cases you considered (e.g., NULL approved_at, approvals falling outside the window, duplicate requests between the same pair, timezone cutoffs), and briefly state how your SQL in (1)-(3) handles each. Be explicit about using UTC date boundaries.
Daily same-day acceptance metrics for a fixed week
You are given two tables, friendships and users, with UTC timestamps. For the week from 2025-08-25 to 2025-08-31 inclusive (in UTC), write a single SQL query that returns one row per calendar day with the following columns:
- day (DATE in UTC)
- same_day_accepts: number of friendship requests where the request was both created and approved on that same UTC calendar date (i.e., CAST(requested_at AS DATE) = CAST(approved_at AS DATE))
- requests: total number of friendship requests created on that date (based on requested_at)
- same_day_accept_rate: same_day_accepts divided by requests, rounded to two decimal places. For days with zero requests, return 0.00 for same_day_accept_rate.
Include all 7 days in the output, even if a day has zero requests and zero same-day accepts.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY |
| spam | BOOLEAN | NOT NULL |
Sample data:
| spam | user_id |
|---|---|
| true | 1 |
| false | 2 |
| false | 3 |
| true | 4 |
| false | 5 |
Table friendships
| column | type | constraints |
|---|---|---|
| requester_id | INT | NOT NULL |
| addressee_id | INT | NOT NULL |
| requested_at | TIMESTAMP | NOT NULL |
| approved_at | TIMESTAMP |
Sample data:
| addressee_id | approved_at | requested_at | requester_id |
|---|---|---|---|
| 1 | 2025-08-25 18:00:00 | 2025-08-25 10:00:00 | 2 |
| 5 | 2025-08-26 00:05:00 | 2025-08-25 23:30:00 | 3 |
| 2 | NULL | 2025-08-26 12:00:00 | 4 |
| 3 | 2025-08-27 09:15:00 | 2025-08-27 09:00:00 | 6 |
| 3 | 2025-08-28 22:59:00 | 2025-08-27 22:59:00 | 5 |
| 6 | 2025-08-31 01:05:00 | 2025-08-31 01:00:00 | 2 |
| 3 | 2025-08-24 12:00:00 | 2025-08-24 11:00:00 | 1 |
| 2 | 2025-08-30 23:59:59 | 2025-08-30 23:50:00 | 3 |
Expected output
One row per UTC date from 2025-08-25 to 2025-08-31, with counts of total requests and same-day approved requests, plus the same-day acceptance rate rounded to two decimals. Days with no requests appear with zero counts and a rate of 0.00.
| day | requests | same_day_accept_rate | same_day_accepts |
|---|---|---|---|
| 2025-08-25 | 2 | 0.5 | 1 |
| 2025-08-26 | 1 | 0 | 0 |
| 2025-08-27 | 2 | 0.5 | 1 |
| 2025-08-28 | 0 | 0 | 0 |
| 2025-08-29 | 0 | 0 | 0 |
| 2025-08-30 | 1 | 1 | 1 |
| 2025-08-31 | 1 | 1 | 1 |
Hints
- Use a date-generating function (e.g., generate_series) to ensure all days in the range appear, then left join the aggregates.
- Aggregate by CAST(requested_at AS DATE) and use a filtered COUNT to compute same-day accepts.
- Handle days with zero requests using COALESCE and guard against division by zero.
Percentage of requests from non-spam requesters in a fixed week
Using the same friendships and users tables, compute the percentage of friendship requests created between 2025-08-25 00:00:00 and 2025-09-01 00:00:00 UTC (i.e., requests whose requested_at falls on UTC dates 2025-08-25 through 2025-08-31) where the requester is NOT spam (users.spam = FALSE).
Include all friendship requests in that time window in the denominator, even if the requester does not appear in the users table. For the numerator, count only those requests whose requester is present in users with spam = FALSE. Return a single row with the percentage as a numeric value rounded to two decimal places.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY |
| spam | BOOLEAN | NOT NULL |
Sample data:
| spam | user_id |
|---|---|
| true | 1 |
| false | 2 |
| false | 3 |
| true | 4 |
| false | 5 |
Table friendships
| column | type | constraints |
|---|---|---|
| requester_id | INT | NOT NULL |
| addressee_id | INT | NOT NULL |
| requested_at | TIMESTAMP | NOT NULL |
| approved_at | TIMESTAMP |
Sample data:
| addressee_id | approved_at | requested_at | requester_id |
|---|---|---|---|
| 1 | 2025-08-25 18:00:00 | 2025-08-25 10:00:00 | 2 |
| 5 | 2025-08-26 00:05:00 | 2025-08-25 23:30:00 | 3 |
| 2 | NULL | 2025-08-26 12:00:00 | 4 |
| 3 | 2025-08-27 09:15:00 | 2025-08-27 09:00:00 | 6 |
| 3 | 2025-08-28 22:59:00 | 2025-08-27 22:59:00 | 5 |
| 6 | 2025-08-31 01:05:00 | 2025-08-31 01:00:00 | 2 |
| 3 | 2025-08-24 12:00:00 | 2025-08-24 11:00:00 | 1 |
| 2 | 2025-08-30 23:59:59 | 2025-08-30 23:50:00 | 3 |
Expected output
A single-row result with the percentage of requests in 2025-08-25..2025-08-31 UTC whose requester is known and marked as non-spam (users.spam = FALSE), out of all requests in that time range. Unknown requesters still count in the denominator but not in the non-spam numerator.
| non_spam_requester_pct |
|---|
| 71.43 |
Hints
- Filter friendships by the requested_at timestamp to the given UTC window.
- Use a LEFT JOIN so that requests from unknown requesters still contribute to the denominator.
- Compute the percentage as SUM(CASE WHEN u.spam = FALSE THEN 1 END) divided by COUNT(*), multiplied by 100 and rounded.
Known-only, pessimistic, and optimistic spam-adjusted percentages
Assume the users table may be incomplete: some requesters appearing in friendships might not exist in users. For the same time window (requests with requested_at between 2025-08-25 00:00:00 and 2025-09-01 00:00:00 UTC), write SQL that returns a single row with the following six columns:
- known_only_pct: percentage of requests whose requester is in users and has spam = FALSE, out of all requests whose requester is present in users (i.e., excluding unknown requesters from the denominator).
- known_only_denominator: the number of requests whose requester is present in users.
- pessimistic_pct: percentage of requests whose requester is in users and has spam = FALSE, out of all requests in the window (treat all unknown requesters as spam for this metric).
- pessimistic_denominator: the total number of requests in the window.
- optimistic_pct: percentage of requests whose requester is either known with spam = FALSE or unknown (treat all unknown requesters as not spam), out of all requests in the window.
- optimistic_denominator: the total number of requests in the window.
All percentages should be rounded to two decimal places.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY |
| spam | BOOLEAN | NOT NULL |
Sample data:
| spam | user_id |
|---|---|
| true | 1 |
| false | 2 |
| false | 3 |
| true | 4 |
| false | 5 |
Table friendships
| column | type | constraints |
|---|---|---|
| requester_id | INT | NOT NULL |
| addressee_id | INT | NOT NULL |
| requested_at | TIMESTAMP | NOT NULL |
| approved_at | TIMESTAMP |
Sample data:
| addressee_id | approved_at | requested_at | requester_id |
|---|---|---|---|
| 1 | 2025-08-25 18:00:00 | 2025-08-25 10:00:00 | 2 |
| 5 | 2025-08-26 00:05:00 | 2025-08-25 23:30:00 | 3 |
| 2 | NULL | 2025-08-26 12:00:00 | 4 |
| 3 | 2025-08-27 09:15:00 | 2025-08-27 09:00:00 | 6 |
| 3 | 2025-08-28 22:59:00 | 2025-08-27 22:59:00 | 5 |
| 6 | 2025-08-31 01:05:00 | 2025-08-31 01:00:00 | 2 |
| 3 | 2025-08-24 12:00:00 | 2025-08-24 11:00:00 | 1 |
| 2 | 2025-08-30 23:59:59 | 2025-08-30 23:50:00 | 3 |
Expected output
A single-row result containing three percentages and their denominators for the 2025-08-25..2025-08-31 UTC window: (1) known_only_pct over known requesters, (2) pessimistic_pct over all requests treating unknowns as spam, and (3) optimistic_pct over all requests treating unknowns as non-spam.
| known_only_denominator | known_only_pct | optimistic_denominator | optimistic_pct | pessimistic_denominator | pessimistic_pct |
|---|---|---|---|---|---|
| 6 | 83.33 | 7 | 85.71 | 7 | 71.43 |
Hints
- Start by building a filtered friendships + users result for the given time window using a LEFT JOIN.
- Compute counts for: total requests, known (in users), unknown (not in users), and known non-spam.
- Express each metric as a ratio of the appropriate counts, guarding against division by zero and rounding to two decimals.