Problem Source

This problem is from the SQL section of Niuke's Big Company Real Interview Questions, specifically scenario 02: User Growth (Baidu Information Flow), question SQL164: Calculate the next-day retention rate of new users for each day in November 2021.

Shifting from Macro-to-Micro to Micro-to-Macro

Previous chapters emphasized a top-down framework: first write the SELECT-FROM-WHERE skeleton, then break down the problem and fill in the details. This macro-to-micro approach works well for straightforward or moderately difficult queries, ensuring clarity and reducing syntax errors.

How ever, complex problems often require multiple nested subqueries, JOINs, and UNIONs. Applying the same top-down method can lead to missed conditions and logic gaps. The reason: solving hard problems is inherently micro-to-macro—we decompose the big problem into small, manageable sub-problems and then combine them. This article introduces a bottom-up approach tailored for difficult SQL challenges.

Problem Description

Table tb_user_log:

Requirement: Calculate the next-day retention rate of new users for each day in November 2021. The retention rate (uv_left_rate) should be rounded to two decimal places.

Note:

• A user is considered active on a day if either their in_time or out_time falls on that day (i.e., cross-day sessions count for both days).
• The next-day retention rate is defined as (number of new users who are active the next day) / (number of new users on the current day).
• Only users whose first activity in the system occurred in November 2021 are considered "new users." Users who had any activity before November 2021 should be excluded.
• Order results by date ascending.

Step 1: Break Down the Problem

We need to identify the core sub-problems and their dependencies:

1. Identify new users in November 2021: Users whose earliest recorded in_time falls in November 2021, and who had no earlier activity. Using WHERE on the MIN(in_time) result would not correctly exclude users with activity before November; we need HAVING after GROUP BY.

2. Identify all active days for each user: Combine in_time and out_time (converted to dates) using UNION to get a unified list of active dates per user.

3. Find next-day retention: For each new user (from step 1), check if the set of active days (from step 2) contains the day after their first activity date.

4. Calculate rate: Group by the first activity date, count distinct new users, and count distinct users who also appear in the next day's active set. Use ROUND for two decimal places and handle NULL.

Step 2: Write Sub-queries from Micro to Macro

Instead of starting with the outer SELECT, we build each logical block independent and then assemble them. Using Common Table Expressions (CTEs) with WITH ... AS makes the final query cleaner and matches the bottom-up thought process.

CTE a: New users and their first activity date

WITH a AS (
    SELECT 
        uid, 
        MIN(DATE(in_time)) AS dt
    FROM tb_user_log
    GROUP BY uid
    HAVING dt LIKE '2021-11%'
),

CTE b: All active dates per user

b AS (
    SELECT uid, DATE(in_time) AS dt
    FROM tb_user_log
    UNION
    SELECT uid, DATE(out_time) AS dt
    FROM tb_user_log
)

Final query: Join and compute retention

SELECT 
    a.dt,
    IFNULL(ROUND(COUNT(DISTINCT b.uid) / COUNT(a.uid), 2), 0) AS uv_left_rate
FROM a
LEFT JOIN b 
    ON a.uid = b.uid 
    AND b.dt = DATE_ADD(a.dt, INTERVAL 1 DAY)
GROUP BY a.dt
ORDER BY a.dt;

Step 3: Complete Code

WITH a AS (
    SELECT 
        uid, 
        MIN(DATE(in_time)) AS dt
    FROM tb_user_log
    GROUP BY uid
    HAVING dt LIKE '2021-11%'
),
b AS (
    SELECT uid, DATE(in_time) AS dt
    FROM tb_user_log
    UNION
    SELECT uid, DATE(out_time) AS dt
    FROM tb_user_log
)
SELECT 
    a.dt,
    IFNULL(ROUND(COUNT(DISTINCT b.uid) / COUNT(a.uid), 2), 0) AS uv_left_rate
FROM a
LEFT JOIN b 
    ON a.uid = b.uid 
    AND b.dt = DATE_ADD(a.dt, INTERVAL 1 DAY)
GROUP BY a.dt
ORDER BY a.dt;

Key Takeaways

• For complex problems, decompose the task into self-contained sub-queries first, then combine them. CTEs (WITH ... AS) help keep the logic modular and readable.
• Use UNION to merge multiple date columns (like in_time and out_time) when both indicate user activity.
• LEFT JOIN ensures that new users with no next-day activity are still counted in the denominator (with 0 retention).
• The IFNULL function prevents NULL values when there is no matching next-day record for a group.
• Filtering new users via HAVING on the result of MIN correctly restricts the set to those whose very first activity was in November 2021.

Common Table Expression (CTE) Notes

Purpose:

• Improves SQL readability.
• In UNION ALL or repeated references, the optimizer may materialize the CTE into a temp table, reducing execution cost.

Basic Syntax:

WITH <cte_name> AS (<subquery>)

Multiple CTEs:

WITH <cte1> AS (<subquery>),
     <cte2> AS (<subquery>)
SELECT ...

Important:

• CTEs must be immediately fololwed by the main query that references them.
• Each CTE name is temporary and scoped to the statement.
• If a CTE is referenced only once, the optimizer may not materialize it; you can use hints to force materialization if needed.