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

22个简单的SQL题目

本篇文章夹杂着题目和自己的题解，欢迎来交流

1、比赛名单整理

select a.team_name as `队伍A`,b.team_name as `队伍B` from competition_list a inner join competion_list b on a.team_name<b.team_name order by a.team_name,b.team_name

上面是课本中的解答，但自己想用cross join来做，欢迎交流

select a.team_name as `队伍A`,b.team_name as `队伍B` from competition_list a cross join competition_list b on a.team_name<b.team_name order by a.team_name,b.team_name

2、参与优惠活动的商品
本题注意将时间条件分四段asbe\aseb\saeb\sabe

select commodity_id from product_promotion where (start_date<='2021-01-09' and start_date>='2021-01-07')or(end_date>='2021-01-07' and end_date<='2021-01-09')or(end_date>='2021-01-09' and start_date<='2021-01-07')or(start_date>='2021-01-07' and end_date<='2021-01-09')

3、连续售出的商品
找出连续下单大于等于3次的商品ID

自己的解法

select commodity_id from(select commodity_id,order_id,rank() over (partition by commodity_id order by order_id) as ranking,order_id - rank() over (partition by commodity_id order by order_id) as difffrom sold_succession )b group by commodity_id,b.diff having count(*)>=3

课本的题解，有趣

select distinct commodiyt_id from(select commodity_id,order_id,lag(order_id,2) over(partition by commodity_id order by order_id) as temp,from sold_succession )a where order_id=temp+2

4、奇偶互换位置

select (case when mod(student_id,2)=1 and student_id=(select count(*) from student_info) then student_idwhen mod(student_id,2)=1 then student_id+1else student_id-1 end) as student_id,student_name from student_info order by student_id

5、热门游戏排名
自己的解答，需要再研究group_concat的用法

select category,group_concat(game,'2') as game from(select category,gamefrom(select *,rank() over (partition by category order by downloads desc) as rankingfrom game_ranking)bwhere b.ranking<=2 )a group by category

课本的做法，妙啊

select category,substring_index(group_concat(game order by downloads desc),',',2) as game from game_ranking group by category

6、商品销量同环比

select sales_volume,sales_volume/year_on_year as year_ratio,sales_volume/month_on_month as month_ration from(select `month`,lag(sales_volume,1) over(order by 'month') as month_on_month,lag(sales_volume,12) over(order by 'month') as year_on_yearfrom comparative_analysis )b where 'month'='2021-05'

7、社区生鲜APP覆盖分析

自己的解法

select count(distinct user_id) as num from fresh_food where locate('A',app)>0

题目的解法

select sum(case when find_in_set('A',app)>0 then 1 else 0 end) as num from fresh_food

8、社区团购行为分析
(1)查询每个用户首次登录的渠道名称

自己的解法

select user_id,login_source from(select *,rank() over(partition by user_id order by login_date) as rankingfrom group_buy )b where b.ranking=1

课本的解法

select a.user_id,a.login_source from group_buy a inner join(select user_id,min(login_date) as first_login_datefrom group_buygroup by user_id )b on a.user_id=b.user_id and a.login_date=b.first_login_date

(2)查询用户登录日期和累计下载量

select user_id,login_date,sum(order_count) over(partition by user_id order by login_date) as total_order_count from group_buy

9、“双十一”活动的电商GMV分析

select `date`,concat(gmv_rate,'%') as ratio from(select `date`,mall_gmv,(mall_gmv-lag(mall_gmv,7) over (order by 'date')) as gmv_diff,(mall_gmv-lag(mall_gmv,7) over (order by 'date'))/lag(mall_gmv,7) over (order by 'date') *100 as gmv_ratefrom gmv_infowhere 'date' between '2020-11-04' and '2020-11-17' )t where `date` between '2020-11-11' and '2020-11-17'

10、统计字符出现次数

select text_id,length(text_context)-length(replace(text_context,'*','')) as num from original_text

11、网站访问量分析

select a.visit_year,a.max_visit from(select substring(data_content,1,4) as visit_year,substring(data_content,-4,4) as max_visit,rank() over (partition by substring(data_content,1,4) order by substring(data_content,-4,4) desc) as rankingfrom website_visitorder by data_content )a where a.ranking=1 order by a.visit_year

12、文本记录连接

select text_id,group_concat(text_content separator '&') as new_text from convert_table group by text_id order by text_id

13、行列互换

select `year`,sum(case when quarter=1 then amount else 0) as `一季度`,sum(case when quarter=2 then amount else 0) as `二季度`,sum(case when quarter=3 then amount else 0) as `三季度`,sum(case when quarter=4 then amount else 0) as `四季度` from purchase_quantity group by `year`

14、寻找符合要求的订单

select order_id,abs(money-sum(money) over (order by order_id)) as diff from consumer_order order by diff,order_id limit 1

15、用户购物信息统计

select a.user_id,a.register_date as reg_date,count(b.order_id) as order_2021 from user_register_info a left join user_order_info b on a.user_id=b.user_id where year(order_date)=2021 group by a.user_id,a.register_date

但是有些用户如果在2021年没有消费的话，上面的方法则没法展示出来。这是不符合要求的，因此选用下面的方式

select aa.user_id,aa.register_id,ifnull(bb.order_2021,0) as order_2021 from user_register_info aa left join(select b.user_id,b.register_date,count(order_id) as order_2021from user_order_info aleft join user_register_info bon a.user_id=b.user_idwhere year(order_date)=2021group by b.user_id,b.register_date )bb on aa.user_id=bb.user_id

16、用户首单消费金额

select user_id,payment from(select user_id,payment,rank() over (partition by user_id order by paytime) as rankingfrom user_order )a where a.ranking=1

17、优惠券使用分析

select a.user_id,count(collection_date) as num from coupon_collection a inner join consumption_info b on a.user_id=b.user_id where datediff(consumption_date,collection_date) between 1 and 7 group by a.user_id

18、游戏玩家登录情况分析

select user_id,count(`date`) as num from(select user_id,substring(login_time,1,10) as `date`from game_login )a group by user_id,`date` having count(`date`)>1

19、员工绩效考核

select employee_id,total_score from(select employee_id,case when target_a>=8 then 1 else 0 end as target_a,case when target_b>=8 then 1 else 0 end as target_b,case when target_c>=8 then 1 else 0 end as target_c,case when target_d>=8 then 1 else 0 end as target_d,case when target_e>=8 then 1 else 0 end as target_e,target_a+target_b+target_c+target_d+target_e as total_scorefrom employee_performance )a where target_a+target_b+target_c+target_d+target_e>=4 order by total_score desc,employee_id

20、找出各类别商品销量最高的商品

select product_category,product_id,sale from(select *,rank() over (partition by product_category order by sale desc) as rankingfrom product_sale )a where a.ranking=1

21、找出每个部门薪资第二高的员工
题解中用的rank()，但本人认为大概用dense_rank()??欢迎来交流一下

select employee_id,employee_name,employee_salary,department_id from(select *,dense_rank() over (partition by department_id order by employee_salary desc) as rankingfrom employees )a inner join department b on a.department_id=b.department_id where a.ranking=2

22、找出游戏中最活跃的用户

select user_id,count(*) as cnt from(select request_id as user_idfrom pk_infounion allselect accept_id as user_idfrom pk_info )a group by user_id order by cnt limit 1

总结

以上是生活随笔为你收集整理的《SQL数据分析——从基础破冰到面试题解》题解1的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。