595-big-countries

https://leetcode.com/problems/big-countries/description/

面積最大的國家

--
select name, population, area
from World
where area >= 3000000 or population >= 25000000

596-classes-with-at-least-5-students

https://leetcode.com/problems/classes-with-at-least-5-students/description/

超過 5 名學生的課

select class
from Courses
group by class
having count(student) >= 5

having和where關鍵區別：

• WHERE：在聚合之前過濾行。
• HAVING：在聚合之后過濾分組。

總結：WHERE 用于過濾行數據，而 HAVING 用于過濾分組后的數據。

601-human-traffic-of-stadium

https://leetcode.com/problems/human-traffic-of-stadium/description/

體育館的人流量

id連續三行或更多

select distinct a.*
from stadium as a,stadium as b,stadium as c
where a.people >= 100 and b.people >= 100 and c.people >= 100
and ((a.id - b.id = 1 AND b.id - c.id = 1)OR (c.id - b.id = 1 AND b.id - a.id = 1)OR (b.id - a.id = 1 AND a.id - c.id = 1))
order by a.visit_date;
-- 用ID減去排名
with stadium_with_rnk as (select id, visit_date, people, rnk, (id - rnk) as islandfrom (select id, visit_date, people, rank() over (order by id) as rnkfrom stadiumwhere people >= 100) as t0
)
select id, visit_date, people
from stadium_with_rnk
where island in (select islandfrom stadium_with_rnkgroup by islandhaving count(*) >= 3)
order by visit_date

602-friend-requests-ii-who-has-the-most-friends

https://leetcode.com/problems/friend-requests-ii-who-has-the-most-friends/description/

好友申請 II ：誰有最多的好友

擁有最多的好友的人和他擁有的好友數目

with all_ids as (select requester_id as id from RequestAcceptedunion allselect accepter_id from RequestAccepted
)
select id, count(id) as num
from all_ids
group by id
order by count(id) desc limit 1;-- use rank
with all_ids as (select requester_id as id from RequestAcceptedunion allselect accepter_id from RequestAccepted
)
select id, num
from (select id, count(id) as num,rank() over (order by count(id) desc ) as rnkfrom all_idsgroup by id
)t0
where rnk = 1

607-sales-person

https://leetcode.com/problems/sales-person/description/

銷售員

select name
from SalesPerson
where sales_id not in (select Orders.sales_idfrom Ordersjoin Company on Orders.com_id = Company.com_id and Company.name = 'RED')