Core SQL patterns that show up often
A strong SQL interview usually does not stop at basic SELECT and GROUP BY. The common questions tend to revolve around user behavior, time-based aggregation, ranking, retention, and relationship networks. Several classic exercises are worth practicing until the logic becomes natural:
- Users who logged in for at least two consecutive days
- The maximum consecutive login streak for each user
- User pairs with at least three mutual friends
- Each user’s cumulative login duration over the most recent 7 days
- Next-day or 7-day retention rate
- Top 3 orders by amount within each category
- Pivot-style row/column transformation for multidimensional behavior statistics
The following examples use MySQL syntax and focus on the reasoning behind each query, especially window functions, self joins, date calculations, and grouped aggregation.
1. Users who logged in for at least two consecutive days
Table: user_login
| user_id | login_date |
|---------|------------|
| 1 | 2025-01-01 |
| 1 | 2025-01-02 |
| 2 | 2025-01-01 |
| 3 | 2025-01-01 |
| 3 | 2025-01-02 |
| 3 | 2025-01-03 |
The key trick is to identify consecutive dates as belonging to the same group. First, assign a row number to each user’s login dates in ascending order. Then subtract that row number from the login date. For consecutive dates, the calculated value stays the same, so it can be used as a grouping key.
After that, group by user_id and the calculated difference, count the number of rows in each group, and keep only groups with at least two records.
WITH login_rn AS (
SELECT
user_id,
login_date,
-- 关键：连续日期的差值固定，形成分组标识
DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY login_date) DAY) AS diff
FROM user_login
GROUP BY user_id, login_date -- 去重：同一用户同一天多次登陆算一次有效登陆
),
continuous_login AS (
SELECT
user_id,
COUNT(*) AS continuous_days
FROM login_rn
GROUP BY user_id, diff
HAVING COUNT(*) >= 2 -- 筛选连续至少2天的记录
)
SELECT DISTINCT user_id -- 去重得到目标用户
FROM continuous_login;
What this tests: ROW_NUMBER(), date arithmetic, deduplication by user and date, and grouping consecutive values.
2. Maximum consecutive login days
This is a direct extension of the previous problem. Once consecutive login blocks are identified, calculate the length of each block, then take the maximum value for every user.
WITH login_rn AS (
SELECT
user_id,
login_date,
DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY login_date) DAY) AS diff
FROM user_login
GROUP BY user_id, login_date
),
continuous_stats AS (
SELECT
user_id,
diff,
COUNT(*) AS continuous_days
FROM login_rn
GROUP BY user_id, diff
)
SELECT
user_id,
MAX(continuous_days) AS max_continuous_days -- 取每个用户的最大连续天数
FROM continuous_stats
GROUP BY user_id;
If the question asks for the longest login streak across the entire platform rather than per user, the final query can be changed to:
SELECT MAX(continuous_days) FROM continuous_stats;
3. User pairs with at least three mutual friends
Table: friend_relation
| user_id | friend_id |
|---------|-----------|
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 1 | 5 |
| 2 | 1 |
| 2 | 3 |
| 2 | 4 |
| 3 | 1 |
| 3 | 2 |
The idea is to self join the friend relationship table. If two users have the same friend_id, that friend is common to both users. To avoid duplicate user pairs such as (1,2) and (2,1), add the condition a.user_id < b.user_id.
Then group by the user pair and count the number of common friends.
WITH friend_pair AS (
-- 自连接找共同好友，限定user1 < user2去重
SELECT
a.user_id AS user1,
b.user_id AS user2,
a.friend_id AS common_friend
FROM friend_relation a
JOIN friend_relation b
ON a.friend_id = b.friend_id -- 好友ID匹配，即共同好友
AND a.user_id < b.user_id -- 核心去重逻辑
)
SELECT
user1,
user2,
COUNT(common_friend) AS common_friend_count
FROM friend_pair
GROUP BY user1, user2
HAVING COUNT(common_friend) >= 3;
What this tests: self joins, pair deduplication, grouping, and aggregate filtering with HAVING.
4. Each user’s cumulative login duration over the last 7 days
Table: user_login_duration
| user_id | login_time | duration_min |
|---------|---------------------|--------------|
| 1 | 2025-01-01 08:00:00 | 30 |
| 1 | 2025-01-05 10:00:00 | 60 |
| 2 | 2025-01-06 14:00:00 | 45 |
This is a typical sliding-window aggregation. Use SUM() OVER(), partition by user, sort by login time, and restrict the window to the current row and the preceding 7 days.
SELECT
user_id,
login_time,
SUM(duration_min) OVER(
PARTITION BY user_id
ORDER BY login_time
RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW -- 近7天滑动窗口
) AS last7d_total_duration
FROM user_login_duration;
What this tests: time-based window frames, cumulative aggregation, and user-level behavior statistics.
5. Next-day retention rate
Next-day retention is defined as:
users who logged in on the first day and also logged in the next day / total users who logged in on the first day
Using user_login again, assume 2025-01-01 is the first day to analyze.
The calculation can be broken into two sets:
- Users who logged in on
2025-01-01 - Among those users, the ones who also logged in on
2025-01-02
Then divide the size of the second set by the size of the first set.
WITH first_login AS (
-- 首日（2025-01-01）登陆的用户
SELECT DISTINCT user_id
FROM user_login
WHERE login_date = '2025-01-01'
),
second_login AS (
-- 首日登陆且次日也登陆的用户
SELECT DISTINCT fl.user_id
FROM first_login fl
JOIN user_login ul
ON fl.user_id = ul.user_id
AND ul.login_date = '2025-01-02'
)
SELECT
-- 左连接确保首日所有用户都被统计，避免分母为0
COUNT(DISTINCT sl.user_id) / COUNT(DISTINCT fl.user_id) AS next_day_retention
FROM first_login fl
LEFT JOIN second_login sl ON fl.user_id = sl.user_id;
For 7-day retention, the logic is the same: replace the next-day date with the first day plus 7 days, such as 2025-01-08.
6. Top 3 orders by amount in each category
Table: order_info
| order_id | category | amount |
|----------|----------|--------|
| 1 | A | 100 |
| 2 | A | 200 |
| 3 | A | 150 |
| 4 | A | 180 |
| 5 | B | 300 |
This is the standard TopN pattern. Rank records inside each category by amount in descending order, then keep the rows whose rank is no greater than 3.
DENSE_RANK() keeps tied values at the same rank. ROW_NUMBER() gives every row a unique ranking and will break ties. The right choice depends on the interview question’s requirement.
WITH order_rank AS (
SELECT
*,
-- DENSE_RANK()：并列排名；ROW_NUMBER()：唯一排名，根据需求选择
DENSE_RANK() OVER(PARTITION BY category ORDER BY amount DESC) AS rk
FROM order_info
)
SELECT *
FROM order_rank
WHERE rk <= 3;
What this tests: ranking window functions and the general solution for TopN queries.
What to focus on when preparing
Several SQL ideas appear repeatedly in these types of questions:
- Window functions:
ROW_NUMBER,RANK,DENSE_RANK,SUM OVER, and their variations. - Consecutive-value detection: date arithmetic plus grouped aggregation is a common interview pattern.
- Joins: especially self joins and left joins, as seen in mutual-friend analysis and retention calculation.
- Sliding-window statistics: cumulative or average metrics over the last N days or N hours.
- Business scenarios: TopN, row-to-column transformations, retention, active users, conversion, and user behavior analysis.
The most useful preparation is not memorizing SQL snippets, but understanding why each query works. Before writing code, clarify the business definition, identify the grouping key, decide whether deduplication is needed, and watch for boundary cases such as repeated logins on the same day or a denominator of zero in retention calculations.