高频SQL50题（基础版）-3

主要内容

LeetCode-高频SQL50题（基础版）21-30

一.SQL练习题

1.1174-即时食物配送

在这里插入图片描述

代码如下（示例）:

# Write your MySQL query statement below
Select round(avg(order_date = customer_pref_delivery_date)*100,2) immediate_percentage
from (
    select *,row_number() over(partition by customer_id order by order_date) as rn 
    from delivery 
) a 
where rn = 1

2.550-游戏玩法分析

在这里插入图片描述

代码如下（示例）:

# Write your MySQL query statement below

select round(avg(a.event_date is not null), 2) fraction
from 
    (select player_id, min(event_date) as login
    from activity
    group by player_id) p 
left join activity a 
on p.player_id=a.player_id and datediff(a.event_date, p.login)=1

3.2356-每位教师所教授的科目种类的数量

在这里插入图片描述

代码如下（示例）:

# Write your MySQL query statement below
select teacher_id,count(distinct subject_id) as cnt 
from teacher 
group by teacher_id;

4.1141-查询近30天活跃用户数

在这里插入图片描述

代码如下（示例）:

# Write your MySQL query statement below
SELECT activity_date day, COUNT(DISTINCT  user_id) active_users
FROM Activity
WHERE DATEDIFF("2019-07-27", activity_date) < 30 AND DATEDIFF("2019-07-27", activity_date) >= 0
GROUP BY activity_date;

5.1084-销售分析

在这里插入图片描述

代码如下（示例）:

# Write your MySQL query statement below
select p.product_id,product_name 
from sales s,product p 
where s.product_id = p.product_id
group by p.product_id
having sum(sale_date < '2019-01-01') = 0
and sum(sale_date > '2019-03-31') = 0;

6.596-超过5名学生的课

在这里插入图片描述

代码如下（示例）:

# Write your MySQL query statement below
select class
from courses
group by class
having count(distinct(student))>=5;

7.1729-求关注者的数量

在这里插入图片描述

代码如下（示例）:

# Write your MySQL query statement below
select user_id, count(follower_id) as followers_count
from followers 
group by user_id
order by user_id;

8.619-只出现一次的最大数字

在这里插入图片描述

代码如下（示例）:

# Write your MySQL query statement below
select max(num) as num
from (
    select num 
    from MyNumbers
    group by num 
    having count(num)=1) as t ;

9.1045-买下所有产品的客户

在这里插入图片描述

代码如下（示例）:

# Write your MySQL query statement below
select distinct c.customer_id
from customer c
left join product p 
on c.product_key = p.product_key
group by c.customer_id
having count(distinct p.product_key) = (select count(distinct product_key)from product)

10.1731-每位经理的下属员工数量

在这里插入图片描述

代码如下（示例）:

# Write your MySQL query statement below
select 
    b.employee_id,
    b.name,
    count(a.reports_to) reports_count,
    round(avg(a.age),0) average_age
from employees a,employees b 
where a.reports_to = b.employee_id
group by a.reports_to
having reports_count != 0 
order by employee_id;