leetcode高频 SQL 50 题（基础版）

1757. 1可回收且低脂的产品

SELECT product_id FROM Products WHERE  low_fats = 'Y' and recyclable = 'Y';

584. 2寻找用户推荐人

SELECT name FROM Customer where referee_id  != 2 or referee_id is NULL;

595. 3大的国家

SELECT name,population,area FROM World WHERE area >= 3000000 or population >= 25000000;

1148. 4文章浏览 I

SELECT distinct author_id as id FROM Views where author_id = viewer_id order by id asc; 

1683. 5无效的推文

SELECT tweet_id FROM Tweets WHERE length(content) > 15;

1378. 6使用唯一标识码替换员工ID

select unique_id,name 
from Employees e 
left join EmployeeUNI eu 
on e.id = eu.id 

1068. 7产品销售分析 I

select product_name,year,price
from Sales s 
left join Product p 
on s.product_id = p.product_id

1581.8 进店却未进行过交易的顾客

select customer_id,count(visit_id) count_no_trans 
from Visits
where visit_id not  in (select distinct visit_id from Transactions t  )
group by customer_id

197. 9上升的温度

select w2.id
from Weather w1,Weather w2
where 
datediff(w2.recordDate,w1.recordDate) =1
and w1.Temperature < w2.Temperature

1661. 10每台机器的进程平均运行时间

select machine_id,
round(sum(case when activity_type = 'end' then timestamp else -timestamp end)/count(process_id)*2,3) 
processing_time
from Activity
group by machine_id

577.11 员工奖金

select name,bonus from Employee e 
left join Bonus b 
on e.empId  = b.empId 
where bonus < 1000 or bonus is null

1280. 12**学生们参加各科测试的次数*

select st.student_id,st.student_name,s.subject_name,count(e.subject_name) attended_exams 
from Subjects s
join  Students st
left join Examinations e  
on st.student_id = e.student_id
and e.subject_name = s.subject_name
group by s.subject_name,st.student_id
order by student_id,subject_name

570. 13至少有5名直接下属的经理

select name from Employee
where id in (select managerId from Employee
group by managerId
having count(id) >=5)

1934. 14确认率

select s.user_id,
round(ifnull(avg(action  = 'confirmed'),0),2)
 confirmation_rate
from Confirmations c
right join Signups s
on c.user_id = s.user_id
group by s.user_id

or

select s.user_id,
round(count(case when action = 'confirmed' then 5 end)/count(*),2)
 confirmation_rate
from Confirmations c
right join Signups s
on c.user_id = s.user_id
group by s.user_id

or

select s.user_id,
round(sum(case when action = 'confirmed' then 1 else 0 end)/count(s.user_id),2)
 confirmation_rate
from Confirmations c
right join Signups s
on c.user_id = s.user_id
group by s.user_id

or

select s.user_id,
round(sum(if (action = 'confirmed',1,0))/count(*),2)
 confirmation_rate
from Confirmations c
right join Signups s
on c.user_id = s.user_id