题目一:银行账号概要II
题目要求:
编写解决方案, 报告余额高于 10000 的所有用户的名字和余额. 账户的余额等于包含该账户的所有交易的总和。
返回结果表单 无顺序要求 。
表结构:
运行结果示例:
思路:
我们根据账户连接两张表,获得每个账户对应的用户名及其流水信息,再按照账户进行分组聚合,计算每个账户下的余额信息。最后使用having子句筛选出余额大于10000的用户名及余额。
运行代码示例:
select name, sum(amount) as balance
from Users
left join Transactions
using(account)
group by Users.account
having sum(amount) > 10000
题目二:查找重复的电子邮箱
题目要求:
编写解决方案来报告所有重复的电子邮件。 请注意,可以保证电子邮件字段不为 NULL。
以 任意顺序 返回结果表。
表结构:
运行结果示例:
思路:
本题要求我们寻找重复出现过的电子邮件,我们直接按照电子邮件作为分组字段进行分组,统计每组数据的数量,也就是每个不同的电子邮件的数目,找到数目大于1的即为重复出现的电子邮件。
运行代码示例:
select email as Email
from Person
group by email
having count(*) > 1
题目三:合作过至少三次的演员和导演
题目要求:
编写解决方案找出合作过至少三次的演员和导演的 id 对
(actor_id, director_id)
表结构:
运行结果示例:
思路:
只要根据actor_id和director_id这两个字段分组,再用having子句统计数量筛选就好了。
运行代码示例:
select t1.actor_id,t1.director_id
from ActorDirector t1
group by t1.actor_id,t1.director_id
having count(*) >= 3
题目四:消费者下单频率
题目要求:
写一个解决方案,报告在 2020 年 6 月和 7 月 每个月至少花费
$100
的客户的customer_id
和customer_name
。以 任意顺序 返回结果表.
表结构:
运行结果示例:
思路:
本题涉及到了三张表,我们首先需要将这三张表连接起来,获取一张总表,之后再按客户id和姓名进行分组,将筛选条件放在having子句中即可。题目中要找满足6月和7月的花费总额都大于100的,我们可以用case when进行判断。
运行代码示例:
select c.customer_id, name
from Customers c
left join Orders o
on c.customer_id = o.customer_id
join Product p
on o.product_id = p.product_id
-- where left(order_date ,7) = '2020-06' or left(order_date ,7) = '2020-07'
group by customer_id, name
having sum(case when left(o.order_date,7)='2020-06' then p.price*o.quantity else 0 end)>=100 and sum(case when left(o.order_date,7)='2020-07' then p.price*o.quantity else 0 end)>=100
题目五:每天的领导和合伙人
题目要求:
对于每一个
date_id
和make_name
,找出 不同 的lead_id
以及 不同 的partner_id
的数量。按 任意顺序 返回结果表。
表结构:
运行结果示例:
思路:
我们按照题目中要求的
date_id
和make_name字段进行
分组即可,统计领导者和合伙人的数量,注意由于题目中要求找到“不同的”领导者和合伙人,因此在统计数量时要使用distinct去重。
运行代码示例:
select date_id,make_name,count(distinct lead_id) as unique_leads,count(distinct partner_id) as unique_partners
from DailySales
group by date_id, make_name