Monitor Friend-Request System for Quality and Abuse
Company: Snapchat
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Friendship
+--------------+-------------+---------------------+---------------------+
| requester_id | approver_id | request_ts | approval_ts |
+--------------+-------------+---------------------+---------------------+
| 1 | 2 | 2023-10-01 10:00:00 | 2023-10-01 12:00:00 |
| 3 | 4 | 2023-10-01 11:00:00 | NULL |
| 5 | 6 | 2023-10-02 09:00:00 | 2023-10-02 09:05:00 |
| 7 | 8 | 2023-10-02 14:00:00 | 2023-10-05 10:00:00 |
+--------------+-------------+---------------------+---------------------+
Users
+---------+---------+
| user_id | is_spam |
+---------+---------+
| 1 | F |
| 2 | F |
| 3 | T |
| 4 | F |
| 5 | F |
+---------+---------+
##### Scenario
Friend-request system wants to monitor quality and abuse for the past week using Friendship and Users tables.
##### Question
Q1. Write an SQL query that returns each of the last 7 calendar days together with the same-day acceptance rate (approvals that occurred on the same date as the request divided by total requests that day). Q2. Write an SQL query that yields the percentage of friendship requests last week that did NOT originate from accounts marked spam = 'T'. Q3. The Users table may be incomplete (new users not yet present). Propose at least one data or query change to make Q1-Q2 robust, and list the key hypotheses and edge cases you would validate when interpreting the results.
##### Hints
Think DATE(request_ts)=DATE(approval_ts); left joins to Users; NULL handling, late approvals, missing rows, timezone cut-offs.
Overview: This question evaluates a candidate's ability to compute time-based acceptance metrics, reason about data quality and abuse signals, and apply joins and filters in SQL/Python while accounting for nulls and incomplete user records.
Same-day acceptance rate
Return each calendar day from 2023-10-01 through 2023-10-07 together with the same-day acceptance rate (approvals that occurred on the same date as the request divided by total requests that day). Days with zero requests should show NULL for the rate.
Tables
Friendship(requester_id INTEGER, approver_id INTEGER, request_ts TIMESTAMP, approval_ts TIMESTAMP)
Users(user_id INTEGER, is_spam CHAR(1))
Hints
- Generate the 7-day series with UNION ALL of literal dates.
- Use DATE() on timestamps to compare by calendar date.
Percent requests not spam
Return the percentage of friendship requests between 2023-10-01 and 2023-10-07 (inclusive) that did NOT originate from accounts marked spam = 'T'. Treat missing Users rows (unknown spam status) as not marked spam.
Tables
Friendship(requester_id INTEGER, approver_id INTEGER, request_ts TIMESTAMP, approval_ts TIMESTAMP)
Users(user_id INTEGER, is_spam CHAR(1))
Hints
- Use LEFT JOIN so missing users count as not spam.
- Filter requests by DATE(request_ts) within 2023-10-01 to 2023-10-07.
Robustness and validation plan
Propose at least one data or query change to make the metrics in Q1–Q2 robust, and list key hypotheses and edge cases to validate when interpreting the results.
Tables
Friendship(requester_id INTEGER, approver_id INTEGER, request_ts TIMESTAMP, approval_ts TIMESTAMP)
Users(user_id INTEGER, is_spam CHAR(1))
Hints
- Think about mutable dimensions (spam flag) and timezones.
- Quantify unknowns and duplicate events to bound bias.