MySQL Interview Scenario: Analyzing E‑Commerce User Repurchase Behavior

Scenario: an e‑commerce platform has an orders table and a users table. The goal is to analyze user repurchase behavior for the year 2023.

Table structures

users : user_id (PK), register_date, city orders : order_id (PK), user_id (FK), order_date,

amount

Analysis requirements

Define a repurchase user as someone with at least two purchases between 2023‑01‑01 and 2023‑12‑31.

Calculate the average interval (in days) between consecutive purchases for each repurchase user.

Calculate the average amount per repurchase.

Group by city to obtain the number of repurchase users, their proportion, average interval and average amount.

Identify high‑value repurchase users whose cumulative purchase amount exceeds 10,000 CNY and compute the same metrics for them.

Reference SQL

WITH user_orders AS (
    -- Get 2023 orders and compute next purchase date per user
    SELECT
        o.user_id,
        o.order_date,
        o.amount,
        u.city,
        LEAD(o.order_date) OVER (PARTITION BY o.user_id ORDER BY o.order_date) AS next_order_date
    FROM orders o
    JOIN users u ON o.user_id = u.user_id
    WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
),
repurchase_users AS (
    -- Filter users with a next purchase and compute interval
    SELECT
        user_id,
        city,
        order_date,
        next_order_date,
        amount,
        DATEDIFF(next_order_date, order_date) AS repurchase_interval
    FROM user_orders
    WHERE next_order_date IS NOT NULL
),
repurchase_stats AS (
    -- Aggregate per user and city
    SELECT
        user_id,
        city,
        COUNT(*) AS repurchase_count,
        AVG(repurchase_interval) AS avg_repurchase_interval,
        SUM(amount) AS total_amount,
        AVG(amount) AS avg_repurchase_amount
    FROM repurchase_users
    GROUP BY user_id, city
),
high_value_repurchase_users AS (
    -- Select users with total_amount > 10000
    SELECT
        user_id,
        city,
        repurchase_count,
        avg_repurchase_interval,
        total_amount,
        avg_repurchase_amount
    FROM repurchase_stats
    WHERE total_amount > 10000
),
city_level_stats AS (
    -- City‑level aggregation
    SELECT
        city,
        COUNT(DISTINCT user_id) AS repurchase_user_count,
        COUNT(DISTINCT user_id) * 1.0 / (SELECT COUNT(DISTINCT user_id) FROM users) AS repurchase_user_ratio,
        AVG(avg_repurchase_interval) AS city_avg_repurchase_interval,
        AVG(avg_repurchase_amount) AS city_avg_repurchase_amount
    FROM repurchase_stats
    GROUP BY city
)
SELECT
    hvr.user_id,
    hvr.city,
    hvr.repurchase_count,
    hvr.avg_repurchase_interval,
    hvr.total_amount,
    hvr.avg_repurchase_amount,
    cls.repurchase_user_count,
    cls.repurchase_user_ratio,
    cls.city_avg_repurchase_interval,
    cls.city_avg_repurchase_amount
FROM high_value_repurchase_users hvr
JOIN city_level_stats cls ON hvr.city = cls.city;

Query logic breakdown

user_orders : extracts 2023 orders, joins with users, and uses LEAD to obtain each user’s next purchase date.

repurchase_users : removes the final purchase of each user (where no next order exists) and calculates the day difference between consecutive orders.

repurchase_stats : groups by user_id and city to compute purchase count, average interval, total and average amount.

high_value_repurchase_users : filters the previous result for users whose cumulative amount exceeds 10 000 CNY.

city_level_stats : aggregates the repurchase metrics at the city level, also computing the proportion of repurchase users among all users.

The final SELECT joins the high‑value user data with the city‑level statistics to produce a comprehensive result set.

Key assessment points

Use of the LEAD window function to compute the next purchase date.

Group‑by aggregation on both user and city dimensions.

CTE ( WITH) structure for step‑by‑step data processing, improving readability and potentially performance.

Example output (illustrative)

Rows include columns such as user_id, city, repurchase_count, avg_repurchase_interval, total_amount, avg_repurchase_amount, repurchase_user_count, repurchase_user_ratio, city_avg_repurchase_interval, and city_avg_repurchase_amount. Sample data shows a Beijing user with three repurchases, a 45.67‑day average interval, total amount 15 000 CNY, and city‑level metrics such as a 0.15 repurchase‑user ratio.