MySQL Interview Scenario: Analyzing High-Value E‑Commerce Users

In an e‑commerce context the article defines two tables, users (user_id, register_date, city) and orders (order_id, user_id, order_date, amount), and poses a requirement to identify users whose total purchase amount in 2023 exceeds 10,000 CNY, compute their average spend and order count, determine their proportion among all users, and break the results down by city.

The solution uses three Common Table Expressions (CTEs):

high_value_users : joins users and orders, filters orders to 2023, groups by user_id and city, and keeps only rows where SUM(amount) > 10000, producing total_amount and total_orders for each high‑value user.

total_users : counts distinct user_id in users to obtain the overall user count.

city_stats : aggregates high_value_users by city, calculating the number of high‑value users per city and their ratio to the total user base using the value from total_users.

The final SELECT joins high_value_users with city_stats, adds window functions AVG(total_amount) OVER () and AVG(total_orders) OVER () to compute the overall average amount and average order count, and returns each high‑value user together with city‑level statistics.

WITH high_value_users AS (
    SELECT u.user_id,
           u.city,
           SUM(o.amount) AS total_amount,
           COUNT(o.order_id) AS total_orders
    FROM users u
    JOIN orders o ON u.user_id = o.user_id
    WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
    GROUP BY u.user_id, u.city
    HAVING SUM(o.amount) > 10000
),
total_users AS (
    SELECT COUNT(DISTINCT user_id) AS total_user_count
    FROM users
),
city_stats AS (
    SELECT city,
           COUNT(user_id) AS high_value_user_count,
           COUNT(user_id) * 1.0 / (SELECT total_user_count FROM total_users) AS high_value_user_ratio
    FROM high_value_users
    GROUP BY city
)
SELECT hvu.user_id,
       hvu.city,
       hvu.total_amount,
       hvu.total_orders,
       AVG(hvu.total_amount) OVER () AS avg_amount,
       AVG(hvu.total_orders) OVER () AS avg_orders,
       cs.high_value_user_count,
       cs.high_value_user_ratio
FROM high_value_users hvu
JOIN city_stats cs ON hvu.city = cs.city;

Thus the article demonstrates a step‑by‑step SQL approach—table definition, requirement breakdown, CTE construction, and final query—to perform high‑value user behavior analysis in MySQL.