数据分析面经-sql相关（题目来源于牛客面经分享和个人经历）

数据表view_log(网络访问日志)，有三个字段分别是user_id(用户ID)、log_date(访问日期)、url(网址)，请找出今年7月份连续三天访问牛客网站的用户

用了窗口函数和lead函数（偏移函数，根据日期排序后再偏移可以直接得到下一天的日期），窗口函数的使用可参考牛客SQL184，只要求连续三天访问的，如果需要三天以上需要更改筛选条件

WITH daily_visits AS ( SELECT user_id, log_date, LEAD(log_date, 1) OVER (PARTITION BY user_id ORDER BY log_date) AS next_day, LEAD(log_date, 2) OVER (PARTITION BY user_id ORDER BY log_date) AS next_next_day FROM view_log WHERE url LIKE '%牛客网%' AND log_date BETWEEN '2024-07-01' AND '2024-07-31' ) SELECT DISTINCT user_id FROM daily_visits WHERE DATEDIFF(next_day, log_date) = 1 AND DATEDIFF(next_next_day, log_date) = 2;

给出id，score，如何实现按score排序且有排名，不准使用开窗函数

使用mysql特有的临时变量，：=是赋值运算符，每查询完一行后，rank排名就+1（是不是很像循环遍历）

-- 创建一个临时变量来计数排名 SET @rank = 0; -- 先按照 score 排序，然后在查询中逐行分配排名 SELECT (@rank := @rank + 1) AS rank, id, score FROM your_table ORDER BY score DESC;

示例：

一个表三列分别是：id,顾客的问题，对问题的回答。获得顾客问的最多的10个问题和每个顾客问的最多的10个问题？

顾客问的最多的10个问题（简单问题）

SELECT customer_question, COUNT(*) AS question_count

FROM customer_questions

GROUP BY customer_question

ORDER BY question_count DESC

LIMIT 10

每个顾客问的最多的10个问题（使用了 ROW_NUMBER() 窗口函数，按照顾客 id 分组，统计每个顾客问每个问题的次数，并根据次数降序排列。然后，我们选择每个顾客排名前 10 的问题。这样可以确保每个顾客都能看到他们自己问得最多的问题列表，而不是所有顾客共同的前 10 个问题。）

WITH RankedQuestions AS (

SELECT

id,

customer_question,

COUNT(*) AS question_count,

ROW_NUMBER() OVER(PARTITION BY id ORDER BY COUNT(*) DESC) AS rank

FROM customer_questions

GROUP BY id, customer_question

)

SELECT id, customer_question, question_count

FROM RankedQuestions

WHERE rank <= 10;