Coding questions · Snapchat
Monitor Friend-Request System for Quality and Abuse
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.
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.
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.
Table Friendship
| column | type | constraints |
|---|---|---|
| requester_id | INTEGER | NOT NULL |
| approver_id | INTEGER | NOT NULL |
| request_ts | TIMESTAMP | NOT NULL |
| approval_ts | TIMESTAMP |
Sample data:
| approval_ts | approver_id | request_ts | requester_id |
|---|---|---|---|
| 2023-10-01 12:00:00 | 2 | 2023-10-01 10:00:00 | 1 |
| NULL | 4 | 2023-10-01 11:00:00 | 3 |
| 2023-10-02 09:05:00 | 6 | 2023-10-02 09:00:00 | 5 |
| 2023-10-05 10:00:00 | 8 | 2023-10-02 14:00:00 | 7 |
Table Users
| column | type | constraints |
|---|---|---|
| user_id | INTEGER | PRIMARY KEY, NOT NULL |
| is_spam | CHAR(1) | NOT NULL |
Sample data:
| is_spam | user_id |
|---|---|
| F | 1 |
| F | 2 |
| T | 3 |
| F | 4 |
| F | 5 |
Expected output
For each day in 2023-10-01 through 2023-10-07, show the rate of requests approved on the same calendar date as the request; NULL when no requests occurred that day.
| day | same_day_acceptance_rate |
|---|---|
| 2023-10-01 | 0.5 |
| 2023-10-02 | 0.5 |
| 2023-10-03 | NULL |
| 2023-10-04 | NULL |
| 2023-10-05 | NULL |
| 2023-10-06 | NULL |
| 2023-10-07 | NULL |
Hints
- Generate the 7-day series with UNION ALL of literal dates.
- Use DATE() on timestamps to compare by calendar date.
- Guard against division by zero with NULLIF to return NULL on empty days.
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.
Table Friendship
| column | type | constraints |
|---|---|---|
| requester_id | INTEGER | NOT NULL |
| approver_id | INTEGER | NOT NULL |
| request_ts | TIMESTAMP | NOT NULL |
| approval_ts | TIMESTAMP |
Sample data:
| approval_ts | approver_id | request_ts | requester_id |
|---|---|---|---|
| 2023-10-01 12:00:00 | 2 | 2023-10-01 10:00:00 | 1 |
| NULL | 4 | 2023-10-01 11:00:00 | 3 |
| 2023-10-02 09:05:00 | 6 | 2023-10-02 09:00:00 | 5 |
| 2023-10-05 10:00:00 | 8 | 2023-10-02 14:00:00 | 7 |
Table Users
| column | type | constraints |
|---|---|---|
| user_id | INTEGER | PRIMARY KEY, NOT NULL |
| is_spam | CHAR(1) | NOT NULL |
Sample data:
| is_spam | user_id |
|---|---|
| F | 1 |
| F | 2 |
| T | 3 |
| F | 4 |
| F | 5 |
Expected output
Share of requests in the fixed week whose requester is not marked spam (NULL/missing Users treated as not spam).
| percent_requests_not_spam_marked |
|---|
| 75 |
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.
- Multiply by 100.0 and round to 2 decimals.
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.
Table Friendship
| column | type | constraints |
|---|---|---|
| requester_id | INTEGER | NOT NULL |
| approver_id | INTEGER | NOT NULL |
| request_ts | TIMESTAMP | NOT NULL |
| approval_ts | TIMESTAMP |
Sample data:
| approval_ts | approver_id | request_ts | requester_id |
|---|---|---|---|
| 2023-10-01 12:00:00 | 2 | 2023-10-01 10:00:00 | 1 |
| NULL | 4 | 2023-10-01 11:00:00 | 3 |
| 2023-10-02 09:05:00 | 6 | 2023-10-02 09:00:00 | 5 |
| 2023-10-05 10:00:00 | 8 | 2023-10-02 14:00:00 | 7 |
Table Users
| column | type | constraints |
|---|---|---|
| user_id | INTEGER | PRIMARY KEY, NOT NULL |
| is_spam | CHAR(1) | NOT NULL |
Sample data:
| is_spam | user_id |
|---|---|
| F | 1 |
| F | 2 |
| T | 3 |
| F | 4 |
| F | 5 |
Expected output
A concise list of concrete data-model or query changes and the main validation checks to interpret Q1–Q2 reliably.
| category | suggestion |
|---|---|
| Data improvement | Snapshot requester spam flag at request time in Friendship (e.g., add requester_is_spam BOOLEAN DEFAULT 0). |
| Query refinement | Use a fixed, explicit reporting timezone and convert timestamps before DATE() truncation. |
| Validation | Quantify share of requests with unknown spam status to assess Q2 bias. |
| Data hygiene | Deduplicate requests by a unique request_id or earliest request per (requester_id, approver_id, request_date). |
Hints
- Think about mutable dimensions (spam flag) and timezones.
- Quantify unknowns and duplicate events to bound bias.