Coding questions · TikTok
Write SQL for last-7-day metrics without windows
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.
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):
- 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.
- 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.
- 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).
- 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.
- 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.
DAU by day for the last 7 days (no window functions)
Using ANSI SQL only (no window functions), compute daily active users (DAU) for each day from 2025-08-26 to 2025-09-01 inclusive. DAU is the count of distinct users who had a 'session' event on that date. Return exactly one row per date in the range (include dates with 0 DAU). Output columns: event_date, dau.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY, NOT NULL |
| signup_date | DATE | NOT NULL |
| channel | VARCHAR(20) | NOT NULL |
Sample data:
| channel | signup_date | user_id |
|---|---|---|
| Organic | 2025-08-29 | 1 |
| Ads | 2025-08-30 | 2 |
| Referral | 2025-08-25 | 3 |
| Ads | 2025-08-31 | 4 |
| Organic | 2025-09-01 | 5 |
Table events
| column | type | constraints |
|---|---|---|
| user_id | INT | NOT NULL |
| event_date | DATE | NOT NULL |
| event_type | VARCHAR(10) | NOT NULL |
| amount | DECIMAL(10,2) |
Sample data:
| amount | event_date | event_type | user_id |
|---|---|---|---|
| NULL | 2025-08-30 | session | 1 |
| 20 | 2025-08-31 | purchase | 1 |
| NULL | 2025-08-31 | session | 2 |
| 35 | 2025-09-01 | purchase | 2 |
| NULL | 2025-08-26 | session | 3 |
| NULL | 2025-09-01 | session | 3 |
| NULL | 2025-09-01 | session | 4 |
| NULL | 2025-09-01 | session | 5 |
Expected output
One row per calendar date from 2025-08-26 through 2025-09-01 with the distinct count of users who had a 'session' event that day (0 if none).
| dau | event_date |
|---|---|
| 1 | 2025-08-26 |
| 0 | 2025-08-27 |
| 0 | 2025-08-28 |
| 0 | 2025-08-29 |
| 1 | 2025-08-30 |
| 1 | 2025-08-31 |
| 3 | 2025-09-01 |
Hints
- Use COUNT(DISTINCT user_id) to avoid inflating counts when a user has multiple sessions in a day.
- To include dates with zero DAU, create a small date table using UNION ALL and LEFT JOIN to the aggregated sessions.
7-day conversion rate by signup channel (no window functions)
Using ANSI SQL only (no window functions), for users who signed up between 2025-08-26 and 2025-09-01 inclusive, compute conversion rate by channel.
A user is a converter if they have at least one 'purchase' event with event_date between their signup_date and (signup_date + 7 days), inclusive. Do not double-count users with multiple purchases.
Return columns: channel, signups, converters, conversion_rate (converters / signups).
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY, NOT NULL |
| signup_date | DATE | NOT NULL |
| channel | VARCHAR(20) | NOT NULL |
Sample data:
| channel | signup_date | user_id |
|---|---|---|
| Organic | 2025-08-29 | 1 |
| Ads | 2025-08-30 | 2 |
| Referral | 2025-08-25 | 3 |
| Ads | 2025-08-31 | 4 |
| Organic | 2025-09-01 | 5 |
Table events
| column | type | constraints |
|---|---|---|
| user_id | INT | NOT NULL |
| event_date | DATE | NOT NULL |
| event_type | VARCHAR(10) | NOT NULL |
| amount | DECIMAL(10,2) |
Sample data:
| amount | event_date | event_type | user_id |
|---|---|---|---|
| NULL | 2025-08-30 | session | 1 |
| 20 | 2025-08-31 | purchase | 1 |
| NULL | 2025-08-31 | session | 2 |
| 35 | 2025-09-01 | purchase | 2 |
| NULL | 2025-08-26 | session | 3 |
| NULL | 2025-09-01 | session | 3 |
| NULL | 2025-09-01 | session | 4 |
| NULL | 2025-09-01 | session | 5 |
Expected output
One row per channel among users who signed up in 2025-08-26..2025-09-01. 'converters' counts distinct users with at least one qualifying purchase within 7 days after signup. conversion_rate is converters/signups.
| channel | conversion_rate | converters | signups |
|---|---|---|---|
| Ads | 0.5 | 1 | 2 |
| Organic | 0.5 | 1 | 2 |
Hints
- Build a set of recent signups first, then identify converter users with SELECT DISTINCT.
- LEFT JOIN converters back to signups so channels with zero converters are still returned.
Day-1 retention by cohort day using self-joins (no window functions)
Using ANSI SQL only (no window functions), compute day-1 retention for each cohort date D from 2025-08-26 through 2025-08-31 inclusive.
Define:
- cohort_users = distinct users who had a 'session' on day D
- retained_users = among those cohort_users, distinct users who also had a 'session' on day D+1
- retention_rate = retained_users / cohort_users (return NULL when cohort_users = 0)
Return one row per cohort date (include dates with zero cohort users). Output: cohort_date, retained_users, cohort_users, retention_rate.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY, NOT NULL |
| signup_date | DATE | NOT NULL |
| channel | VARCHAR(20) | NOT NULL |
Sample data:
| channel | signup_date | user_id |
|---|---|---|
| Organic | 2025-08-29 | 1 |
| Ads | 2025-08-30 | 2 |
| Referral | 2025-08-25 | 3 |
| Ads | 2025-08-31 | 4 |
| Organic | 2025-09-01 | 5 |
Table events
| column | type | constraints |
|---|---|---|
| user_id | INT | NOT NULL |
| event_date | DATE | NOT NULL |
| event_type | VARCHAR(10) | NOT NULL |
| amount | DECIMAL(10,2) |
Sample data:
| amount | event_date | event_type | user_id |
|---|---|---|---|
| NULL | 2025-08-30 | session | 1 |
| 20 | 2025-08-31 | purchase | 1 |
| NULL | 2025-08-31 | session | 2 |
| 35 | 2025-09-01 | purchase | 2 |
| NULL | 2025-08-26 | session | 3 |
| NULL | 2025-09-01 | session | 3 |
| NULL | 2025-09-01 | session | 4 |
| NULL | 2025-09-01 | session | 5 |
Expected output
One row for each cohort_date in 2025-08-26..2025-08-31 showing cohort size (distinct session users on D) and retained users with a session on D+1.
| cohort_date | cohort_users | retained_users | retention_rate |
|---|---|---|---|
| 2025-08-26 | 1 | 0 | 0 |
| 2025-08-27 | 0 | 0 | NULL |
| 2025-08-28 | 0 | 0 | NULL |
| 2025-08-29 | 0 | 0 | NULL |
| 2025-08-30 | 1 | 0 | 0 |
| 2025-08-31 | 1 | 0 | 0 |
Hints
- Treat sessions on day D as the cohort definition, then self-join sessions to day D+1.
- Use DISTINCT on (cohort_date, user_id) so multiple sessions don’t inflate counts.
First purchase date and lifetime revenue per user (no window functions)
Using ANSI SQL only (no window functions), return for every user their first purchase date (NULL if they never purchased) and lifetime revenue (sum of purchase amounts; treat NULL amounts as 0; return 0 when no purchases).
Output columns: user_id, first_purchase_date, lifetime_revenue.
Restrictions: use only aggregates and GROUP BY (e.g., MIN with CASE), no window functions.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY, NOT NULL |
| signup_date | DATE | NOT NULL |
| channel | VARCHAR(20) | NOT NULL |
Sample data:
| channel | signup_date | user_id |
|---|---|---|
| Organic | 2025-08-29 | 1 |
| Ads | 2025-08-30 | 2 |
| Referral | 2025-08-25 | 3 |
| Ads | 2025-08-31 | 4 |
| Organic | 2025-09-01 | 5 |
Table events
| column | type | constraints |
|---|---|---|
| user_id | INT | NOT NULL |
| event_date | DATE | NOT NULL |
| event_type | VARCHAR(10) | NOT NULL |
| amount | DECIMAL(10,2) |
Sample data:
| amount | event_date | event_type | user_id |
|---|---|---|---|
| NULL | 2025-08-30 | session | 1 |
| 20 | 2025-08-31 | purchase | 1 |
| NULL | 2025-08-31 | session | 2 |
| 35 | 2025-09-01 | purchase | 2 |
| NULL | 2025-08-26 | session | 3 |
| NULL | 2025-09-01 | session | 3 |
| NULL | 2025-09-01 | session | 4 |
| NULL | 2025-09-01 | session | 5 |
Expected output
One row per user with first purchase date (NULL if none) and total revenue across all purchase events (0 if none).
| first_purchase_date | lifetime_revenue | user_id |
|---|---|---|
| 2025-08-31 | 20 | 1 |
| 2025-09-01 | 35 | 2 |
| NULL | 0 | 3 |
| NULL | 0 | 4 |
| NULL | 0 | 5 |
Hints
- Use MIN(CASE WHEN ...) to get the first purchase date without window functions.
- LEFT JOIN from users ensures users with no events still appear.
Top revenue channel in a date range with tie-breaker (no window functions)
Using ANSI SQL only (no window functions), identify the single top channel by total purchase revenue for purchases with event_date between 2025-08-26 and 2025-09-01 inclusive.
Revenue is the sum of purchase amounts (treat NULL amounts as 0). Attribute each purchase to the channel of the purchasing user (from users.channel).
If multiple channels tie for highest total revenue, return the lexicographically smallest channel name.
Return exactly one row with: channel, total_revenue.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY, NOT NULL |
| signup_date | DATE | NOT NULL |
| channel | VARCHAR(20) | NOT NULL |
Sample data:
| channel | signup_date | user_id |
|---|---|---|
| Organic | 2025-08-29 | 1 |
| Ads | 2025-08-30 | 2 |
| Referral | 2025-08-25 | 3 |
| Ads | 2025-08-31 | 4 |
| Organic | 2025-09-01 | 5 |
Table events
| column | type | constraints |
|---|---|---|
| user_id | INT | NOT NULL |
| event_date | DATE | NOT NULL |
| event_type | VARCHAR(10) | NOT NULL |
| amount | DECIMAL(10,2) |
Sample data:
| amount | event_date | event_type | user_id |
|---|---|---|---|
| NULL | 2025-08-30 | session | 1 |
| 20 | 2025-08-31 | purchase | 1 |
| NULL | 2025-08-31 | session | 2 |
| 35 | 2025-09-01 | purchase | 2 |
| NULL | 2025-08-26 | session | 3 |
| NULL | 2025-09-01 | session | 3 |
| NULL | 2025-09-01 | session | 4 |
| NULL | 2025-09-01 | session | 5 |
Expected output
A single row giving the channel with the highest total purchase revenue in the date range; ties are broken by lexicographically smallest channel name.
| channel | total_revenue |
|---|---|
| Ads | 35 |
Hints
- Join purchases to users to attribute revenue to a channel.
- Use ORDER BY total_revenue DESC, channel ASC and FETCH FIRST 1 ROW ONLY to implement the tie-break rule.