《SQL数据分析——从基础破冰到面试题解》题解3

题解》题解3"/>

《SQL数据分析——从基础破冰到面试题解》题解3

9个高难度的SQL题目

1、活跃用户分析

select a.user_id,a.login_date
from(select user_id,login_datefrom (select user_id,login_date,rank() over (partition by user_id order by login_date desc) as rankingfrom actice_user_analysis)aawhere ranking=1
)a
inner join(select user_idfrom active_user_analysisgroup by user_idorder by count(*) desclimit 3
)b
on a.user_id=b.user_id
order by a.user_id,login_date desc

2、连续登录用户分析

select b.user_id,min(b.`date`) as min_date,max(b.`date`) as max_date,count(1) as num
from(select a.user_id,a.`date`,date_sub(a.`date`,interval a.ranking day) as difffrom(select *,rank() over (partition by user_id order by `date`) as rankingfrom user_login)a
)b
group by b.user_id,b.diff
having num>2

3、商品价格中位数

select apany,avg(a.price) as price
from(select apany,a.pricefrom(select company,commodity_id,price,row_number() over (partition by company order by price) as ranking,count(1) over (partition by company) as cnt,count(1) over (partition by company)/2 as even_mid,ceil(count(1) over (partition by company)/2) as odd_midfrom commodity_price)awhere (mod(cnt,2)=0 and ranking in (even_mid,even_mid+1)) or (mod(cnt,2)=1 and ranking=odd_mid)order by company
)a
group by apany

4、特定时间的商品价格

select t1modity_id,t1.new_price as price
from commodity_price t1
where (commodity_id,adjust_date) in (select commodity_id,max(adjust_date)from commodity_pricewhere adjust_date>='2021-03-17'group by commodity_id)
union all
select distinct t2modity_id,100 as price
from commodity_price t2
where (commodity_id,adjust_date) in (select commodity_id,min(adjust_date)from commodity_pricegroup by commodity_idhaving min(adjust_date)>'2021-03-17')

5、团队积分赛

select team_id,team_name,sum(score) as score
from(select team_a as team_id,case when score_a>score_b then 100when score_a=score_b then 30else 0 end as scorefrom team_competitionunion allselect team_b as team_id,case when score_b>score_a then 100when score_a=score_b then 30else 0 end as scorefrom team_competition
)a
inner join team t
on t.team_id=a.team_id
group by team_id,team_name
order by score desc,team_id

6、小程序体验分析

select applet,avg(new_ranking) as avg_ranking
from(select user_id,applet,visit_time,rank() over (partition by user_id order by visit_time) as new_rankingfrom(select user_id,applet,visit_time,rank() over (partition by user_id,applet order by visit_time) as rankingfrom applet_use)a where ranking=1
)b
group by applet

7、用户购买渠道分析

select t1.purchase_date,t1.channel,t2.sum_amount,t2.total_users
from(select distinct a.purchase_date,b.channelfrom purchase_channel a,(select 'app' as channelunionselect 'web' as channelunionselect 'both' as channel)b
)t1
left join(select purchase_date,channel,sum(sum_amount) as sum_amount,sum(total_users) as total_usersfrom(select purchase_date,min(channel) as channel,sum(purchase_amount) as sum_amount,count(distinct user_id) as total_usersfrom purchase_channelgroup by purchase_datehaving count(distinct channel)=1unionselect purchase_date,'both' as channel,sum(purchase_amount) as sum_amount,count(distinct user_id) as total_usersfrom purchase_channelgroup by purchase_datehaving count(distinct channel)>1)cgroup by purchase_date,channel
)t2
on t1.purchase_date=t2.purchase_date and t1.channel=t2.channel

8、游戏关卡分析

select count(*) as num
from(select a.user_id,count(distinct a.min_game_date) as numfrom(select user_id,level_id,game_date,min(game_date) over (partition by user_id,level_id) as min_game_datefrom game_levelwhere game_date between '2021-03-01' and '2021-03-03')awhere a.game_date=a.min_game_dategroup by a.user_idhaving num=3
)b

9、直播中最大在线观众数量

select max(current_num) as max_num
from(select change_time,sum(change_num) over (order by change_time) as current_numfrom(select user_id,enter_time as change_time,1 as change_numfrom watch_liveunion allselect user_id,quit_time as change_time,-1 as change_numfrom watch_live)a
)b