*HARD* Walmart SQL Interview Question (StrataScratch 9900: Median Salary)

Solution and walkthrough of a real SQL interview question for Data Scientist and Data Analyst technical coding interviews. This question was asked by Walmart and is called "Median Salary". Feel free to watch at higher speeds!
Try this question on StrataScratch: platform.stratascratch.com/co...
Find me on LinkedIn: / frederikmueller
0:00 Intro/problem statement
3:32 Easy approach
8:22 Hard approach
18:44 Discussion/outro
Playlists:
• StrataScratch Coding Q...
• LeetCode Database Prob...
• LeetCode Easy Database...
• LeetCode Medium Databa...
• LeetCode Hard Database...

[@Hsalz]

Using case in the where clause was slightly confusing but eventually made sense. Could also just do WHERE r IN ((c+1)/2, (c+2)/2). Interesting problem, thanks for sharing!

[@frederikmuller]

interesting solution, thanks for sharing

[@kinjalshah498]

select department, avg(salary) as median
from
(select
department,salary,
count(*) over (partition by department) as ct,
row_number() over (partition by department order by salary) as rn
from employee) a
where rn between ct/2 and ct/2+1
group by department

[@Harsha-D311]

Why would you go by 2 methods
Just asking
Do interviewers care about the approach

[@frederikmuller]

You can showcase your breadth of knowledge and impress the interviewer. Makes you stand out from the competition. Some interviewers might not care if they just tick off a solution as correct or not, though.