Quick Overview

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.

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.

Overview: 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.

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.

Tables

users(user_id INT, signup_date DATE, channel VARCHAR(20))

events(user_id INT, event_date DATE, event_type VARCHAR(10), amount DECIMAL(10,2))

Hints

  1. Use COUNT(DISTINCT user_id) to avoid inflating counts when a user has multiple sessions in a day.
  2. 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).

Tables

users(user_id INT, signup_date DATE, channel VARCHAR(20))

events(user_id INT, event_date DATE, event_type VARCHAR(10), amount DECIMAL(10,2))

Hints

  1. Build a set of recent signups first, then identify converter users with SELECT DISTINCT.
  2. 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.

Tables

users(user_id INT, signup_date DATE, channel VARCHAR(20))

events(user_id INT, event_date DATE, event_type VARCHAR(10), amount DECIMAL(10,2))

Hints

  1. Treat sessions on day D as the cohort definition, then self-join sessions to day D+1.
  2. 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.

Tables

users(user_id INT, signup_date DATE, channel VARCHAR(20))

events(user_id INT, event_date DATE, event_type VARCHAR(10), amount DECIMAL(10,2))

Hints

  1. Use MIN(CASE WHEN ...) to get the first purchase date without window functions.
  2. 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.

Tables

users(user_id INT, signup_date DATE, channel VARCHAR(20))

events(user_id INT, event_date DATE, event_type VARCHAR(10), amount DECIMAL(10,2))

Hints

  1. Join purchases to users to attribute revenue to a channel.
  2. Use ORDER BY total_revenue DESC, channel ASC and FETCH FIRST 1 ROW ONLY to implement the tie-break rule.

Loading coding console...