sql面试记录题

最长连续登录天数[字节]

给定A表的id,date。求每个用户的最长连续登录天数

select temp.id, max(temp.date_count)
from (select id,count(*) as date_countfrom (select id,date,date_sub(date, interval row_number() over(partition by id order by date) day) as grpfrom A) tgroup by id, grp
) temp
group by temp.id;

行转列

 行转列 = case when + group by

select id,max(case when subject = 'math' then score end) as math,  # select 里的非分组字段必须使用聚合函数。max(case when subject = 'eng' then score end)  as eng,max(case when subject = 'cs' then score end)   as cs
from A
group by id;

为什么要用max:

列转行

select id, 'math' as subject, math as score from A
union all
select id, 'eng', eng from A
union all
select id, 'cs', cs from A;

留存率

某一天新增的用户，在未来第 N 天仍然活跃的比例

N日留存率 = 第N天仍活跃的用户数 / 首日新增用户数

查看第N天是否活跃

selecta.user_id,a.first_date,b.login_date,datediff(b.login_date, a.first_date) as diff_day
from (select user_id, min(login_date) first_datefrom user_loggroup by user_id
) a
join user_log b
on a.user_id = b.user_id;

标准写法：

with new_user as (select user_id, min(login_date) first_datefrom user_loggroup by user_id
),
retention as (selectn.user_id,n.first_date,datediff(l.login_date, n.first_date) diff_dayfrom new_user njoin user_log lon n.user_id = l.user_id
)
selectfirst_date,sum(diff_day = 1) / count(distinct user_id) as day1,sum(diff_day = 7) / count(distinct user_id) as day7
from retention
group by first_date; # 这里要按照首登日分组 因为是针对每一天计算的

 2号 cohort 次日留存 = 1 / 1 = 100%

 找出公司各部门的薪资最高者

注意：有并列第一的情况

select user_id, part_id, user_name
from (select user_id, part_id, user_name, rank() over(partition by part_id order by salary desc) as salary_rankfrom A
) temp
where salary_rank = 1; # 注意最外层不需要再对部门group by 了

查询每日活跃用户数DAU

日活用户数：每个 app，每天有过行为的唯一用户数。

SELECT app_id,DATE(action_time) AS action_date,  COUNT(DISTINCT user_id) AS daily_active_users
FROM user_behavior
WHERE action_time >= CURDATE() - INTERVAL 7 DAY
GROUP BY app_id, DATE(action_time)  # 这里不能用action_date是因为执行这个group by 后才有select才有别名
ORDER BY action_date, app_id;