Write SQL for last-7-day metrics without windows
Company: TikTok
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Technical Screen
Assume today is 2025-09-01. Use ANSI SQL only and do not use window functions. You may use subqueries, GROUP BY, HAVING, and JOINs. Schema and small sample data follow.
Schema:
- users(user_id INT PRIMARY KEY, signup_date DATE, channel VARCHAR)
- events(user_id INT, event_date DATE, event_type VARCHAR, amount DECIMAL(10,2)) -- event_type in ('session','purchase'); amount is NULL unless event_type='purchase'
Sample data:
users
user_id | signup_date | channel
1 | 2025-08-29 | Organic
2 | 2025-08-30 | Ads
3 | 2025-08-25 | Referral
4 | 2025-08-31 | Ads
5 | 2025-09-01 | Organic
events
user_id | event_date | event_type | amount
1 | 2025-08-30 | session | NULL
1 | 2025-08-31 | purchase | 20.00
2 | 2025-08-31 | session | NULL
2 | 2025-09-01 | purchase | 35.00
3 | 2025-08-26 | session | NULL
3 | 2025-09-01 | session | NULL
4 | 2025-09-01 | session | NULL
5 | 2025-09-01 | session | NULL
Tasks (answer each with a single SQL query, no window functions):
1) Compute DAU for each day in the last 7 days (2025-08-26 to 2025-09-01, inclusive) counting distinct users with a 'session' event. Return columns: event_date, dau.
2) For users who signed up in the last 7 days (2025-08-26 to 2025-09-01), compute the conversion rate by channel = users with ≥1 'purchase' within 7 days of their own signup divided by total signups in that channel. Return: channel, signups, converters, conversion_rate. Ensure you do not double-count users with multiple purchases.
3) Compute day-1 retention for each cohort day D in 2025-08-26..2025-08-31: among users who had a 'session' on day D, what fraction also had a 'session' on day D+1. Return: cohort_date, retained_users, cohort_users, retention_rate. Do this with self-joins or subqueries (no windows).
4) For all users, return their first purchase date (or NULL if none) and lifetime revenue. Return: user_id, first_purchase_date, lifetime_revenue. Use only aggregates and GROUP BY (e.g., MIN with CASE), no windows.
5) Identify, for 2025-08-26..2025-09-01, the top channel by total revenue from purchases made by users in that channel, breaking ties by lexicographically smallest channel name. Return a single row with: channel, total_revenue.
Edge cases to handle: multiple sessions per day per user (should not inflate counts), users without purchases, NULL amounts, and users signing up before the 7-day window but active within it.
Quick Answer: This question evaluates proficiency in ANSI SQL data manipulation, including aggregation, de-duplication, date arithmetic, joins and subqueries, GROUP BY/HAVING usage, and cohort/retention and conversion calculations performed without window functions.