180 Consecutive Numbers
조건
- 연속 세 번 이상 나타나는 데이터를 찾으시오
# LAG
SELECT DISTINCT l.Num as ConsecutiveNums --연속되는 숫자 찾아오기
FROM (
SELECT DISTINCT Num,
LAG(Num,1) OVER (ORDER BY Id) as lag1,
LAG(Num,2) OVER (ORDER BY Id) as lag2
FROM logs) l
WHERE l.lag1 = l.Num
AND l.Num = l.lag2;
1 1 1
1 1 2
1 2 1
2 1 2
1 2 2
2 2
2
# LEAD
SELECT DISTINCT l.Num as ConsecutiveNums
FROM (
SELECT Num
, LEAD(Num, 1) OVER (ORDER BY Id) as next
, LEAD(Num, 2) OVER (ORDER BY Id) as afternext
FROM logs) l
WHERE l.Num = l.next
AND l.Num = l.afternext
1 1 1
1 1 2
1 2 1
2 1 2
1 2 2
2 2
윈도우 펑션 사용 안하고?
윈도우와 로직은 거의 동일하지만 조인으로 풀기
select distinct a.num as ConsecutiveNums
from logs a
join logs b on a.id + 1 = b.id
join logs c on a.id + 2 = c.id
where a.num = b.num and b.num = c.num
184
- who have the highest salary in each of the departments.
select d.name as department
,e.name as employee
,e.salary as salary
from employee as e, department as d
where e.departmentid = d.id
and (e.departmentid = e.salary) = (select max(salary
from employee
group by departmentId)
SELECT d.Name AS Department
,e.Name AS Employee
,e.Salary AS Salary
FROM Employee AS e, Department AS d
WHERE e.DepartmentID = d.Id
AND e.Salary = (SELECT MAX(Salary)
FROM Employee
WHERE DepartmentId = d.Id) -- from절이 먼저 돌아가니까 가능?with ms as (select departmentid
,max(salary) as max_sal
from employee
group by departmentid)
select d.name as department
, e.name as employee
, e.salary as salary
from employee e
join department d on e.departmentid = d.id
join ms m on e.salary = m.max_sal and e.departmentid = m.departmentid --[넘나 당연한 소리 ㅠ] department 조인 안걸면 중복 출력됨
1075 project employees 1
# 가장 간단하게 그냥 조인해서 풀기
select p.project_id
, round(avg(e.experience_years),2) as average_years
from project p left join employee e on
p.employee_id = e.employee_id
group by p.project_id
If you use a left join, you are assuming all values for Employee can be NULL for a particular project.
As a result, you would need to account for experience_years being NULL and using a solution similar to below instead:
ROUND(AVG(experience_years) ,2) -> ROUND(COALESCE(AVG(experience_years),0) ,2)
There are two popular versions of the solution. One uses a LEFT JOIN and the other an INNER JOIN. Even though intuitively the INNER JOIN should be faster than the LEFT JOIN it isn't. The explanation is here: (stackoverflow)
If you just find a brief explanation here:
Why an inner join should be faster than a left join
One reason is in the different ways that they are implemented: INNER JOINS use hash matching which has a O(1) complexity vs LEFT JOINS which use nested loops O(n). The other reason is that a LEFT JOIN would generally have to deal with more columns since it is more expansive.
There are two cases when a left join would be faster
Small number of entries
Since INNER JOINS use hash matching, small sets would cause the overhead of a hash match the most expensive part of the query - this allows LEFT JOINS to work faster in this context.
When the join columns are the PK of the outer table
and none of the outer table columns are referenced outside of the outer join itself. This would allow the query planner to drop the outer joined table in case of a LEFT JOIN whereas an INNER JOIN in this case would test each row to join the two tables.
This is a very broad overview of the two most popular answers on this thread. I guess if you just want an idea or want to read the full thing this can give you an overview.
1076 project employees 2
select project_id
from project
group by project_id
having count(employee_id) = (select count(employee_id)
from project
group by project_id
order by count(employee_id) desc
limit 1);with project_cnt as(
select project_id
,count(distinct employee_id) as cnt
from project
group by 1
),project_rnk as(
select project_id
,cnt
,dense_rank() over(order by cnt desc) as rnk
from project_cnt
)
select project_id
from project_rnk
where rnk=1
# --------------------------------------------------------------------
# all project... 만약 인원이 가장 많은 플젝이 2개 이상이면?
# with project_cnt as(
# select project_id
# ,count(employee_id) over(partition by project_id) as cnt
# from project
# ), project_num as(
# select project_id
# ,cnt
# ,dense_rank() over(order by cnt desc) as num
# from project_cnt
# order by cnt desc
# )
# select distinct project_id
# from project_num
# where num =11082
[조건]
- 총 판매가별로 베스트 셀러를 출력하라
[생각의 흐름..]
- 해당 문제에서는 product 테이블은 사용이 필요 없을 것 같음.
- 반복되는 행이 있다 명시했기 때문에 seller_id를 기준으로 하여 group by를 진행 함
- quantity는 고려하지 않고, price를 기준으로 best_seller이 결정되는 것이기 때문에 sum(price) as sum_price 를 진행해서 max(sum_price)를 도출해야 함.
- 만약 quantity를 고려해야하는 상황이라면???
# 기존 order by desc, limit 1로 뽑아내기
select seller_id
from sales
group by seller_id
having sum(price) = (select sum(price)
from sales
group by seller_id
order by 1 desc
limit 1)with 절을 활용하여 max(sum_price) 도출하기
with df as (select sum(price) as sum_price
from sales
group by seller_id)
select seller_id
from df
where sum_price = (select max(sum_price)
from df)rank 함수를 이용해서 max_price 뽑아내기
select seller_id
from ( select seller_id
,rank() over(order by sum(price) desc) as rnk_price
from sales
group by seller_id) temp
where rnk_price = 1;