Calculate the 30-day retention rate for users who watched their first video. Retention means the user watched another video exactly 30 days after their first watch. Return user_id and whether they were retained (1 or 0).
This cohort retention problem is critical for ByteDance's TikTok and other video platforms. Understanding how many users return after their first session is a key metric for product-market fit and growth. This question tests your ability to work with dates, perform self-joins on temporal conditions, and calculate retention metrics—essential skills for any consumer app company.
Advanced concepts: self-join on the same table with date conditions, DATE() function for date arithmetic, MIN() to find first watch date, CASE WHEN for binary retention flag, and LEFT JOIN to preserve all users (even those not retained). The challenge is correctly identifying 'exactly 30 days later' and handling users who never returned.
ByteDance's growth teams use similar queries to: calculate D1, D7, D30 retention cohorts for product health monitoring, identify which features drive retention, segment users by retention behavior for targeted campaigns, measure the impact of onboarding changes, power executive dashboards showing user lifecycle metrics, and feed churn prediction models.
When tackling this ByteDance problem, the key is to understand the grain of the result. Are you returning one row per user, or one row per category? Always start by identifying your unique join keys and consider if filtered aggregations (CASE WHEN) are more efficient than multiple subqueries.
Be careful with NULL values in your JOIN conditions or aggregate functions. In interview scenarios, datasets often include edge cases like zero-count categories or duplicate entries that can throw off a simple COUNT(*) if not handled with DISTINCT.
Share your approach, optimized queries, or ask questions. Learning from others is the fastest way to master SQL.
WITH first_watch AS (
SELECT user_id, MIN(DATE(watch_date)) AS first_date
FROM video_watches
GROUP BY user_id
)
SELECT
f.user_id,
CASE WHEN v.watch_date IS NOT NULL THEN 1 ELSE 0 END AS retained
FROM first_watch f
LEFT JOIN video_watches v
ON f.user_id = v.user_id
AND DATE(v.watch_date) = DATE(f.first_date, '+30 days')
ORDER BY f.user_id;The CTE finds each user's earliest watch date. The LEFT JOIN then probes for any row where the same user watched a video exactly 30 days later. If a match exists, retained = 1; otherwise the LEFT JOIN produces NULL and retained = 0. DATE(f.first_date, '+30 days') is SQLite syntax — use first_date + INTERVAL '30 days' in PostgreSQL/MySQL. This is clean and explicit. The tradeoff is a potentially expensive join if video_watches is large and has no composite index on (user_id, watch_date).
WITH first_watch AS (
SELECT user_id, MIN(DATE(watch_date)) AS first_date
FROM video_watches
GROUP BY user_id
)
SELECT
f.user_id,
CASE
WHEN EXISTS (
SELECT 1 FROM video_watches v
WHERE v.user_id = f.user_id
AND DATE(v.watch_date) = DATE(f.first_date, '+30 days')
) THEN 1 ELSE 0
END AS retained
FROM first_watch f
ORDER BY f.user_id;EXISTS short-circuits as soon as one matching row is found, avoiding the join inflation that occurs when a user has multiple watches on day 30. The LEFT JOIN in Solution 1 could return multiple rows per user if they watched multiple videos on exactly day 30, requiring an additional DISTINCT. EXISTS naturally returns exactly one row per user. Prefer this approach when you only need a binary retained flag rather than accessing data from the day-30 watch event itself. It is also more semantically clear: it reads as 'does there exist a day-30 watch?'
The critical index for both solutions is a composite index on video_watches(user_id, watch_date). Without it, the inner probe in the LEFT JOIN or EXISTS subquery degrades to a full table scan per user, giving O(n × m) complexity. With the composite index, each lookup is O(log m) where m is the number of watches per user. The MIN aggregation in the CTE also benefits from this index since it can use an index scan with early termination per user_id group. At ByteDance scale (billions of watch events), the first_watch CTE should ideally be pre-materialized as a daily snapshot. Date function wrapping — DATE(v.watch_date) — can prevent index use on indexed timestamp columns; storing watch_date as a DATE type or using range predicates (watch_date >= day30 AND watch_date < day30 + 1 day) restores index sargability.
The most dangerous mistake is using INNER JOIN instead of LEFT JOIN — this silently drops all non-retained users, making the retention rate appear 100%. The query must include all users regardless of day-30 activity. Another pitfall is off-by-one: +30 days means the 31st calendar day after the first watch, not 30 days inclusive. Verify whether the product definition means exactly day 30 or within 30 days (the latter uses a range condition). Applying DATE() to a TIMESTAMP column can inhibit index use — a common performance trap. Duplicate watch events on day 1 or day 30 can cause multiple rows per user in the LEFT JOIN without a DISTINCT or EXISTS guard. Finally, users with a single total watch event are correctly marked as non-retained.
Wrap the query as a subquery or CTE and compute ROUND(AVG(retained) * 100.0, 2) AS retention_rate or equivalently ROUND(SUM(retained) * 100.0 / COUNT(*), 2). This gives the percentage of users who returned on day 30. You can also add cohort grouping by DATE_TRUNC('month', first_date) to get monthly retention cohorts.
Replace the equality check with a range: DATE(v.watch_date) > f.first_date AND DATE(v.watch_date) <= DATE(f.first_date, '+30 days'). This captures any return visit in the 30-day window. The EXISTS approach handles this more efficiently since it short-circuits on the first qualifying row, while the LEFT JOIN may match many rows and needs DISTINCT.
Use three separate EXISTS subqueries (or LEFT JOINs with aliases) in the SELECT list, each checking for a watch on day +7, +14, and +30 respectively. Alternatively, pivot using conditional aggregation with MAX(CASE WHEN days_since_first = 7 THEN 1 ELSE 0 END) across a joined date-difference calculation, producing all three retention flags in one pass.