面了BAT、TMD等大厂，总结出必问的5个sql

一、每门课程问题

用一条 SQL 语句查询 学生表每门课都大于 80 分的学生姓名。

解决办法一： having

思路：如果最小的课程都大于80分，那他所有的课程分数，肯定都大于80分！

代码实现

SELECT name
FROM xuesheng
GROUP BY name
HAVING MIN(score)> 80

解决办法二：not in

可以用反向思维，先查询出表里面有小于 80 分的 name,然后用 not in 去除掉

代码实现

SELECT DISTINCT name
FROM xuesheng
WHERE name NOT IN
    (SELECT DISTINCT name
    FROM xuesheng
    WHERE score <=80);

二、topN 问题

案例：查询各科成绩前两名的记录

代码实现

row_number() over( partition by 课程 order by 成绩 desc) as rank
...
where rank <= 2  -- 前两名

三、连续问题（7 天连续登陆）

实现思路:

1. 因为每天用户登录次数可能不止一次，所以需要先将用户每天的登录日期去重。

2. 再用row_number() over(partition by _ order by _)函数将用户id分组，按照登陆时间进行排序。

3. 计算登录日期减去第二步骤得到的结果值，用户连续登陆情况下，每次相减的结果都相同。

4. 按照id和日期分组并求和，筛选大于等于7的即为连续7天登陆的用户。

代码实现

SELECT user_id, MAX(count_val) AS max_count  -- 查出了最大连续登陆，where>=7,即7天连续
FROM (
     -- group by相同日期
  SELECT user_id, symbol_date, COUNT(*) AS count_val
  FROM (
       -- 日期减rank，连续登陆的话，会得到相同日期
    SELECT user_id, log_date, date_sub(log_date, CAST(rn AS INT)) AS symbol_date
    FROM (
         --  打上rank标识
      SELECT user_id, log_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY log_date) AS rn
      FROM user_logging_format
    ) c
  ) d
  GROUP BY user_id, symbol_date
) e
GROUP BY user_id;

四、行转列问题

行转列可谓是经典中的经典了，必须掌握了！

6行变2行，2列变3列

代码实现

SELECT SID,
MAX(case CID when '01' then score else 0 end) '01',
MAX(case CID when '02' then score else 0 end)'02',
MAX(case CID when '03' then score else 0 end)'03'
FROM SC
GROUP BY SID

五、留存问题

留存率是衡量用户质量的最重要指标之一，因此计算用户留存率是用户数据分析中必须掌握的技能之一。同样也成为了面试经典sql之一。

留存率指标中，通常需要关注次日留存、3日留存、7日留存和月留存。对新增用户而言，需要关注更细颗粒度的数据，也就是7日内每天的留存率。

代码实现

select
dd
, count( if(id=lead_id and datediff(dd,lead_dd)=1 ,id, null ) ) as '1 日留存'
, count( if(id=lead_id7 and datediff(dd,lead_dd7)=7 ,id, null ) ) as '7 日留存'
from
(
select
id, dd
, lead(dd,1) over(partition by id order by dd asc ) as lead_dd
, lead(id,1) over(partition by id order by dd asc ) as lead_id
, lead(dd,7) over(partition by id order by dd asc ) as lead_dd7
, lead(id,7) over(partition by id order by dd asc ) as lead_id7
from  
 (
select 'slm' as id, '2018-12-26' as dd
union all select 'slm' as id, '2018-12-27' as dd
union all select 'slm' as id, '2018-12-28' as dd
union all select 'hh ' as id, '2018-12-26' as dd
union all select 'hh ' as id, '2018-12-28' as dd ) aa
) bb

 更多面试相关问题，欢迎私聊讨论。