with a as( select * ,case when course like '%1%' then 1 else 0 end as course1 ,case when course like '%2%' then 1 else 0 end as course2 ,case when course like '%3%' then 1 else 0 end as course3 from cultivate_tb ) select (sum(course1)+sum(course2)+sum(course3)) as staff_nums from a

##where group by tag,max() select b.tag ,sum(a.if_retweet) as retweet_cut ,round(sum(a.if_retweet)/count(a.id),3) as retweet_rate from tb_user_video_log a left join tb_video_info b on a.video_id=b.video_id where datediff((select max(date(start_time)) from tb_user_video_log),date(a.start_time))&l...

##left join duration ##avg with a as(select b.tag ,round(avg(if(timestampdiff(second,a.start_time,a.end_time)>=b.duration,1,timestampdiff(second,a.start_time,a.end_time)/b.duration))*100,2) as avg_play_progress from tb_user_video_log a left join tb_video_info b on a.video_id=b.video_id group by b...

with a as( select user_id ,min(date) as date from login group by user_id ) select l.date ,count(distinct case when l.date =a.date then l.user_id else NULL end) as new from login l left join a on l.user_id=a.user_id group by l.date order by l.date 一开始是用where在login中筛选出所有user_id和min（date）的数据，然后...

with a as( select user_id ,min(date) as reg_date from login group by user_id ) select round(count(distinct case when datediff(b.date,a.reg_date)=1 then b.user_id else NULL end)/count(distinct a.user_id),3) as p from a left join login b on a.user_id=b.user_id 1、注意输出形式，保留3位小数是round，如...

##有月份 ##group by 每个月每首歌的pv ##分组每个月，按照pv排序 ##筛选rk《=3的 ##pv相同按照id决，先月份，再排名再id with a as( select p.user_id ,month(p.fdate) as month ,p.song_id ,s.song_name from play_log p left join song_info s on p.song_id=s.song_id left join user_info u on p.user_id=u.user_id where year(p....

面试的时候手撕sql然而用自连接好像不可以呜呜呜。。

with a as(select fdate ,user_id ,fdate-rank()over(partition by user_id order by fdate) as ffdate from tb_dau where fdate between '2023-01-01' and '2023-01-31') ,b as( select user_id ,count(1) as consec_days from a group by user_id,ffdate) select user_id ,max(consec_days) as max_consec_days from b ...