2021-02-28

185.部门工资前三高的所有员工

# method 1
select Department, Employee, Salary from 
(select d.name as Department, e.Name as Employee, e.Salary,
dense_rank() over(partition by DepartmentId order by Salary desc) as 'rank' 
from Employee e 
inner join Department d 
on e.DepartmentId = d.Id) sub 
where sub.rank <= 3

# method 2
select d.Name as Department, e1.Name as Employee, e1.salary 
from Department d, Employee e1, Employee e2
where d.Id = e1.DepartmentId and e1.DepartmentId = e2.DepartmentId and e1.Salary <=e2.Salary 
group by e1.name
having count(distinct e2.Salary) <= 3
order by d.Name, e1.Salary desc, e1.Name

# method 3
select
    d.Name as 'Department', e1.name as 'Employee', e1.Salary
from 
    Employee e1 
    join Department d on e1.DepartmentId = d.Id 
where 3 > (
    select count(distinct e2.Salary) from Employee e2 
    where e2.Salary > e1.Salary and e1.DepartmentId = e2.DepartmentId
)

262.行程和用户

# method 1
select request_at as 'Day',
    round(sum(if(Status!='completed',1,0))/count(*),2) as 'Cancellation Rate'
from Trips t 
 join Users u1 on t.Client_Id = u1.Users_Id and u1.Banned = 'No'
 join Users u2 on t.Driver_Id = u2.Users_Id and u2.Banned = 'No'
 WHERE	
    request_at BETWEEN '2013-10-01' AND '2013-10-03'
group by request_at

# method 2
# Write your MySQL query statement below 
select  Request_at as Day, 
    round(sum(if(Status!='completed',1,0))/count(*),2) as 'Cancellation Rate'
from 
(select *
from Trips 
where Client_Id  not in (select Users_Id from Users where Banned = 'Yes')  
    and Driver_Id not in (select Users_Id from Users where Banned = 'Yes') 
    and Request_at between '2013-10-01' and '2013-10-03'
) sub 
group by Request_at

569.员工薪水中位数

# method 1
SELECT Id, Company, Salary
FROM Employee
WHERE Id in
(select e1.Id 
from Employee e1, Employee e2 
where e1.Company = e2.Company
group by e1.Id
having sum(case when e1.Salary <= e2.Salary then 1 else 0 end) >= count(*)/2
and sum(case when e1.Salary >= e2.Salary then 1 else 0 end) >= count(*)/2)
GROUP BY Company, Salary
ORDER BY Company

# method 2
select Id, Company, Salary
from 
(select *, row_number() over(partition by Company order by Salary) as ranking,
count(*) over(partition by Company) as num
from Employee) sub 
where ranking >= num/2 and ranking <= num/2+1

571.给定数字的频率查询中位数

百度百科：一个数集中最多有一半的数值小于中位数，也最多有一半的数值大于中位数。
# method 1
select avg(Number) as median 
from(
    select Number,
    sum(Frequency) over(order by Number) as asc_num,
    sum(Frequency) over(order by Number desc) as desc_num
    from Numbers 
) t1, (select sum(Frequency)/2 as total from Numbers) t2
where t1.asc_num >= t2.total and t1.desc_num >= t2.total

# method 2
select avg(sub.Number) as median 
from 
(select 
    n1.Number,
    sum(if(n2.Number < n1.Number, n2.Frequency, 0)) as count_small,
    sum(if(n2.Number > n1.Number, n2.Frequency, 0)) as count_big,
    s.total 
from Numbers n1, Numbers n2, (select sum(Frequency) total FROM Numbers) s
group by n1.Number
having count_small<= floor(s.total/2 ) and count_big <= floor(s.total/2 )) sub 

579.查询员工的累计薪水

# method 1  两表连接
select a.Id, a.Month, sum(b.Salary) as Salary
from Employee a 
join Employee b 
on a.Id = b.Id and a.Month >= b.Month and b.Month >= a.Month-2
where (a.Id, a.Month) not in (select Id, Max(Month) from Employee group by Id)
group by a.Id, a.Month
order by a.Id, a.Month desc

# method 2 三表连接
select 
    a.Id, 
    a.Month, 
    (ifnull(a.Salary, 0) + ifnull(b.Salary, 0) + ifnull(c.Salary, 0)) as Salary
from Employee a
left join Employee b on a.Id = b.Id and a.Month = b.Month + 1
left join Employee c on a.Id = c.Id and a.Month = c.Month + 2 
where (a.Id, a.Month) not in (select Id, max(Month) from Employee group by Id)
group by a.Id, a.Month  
order by a.Id, a.Month desc 


# method 3
select Id, Month, Salary 
from 
(select Id, Month, 
    sum(Salary) over(partition by Id order by Month rows between 2 preceding and current row) as Salary, 
    lead(Month,1,0) over(partition by Id order by Month) lm
from Employee) sub 
where lm != 0 
order by Id, Month desc

601.体育馆的人流量

# method 1
with sub as(
    select 
        id, visit_date, people,
        id - rank() over(order by id) rk 
    from Stadium 
    where people >= 100
)
select 
    id, visit_date, people 
from sub  
where rk in 
(select rk from sub group by rk having count(*)>=3)

# method 2 lead lag函数！！
select 
    id, visit_date, people
from 
(select 
    id, visit_date,
    lag(people, 2)  over(order by id) as lg2,
    lag(people, 1)  over(order by id) as lg,
    people,
    lead(people, 1)  over(order by id) as ld1,
    lead(people, 2)  over(order by id) as ld2
from Stadium) sub 
where (sub.lg2 >= 100 and sub.lg >= 100 and sub.people >= 100)
or (sub.lg>=100 and sub.people>=100 and sub.ld1 >= 100)
or(sub.people>=100 and sub.ld1>=100 and sub.ld2>=100)
# method 3
select 
    distinct a.id, a.visit_date, a.people
from Stadium a, Stadium b, Stadium c 
where ((a.id = b.id - 1 and b.id = c.id -1) or (a.id = b.id+1 and a.id = c.id-1) or (b.id = c.id-1 and c.id = a.id -1)) and a.people >=100 and b.people>=100 and c.people>=100
order by a.id 

1097. 游戏玩法分析

# method 1
select 
    install_dt,  
    count(distinct player_id) as installs,
    round(
        ifnull(
        sum(case when datediff(event_date, install_dt)=1 then 1 else 0 end)/count(distinct player_id) 
        ,0)
    ,2) as Day1_retention 
from 
(select player_id , event_date, min(event_date) over(partition by player_id) as install_dt from Activity) sub 
group by install_dt

# method 2
select 
    b.install_dt, count(*) as installs, 
    round(count(distinct a.player_id)/count(*),2) as Day1_retention 
from
(select player_id, min(event_date) as install_dt  from Activity group by player_id) b  
left join Activity a 
on b.player_id = a.player_id and datediff(a.event_date, b.install_dt ) = 1
group by b.install_dt 

1164.指定日期的产品价格

# method 1
select product_id, new_price as price 
from Products 
where (product_id, change_date) in 
(select  product_id, max(change_date) as change_date
from Products 
where change_date <='2019-08-16'
group by product_id)
union 
select distinct product_id, 10 from Products 
where product_id not in (select product_id from Products where change_date <= '2019-08-16')

# method 2 
select p1.product_id, ifnull(p2.new_price, 10) as price 
from 
(select distinct product_id from products) p1 
left join 
(select product_id, new_price 
from Products
where (product_id, change_date) in 
(select product_id, max(change_date)
from Products
where change_date <= '2019-08-16'
group by product_id)) p2 
on p1.product_id = p2.product_id

1194. 锦标赛优胜者

# method 1  最后一个group by 是为了取出每个group_id的第一行。
select group_id, player_id 
from 
(select sub.player_id, sum(score) as score, group_id 
from 
(select first_player as player_id, sum(first_score) as score from Matches group by first_player
union all
select second_player as player_id, sum(second_score) as score from Matches group by second_player) sub 
inner join players p 
on sub.player_id = p.player_id
group by sub.player_id
order by score desc, sub.player_id) result 
group by group_id

# method 2
select group_id, player_id
from 
(select 
    group_id, player_id, 
    row_number() over(partition by group_id order by score desc, player_id asc) as rk
from 
(select 
    group_id, player_id,
    sum(
        case when first_player = player_id then first_score
        when second_player = player_id then second_score end 
    ) as score 
from Matches m
inner join Players p 
on m.second_player = p.player_id or m.first_player = p.player_id 
group by group_id,player_id
) result1
)result2 where rk = 1

1204.最后一个能进入电梯的人

# method 1
select person_name from 
(select person_name, sum(weight) over(order by turn) as sum_all 
from Queue
order by sum_all desc) sub 
where sum_all <= 1000
limit 1

# method 2
select a.person_name 
from Queue a, Queue b 
where a.turn >= b.turn 
group by a.person_id 
having sum(b.weight) <= 1000
order by a.turn desc 
limit 1

# method 3
select person_name       
from Queue q 
where (select sum(weight) from Queue where turn <= q.turn) <= 1000
order by turn desc 
limit 1

1264.页面推荐

# method 1
select distinct page_id as recommended_page from Likes 
where user_id in 
(
    select user1_id from Friendship where user2_id = 1
    union all 
    select user2_id from Friendship where user1_id = 1
)
and page_id not in (select page_id from Likes where user_id =1)

# method 2
select distinct page_id as recommended_page from Likes 
where user_id in 
(
    select (case when user1_id = 1 then user2_id
                 when user2_id = 1 then user1_id
            end
    )
    from Friendship 
    where user1_id = 1 or user2_id =1
)
and page_id not in (select page_id from Likes where user_id =1)

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

# method 1
select a.log_id as start_id, min(b.log_id) as end_id
from 
(select log_id from Logs where log_id-1 not in (select log_id from Logs)) a,    
(select log_id from Logs where log_id+1 not in (select log_id from Logs)) b 
where a.log_id <= b.log_id 
group by a.log_id

# method 2
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 asc)) as num from Logs 
) sub 
group by num 

1308.不同性别每日分数总计

# method 1
select gender, day, sum(score_points) over(partition by gender order by day asc) as 'total'
from Scores 

# method 2
SELECT s1.gender, s1.day, SUM(s2.score_points) AS total
FROM Scores AS s1, Scores AS s2
WHERE s1.gender = s2.gender AND s1.day >= s2.day
GROUP BY s1.gender, s1.day
ORDER BY s1.gender, s1.day;

1321. 餐厅营业额变化增长

# method 1
select 
a.visited_on, sum(b.amount) as amount, 
round(sum(b.amount)/7,2) as average_amount 
from 
(select distinct visited_on  from Customer) a 
inner join Customer b 
on datediff(a.visited_on, b.visited_on) between 0 and 6    
where a.visited_on >= (select min(visited_on) from Customer) +6
group by a.visited_on

# method 2
select  visited_on,amount,average_amount
from
(
	select 
		visited_on,
		sum(total) over(order by visited_on rows between 6 preceding and current row) as amount,
		round(avg(total) over(order by visited_on rows between 6 preceding and current row),2) as average_amount
	
	from(select visited_on,sum(amount) as total from Customer group by visited_on order by visited_on)as t

)as t3
where visited_on >= (select min(visited_on) from Customer) +6
-- where visited_on in (select t1.visited_on from Customer t1, Customer t2 where datediff(t1.visited_on, t2.visited_on)=6)

1355.活动参与者

# method 1 
select activity from Friends
group by activity
having count(*) > any(select count(*) from Friends group by activity)
and count(*) < any(select count(*) from Friends group by activity)

# method 2
select activity from Friends
group by activity
having count(*) != (select count(*) from Friends group by activity order by count(*) desc limit 1)
and count(*) != (select count(*) from Friends group by activity order by count(*) asc limit 1)

# method 3
select activity from 
(select activity, 
    rank() over(order by count(*) asc) as 'rank1',
    rank() over(order by count(*) desc) as 'rank2'
from Friends
group by activity) sub 
where sub.rank1 <> 1 and sub.rank2 <> 1

1369. 获取最近第二次的活动

# method 1
select 
    username, activity, startDate, endDate
from 
(select 
    *,
    row_number() over(partition by username order by startDate desc) as rk ,
    count(*) OVER(partition BY username) as num 
from UserActivity) sub 
where rk =2 or num =1

# method 2
select 
    u1.*
from UserActivity u1 
join UserActivity u2
using(username)
group by u1.username, u1.startdate
having sum(u2.startdate > u1.startdate) = 1 or count(u1.username) = 1

1412. 找出成绩处于中游的学生

# method 1
select 
    student_id, student_name 
from Student 
where student_id not in 
(
  select student_id 
  from Exam e 
  where score >= all(select score from Exam where exam_id = e.exam_id) or score <= all(select score from Exam where exam_id = e.exam_id)
) 
and student_id in (select student_id from Exam)

# method 2
select sub_rk.student_id,s.student_name
from 
    (
        select 
            exam_id, student_id, score, 
            if(rank() over(partition by exam_id order by score)=1,1,0) as asc_rk,
            if(rank() over(partition by exam_id order by score desc)=1,1,0) as desc_rk
        from Exam
    ) sub_rk
inner join Student s 
using(student_id)
group by sub_rk.student_id
having sum(asc_rk) = 0 and sum(desc_rk) = 0

1454. 活跃用户

# method 1
select 
	distinct l1.id, ac.name
from Logins l1
join Logins l2
on l1.id = l2.id and datediff(l2.login_date,l1.login_date) between 0 and 4
inner join Accounts ac on l1.id = ac.id
group by l1.id,l1.login_date
having count(distinct l2.login_date) >= 5

# method 2 
select id,name from Accounts 
where id in 
(select id from 
(select 
	 distinct id, login_date, 
     date_sub(login_date, INTERVAL dense_rank() over(partition by id order by login_date) DAY) as diff
from Logins) sub 
group by id,diff
having count(*) >= 5) 
order by id 

1549. 每件产品的最新订单

# method 1
select product_name, product_id, order_id, order_date
from 
(select p.product_name, o.*, rank() over(partition by o.product_id order by o.order_date desc) as 'rank' from Orders o 
inner join Products p using(product_id)) sub 
where sub.rank = 1
order by product_name, product_id, order_id

# method 2
select p.product_name, o.product_id, o.order_id, o.order_date 
from Orders o 
inner join Products p using(product_id)
where (o.product_id, o.order_date) in 
(select product_id, max(order_date) as order_date from Orders 
group by product_id)
order by p.product_name, o.product_id, o.order_id

1699. 两人之间的通话次数

# method 1
select if(from_id<to_id, from_id, to_id) as person1,
    if(from_id<to_id, to_id, from_id) as person2,
    count(*) as call_count,
    sum(duration) as total_duration 
from Calls 
group by person1, person2

# method 2
select from_id as person1,
        to_id as person2,
        count(*) as call_count,
        sum(duration) as total_duration 
from Calls 
group by if(from_id<to_id, from_id, to_id), if(from_id<to_id, to_id, from_id)

# method 3
select from_id as person1,
        to_id as person2,
        count(*) as call_count,
        sum(duration) as total_duration 
from Calls 
group by least(from_id, to_id), greatest(to_id, from_id)