175. Combine Two Tables
Question:
Solution:
select
p.firstname,
p.lastname,
a.city,
a.state
from person p
left join address a
on p.personid = a.personid
176. Second Highest Salary
Question:
Solution:
select
case
when count(*) >0 then SecondHighestSalary
else null
end as SecondHighestSalary
from(
select
distinct salary as SecondHighestSalary
from(
select
salary,
dense_rank() over(order by salary desc) as r
from employee
) s
where r = 2
) s1
Notes:
- The key is to use the dense_rank() function rather than using the rank() function. dense_rank() is able to address the situation that employees can have the same salary amount. dense_rank() will return the same rank when there is a tie between employee A and B.
- The CASE WHEN statement will return NULL when there is ONLY one employee in the dataset and output the 2nd highest salary when there are >=2 employees.
177. Nth Highest Salary
Question:
Solution:
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
select
case
when count(*) is not null then salary
else null
end as salary
from(
select
salary,
dense_rank() over(order by salary desc) r
from employee
) s
where r = N
);
END
178. Rank Scores
Question:
Solution:
select
score,
r as "Rank"
from(
select
score,
dense_rank() over(order by score desc) as r
from scores
) a
order by 2
180. Consecutive Numbers
Question:
Solution:
select
distinct Num as ConsecutiveNums
from(
select
Id,
Num,
case
when lag_num_1 = num and lag_num_2 = num then 1
else 0
end as consec_num
from(
select
Id,
Num,
lag(Num,1) over(order by Id) as lag_num_1,
lag(Num,2) over(order by Id) as lag_num_2
from logs
) l
) l2
where consec_num =1
