题目一:判断其是否有过购买记录
题目要求:
现有牛客顾客购买信息表customers_info,请查询客户id并新增一列判断该客户是否有过购买记录(`latest_place_order_date` 1为有 0为没有)
表结构:
运行结果示例:
思路:
使用case when语句对latest_place_order_date列进行判断,由于latest_place_order_date列中的内容不确定,因此我们可以判断其为空的情况设置为0,其他情况全部设为1,注意要对该列进行重命名(if_placed_order)。
运行代码示例:
select customer_id,
case
when latest_place_order_date is null then 0
else 1
end
as if_placed_order
from customers_info
题目二:请按城市对客户进行排序,如果城市为空,则按国家排序
题目要求:
牛客顾客购买信息表`customers_info`,请按城市对客户进行排序,如果城市为空,则按国家排序,返回全部字段
customer_id|gender|city|country|age|latest_place_order_date
表结构:
运行结果示例:
思路:
还是刷得题目太少了,第一次见到在order by子句里使用条件判断语句的。
运行代码示例:
select customer_id,gender,city,country,age,latest_place_order_date
from customers_info
order by (if(city is not null,city,country))
题目三:分群并计算群体人数
题目要求:
牛客顾客购买信息表`customers_info`
按年龄给客户分群(age_group 分为 '20以下' ,'20-50' , '50以上','未填写' 四个群体),并计算各群体人数并命名为 user_count
表结构:
运行结果示例:
思路:
按照题目要求进行分组,再使用group by子句进行分组聚合统计即可。
运行代码示例:
select (
case
when age < 20 then '20以下'
when age >= 20 and age <= 50 then '20-50'
when age > 50 then '50以上'
else '未填写'
end
) as age_group,
count(*) as user_count
from customers_info
group by age_group
题目四:输出提交次数大于2次的用户ID且倒序排列
题目要求:
牛客刷题记录表`done_questions_record`,输出提交次数大于2次的用户ID且倒序排列,查询返回结果名称和顺序
user_id
表结构:
运行结果示例:
思路:
题目要求筛选出提交次数大于2次的用户ID,这时我们就需要按照id进行分组聚合统计了,查询的条件分组后每组的总数大于2,要用到聚合函数,因此我们可以判断出该题应该用having子句实现而不是where子句。查询出结果后,按照用户id再进行一个排序即可。
运行代码示例:
select user_id
from done_questions_record
group by user_id
having count(*) > 2
order by user_id desc
题目五:输出提交且通过次数大于2 的用户ID且升序排列
题目要求:
牛客刷题记录表`done_questions_record`,输出提交且通过次数大于2 的用户ID且升序排列,result_info '是否通过,1:通过; 0:不通过',查询返回结果名称和顺序
user_id
表结构:
运行结果示例:
思路:
本题和上一题相比,只是多了一个限定条件,输出提交且“通过”次数大于2 的用户,并且将排序顺序从降序改为了升序。整体难度不大。在上一题的基础上,我们要增加一个通过与否的判断,不难想到,我们完全可以先筛选出所有通过的刷题记录,再在此基础上分组查询,也就是先查询再分组,使用where子句即可。
运行代码示例:
select user_id
from done_questions_record
where result_info = 1
group by user_id
having count(*) > 2
order by user_id
题目六: 验证刷题效果,输出题目真实通过率
题目要求:
牛客刷题记录表`done_questions_record`,为验证重复刷题率,需要我们查找一些数据: question_pass_rate 表示每个用户不同题目的通过率(同一用户同一题重复提交通过仅计算一次);pass_rate 表示每个用户的提交正确率(只要有提交一次即计算一次);question_per_cnt表示平均每道不同的题目被提交的次数(只要有一次提交即计算一次),请你输出题目通过率question_pass_rate > 60% 的用户的提交正确率 pass_rate 与每题目平均提交次数 question_per_cnt。按照用户名升序排序。
result_info '是否通过,1:通过; 0:不通过',查询返回结果名称和顺序为
表结构:
运行结果示例:
思路:
本题目的难点在于“阅读理解”(真的服了牛客的命题人了啊啊啊啊啊,早知道就开力扣会员了),这道题并不难,只是题目的叙述不太好,大家直接看看运行代码就好,没必要深挖
运行代码示例:
select user_id,
count(distinct if(result_info=1,question_id,null))/count(distinct question_id) "question_pass_rate",
sum(result_info)/count(done_time) "pass_rate",
count(done_time)/count(distinct question_id) "question_per_cnt"
from done_questions_record
group by user_id
having question_pass_rate>0.6
order by user_id