https://leetcode.cn/studyplan/sql-premium-50/

一、查询

1821. 寻找今年具有正收入的客户

select
    customer_id
from Customers
where year = 2021
group by customer_id
having sum(revenue) > 0

183. 从不订购的客户

select 
    c.name as Customers
from Customers c
left join Orders o on c.id = o.customerId
where o.id is null

1873. 计算特殊奖金

select
    employee_id
    ,case when mod(employee_id,2) = 1 and substr(name,1,1) != 'M' then salary
        else 0
    end as bonus
from Employees
order by employee_id

1398. 购买了产品 A 和产品 B 却没有购买产品 C 的顾客

select
    customer_id,customer_name
from Customers
where customer_id in (
    select
        customer_id
    from Orders
    where product_name in ('A','B')
    and customer_id not in (select customer_id from Orders where product_name = 'C')
    group by customer_id
    having count(distinct product_name) = 2
)
order by customer_id

1112. 每位学生的最高成绩

select  student_id,course_id,grade 
from (
select
    e.*
    ,rank() over(partition by student_id order by grade desc,course_id) as rk 
from Enrollments e 
) where rk = 1
order by student_id

二、连接

175. 组合两个表

select
    firstName,lastName,city,state
from Person p 
left join Address a on p.PersonId = a.PersonId

1607. 没有卖出的卖家

select seller_name
from Seller
where seller_id not in (
select 
    seller_id
from Orders
where to_char(sale_date,'yyyy') = '2020'
)order by seller_name

1407. 排名靠前的旅行者

select
    name,nvl( sum(distance) ,0) as travelled_distance
from Users u 
left join Rides r on u.id = r.user_id
group by name,u.id
order by travelled_distance desc,name 

607. 销售员

select
    name
from SalesPerson
where sales_id not in (
    select
        sales_id
    from Orders o
    left join Company c on o.com_id = c.com_id
    where c.name = 'RED'
)

1440. 计算布尔表达式的值

select 
	e.*,
	case when operator = '>' and v1.value > v2.value then 'true'
		when operator = '<' and v1.value < v2.value then 'true'
		when operator = '=' and v1.value = v2.value then 'true'
		else 'false'
	end as value
	
from Expressions e 
join Variables v1 on e.left_operand = v1.name
join Variables v2 on e.right_operand = v2.name 

1212. 查询球队积分

with t1 as (
	select
		m.*,
		case when host_goals > guest_goals then 3
			when host_goals = guest_goals then 1
			else 0 
		end as host_p
		,case when host_goals < guest_goals then 3
			when host_goals = guest_goals then 1
			else 0 
		end as gust_p
	from Matches m 
)

select
	t.team_id ,team_name,nvl(sum_p,0) as num_points
from Teams t 
left join (
	select
		team_id,sum(p) as sum_p
	from (
	select
		host_team as team_id
		,host_p as p 
	from t1
	union all 
	select
		guest_team as team_id
		,gust_p as p 
	from t1
	) group by team_id
) a on t.team_id = a.team_id
order by num_points desc,team_id

三、聚合函数

1890. 2020年最后一次登录

select user_id,last_stamp 
from (
    select 
        user_id,time_stamp as last_stamp 
        ,row_number() over(partition by user_id order by time_stamp desc) as rk 
    from Logins
    where to_char(time_stamp,'yyyy') = '2020'
) where rk = 1

511. 游戏玩法分析 I

select
    player_id
    ,to_char( min(event_date) ,'yyyy-mm-dd')  as  first_login
from Activity
group by player_id

1571. 仓库经理

select
    name as warehouse_name
    ,nvl(
        sum(units * Width * Length * Height)
        ,0
    ) as volume
from Warehouse w 
left join Products p on w.product_id = p.product_id
group by name

586. 订单最多的客户

select * from (
    select
        customer_number
    from Orders
    group by customer_number
    order by count(order_number) desc
) where rownum <= 1

1741. 查找每个员工花费的总时间

select
    to_char(event_day,'yyyy-mm-dd') as day
    ,emp_id
    ,sum( out_time - in_time ) as total_time
from Employees
group by emp_id,event_day

1173. 即时食物配送 I

select
    round(
        count(case when order_date = customer_pref_delivery_date then delivery_id end)*100
        / count( delivery_id )
        ,2
    ) as immediate_percentage
from Delivery

1445. 苹果和桔子

select
    to_char(sale_date) as sale_date
    ,sum(case when fruit = 'apples' then sold_num end) 
        - sum(case when fruit = 'oranges' then sold_num end)
        as diff
from Sales
group by to_char(sale_date)
order by sale_date

1699. 两人之间的通话次数

with t1 as (
    select
        from_id as p1
        ,to_id as p2
        ,duration
    from Calls
    union all
    select
        to_id as p1
        ,from_id as p2
        ,duration
    from Calls
)

select
    p1 as person1
    ,p2 as person2
    ,count(*) as call_count
    ,sum(duration) as total_duration
from t1 
where p1 < p2 
group by p1,p2 

四、排序和分组

1587. 银行账户概要 II

select
    u.name,sum(amount) as balance
from Users u 
left join Transactions t on u.account = t.account
group by u.account,u.name
having sum(amount) > 10000

182. 查找重复的电子邮箱

select
    email
from Person
group by email
having count(id) > 1

1050. 合作过至少三次的演员和导演

select
    actor_id,director_id
from ActorDirector
group by actor_id,director_id
having count(*) >= 3

1511. 消费者下单频率

select distinct customer_id,name from (
    select
        c.customer_id,c.name
        , count(to_char(order_date,'yyyy-mm') ) over(partition by c.customer_id) as mon
    from Orders o 
    left join Product p on o.product_id = p.product_id
    left join Customers c on o.customer_id = c.customer_id
    where to_char(order_date,'yyyy-mm') in ('2020-06','2020-07')
    group by c.customer_id,c.name,to_char(order_date,'yyyy-mm')
    having sum(price * quantity) >= 100
) where mon > 1

1495. 上月播放的儿童适宜电影

select
    distinct title 
from TVProgram t 
join Content c on t.content_id = c.content_id
where Kids_content = 'Y'
and to_char(program_date,'yyyy-mm') = '2020-06'
and content_type = 'Movies'

1501. 可以放心投资的国家

with t1 as (
    select caller_id as c1,callee_id as c2 ,duration from Calls
    union all 
    select callee_id as c1,caller_id as c2 ,duration from Calls
)

select name as country from (
    select
        c.name
        ,avg(duration) as avg_duration
    from t1  
    join Person p on p.id = t1.c1 
    join Country c on substr(p.phone_number,1,3) = c.country_code
    group by c.name
) where avg_duration > (select avg(duration) from Calls)

五、高级查询和连接

603. 连续空余座位

select seat_id from (
    select 
        seat_id,count(seat_id) over(partition by gap) as cnt
    from (
    select
        seat_id
        ,seat_id - rank() over(order by seat_id) as gap
    from Cinema
    where free = 1
    )
) where cnt > 1
order by seat_id

1795. 每个产品在不同商店的价格

select
    product_id , store  , price 
from Products
unpivot(
    price for store in (
        store1 as 'store1'
        ,store2 as 'store2'
        ,store3 as 'store3'
    )
)

613. 直线上的最近距离

select
    min( abs(x - lag_x) ) as shortest
from (
select
    x,lag(x) over(order by x) as lag_x
from Point
)

1965. 丢失信息的雇员

select
    nvl(e.employee_id,s.employee_id) as employee_id
from Employees e 
full join Salaries  s on e.employee_id = s.employee_id
where e.employee_id is null or s.employee_id is null
order by employee_id

1264. 页面推荐

with t1 as (
    select 
        user1_id as u1 ,user2_id as u2 
    from Friendship  
    union all 
    select 
        user2_id as u1 ,user1_id as u2 
    from Friendship 
)

select
    distinct page_id as recommended_page
from Likes l
join t1  on l.user_id = t1.u2
and t1.u1 = 1
and page_id not in (select page_id from Likes where user_id = 1)

608. 树节点

select
    id
    ,case when p_id is null then 'Root'
        when id not in (select p_id from tree where p_id is not null) then 'Leaf'
        else 'Inner'
    end as type
from Tree

534. 游戏玩法分析 III

select
    player_id
    ,to_char(event_date,'yyyy-mm-dd') as event_date
    ,sum(games_played) over(partition by player_id order by event_date
                rows between unbounded preceding and current row ) as games_played_so_far
from Activity

1783. 大满贯数量

with t1 as (
    select
        *
    from Championships
    unpivot(
        cp for game in (
                Wimbledon as 'Wimbledon'
                ,Fr_open as 'Fr_open'
                ,US_open as 'US_open'
                ,Au_open as 'Au_open'
            )
    )
) 

select
    p.player_id,player_name
    ,count(*) as grand_slams_count
from t1 
join Players p on p.player_id = t1.cp 
group by  p.player_id,player_name

1747. 应该被禁止的 Leetflex 账户

select
    distinct l1.account_id
from LogInfo l1 
 join LogInfo l2 on l1.account_id = l2.account_id
and l1.ip_address != l2.ip_address
and l1.login between l2.login and l2.logout

512. 游戏玩法分析 II

select     player_id
    ,device_id
from (
select
    player_id
    ,device_id
    ,rank() over(partition by player_id order by  event_date) as rk 
from Activity
) where rk =1 

184. 部门工资最高的员工

select 
    Department,Employee,Salary
from (
select
    e.name as EMPLOYEE
    ,e.salary
    ,d.name as Department
    ,rank() over(partition by d.name order by salary desc) as rk 
from Employee e 
join Department d on e.departmentId = d.id
) where rk = 1

1549. 每件商品的最新订单

select     
    product_name
    ,product_id
    ,order_id
    ,order_date
from (
    select
        product_name
        ,o.product_id
        ,order_id
        ,to_char(order_date,'yyyy-mm-dd') as order_date
        ,rank() over (partition by o.product_id order by order_date desc) as rk 
    from Orders o 
    join Products p on o.product_id = p.product_id
) where rk = 1
order by product_name,product_id,order_id

1532. 最近的三笔订单

select
    customer_name
    ,customer_id
    ,order_id
    ,order_date
from (
    select
        name as customer_name
        ,o.customer_id
        ,order_id
        ,to_char(order_date,'yyyy-mm-dd') as order_date
        ,row_number() over(partition by o.customer_id order by order_date desc) as rk 
    from Orders o 
    join Customers c on o.customer_id = c.customer_id
)where rk <= 3
order by customer_name,customer_id,order_date desc

1831. 每天的最大交易

select
    transaction_id
from (
    select
        transaction_id
        ,rank() over(partition by trunc(day) order by amount desc ) as rk 
    from Transactions
) where rk = 1
order by transaction_id

六、子查询

1350. 院系无效的学生

select
    id,name
from Students
where department_id not in (
    select id from Departments
)

1303. 求团队人数

select
    employee_id
    ,count(employee_id) over(partition by team_id) as team_size
from Employee

七、窗口函数和公共表表达式CTE

1077. 项目员工 III

select 
    project_id,employee_id
from (
    select
        project_id
        ,p.employee_id
        ,rank() over(partition by project_id order by experience_years desc) as rk 
    from Project p 
    left join Employee e  on e.employee_id = p.employee_id
)where rk = 1

1285. 找到连续区间的开始和结束数字

select
    min(log_id) as start_id
    ,max(log_id) as end_id
from(
    select
        log_id
        ,log_id - row_number() over(order by log_id) as gap
    from Logs l 
)group by gap
order by start_id

1596. 每位顾客最经常订购的商品

select 
    a.customer_id,a.product_id,p.product_name
from (
    select
        product_id,customer_id
        ,rank() over(partition by customer_id order by count(order_id) desc) as rk 
    from Orders
    group by product_id,customer_id
) a 
join Products p on a.product_id = p.product_id and a.rk = 1

1709. 访问日期之间最大的空档期

select 
    user_id,max(gap) as biggest_window
from (
    select
        user_id,visit_date
        ,lead(visit_date,1,to_date('2021-1-1','yyyy-mm-dd')) 
            over(partition by user_id order by visit_date) - visit_date as gap
    from UserVisits
)group by user_id 

1270. 向公司 CEO 汇报工作的所有人

select distinct 
    employee_id
from Employees
where employee_id != 1
start with manager_id = 1
connect by nocycle prior employee_id = manager_id