Compute same-day acceptance metrics last week
Company: Snapchat
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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:
1) 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).
2) 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.
3) 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.
4) 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.
Overview: 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.
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.
Tables
users(user_id INT, spam BOOLEAN)
friendships(requester_id INT, addressee_id INT, requested_at TIMESTAMP, approved_at TIMESTAMP)
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.
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.
Tables
users(user_id INT, spam BOOLEAN)
friendships(requester_id INT, addressee_id INT, requested_at TIMESTAMP, approved_at TIMESTAMP)
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.
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.
Tables
users(user_id INT, spam BOOLEAN)
friendships(requester_id INT, addressee_id INT, requested_at TIMESTAMP, approved_at TIMESTAMP)
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.