Day 2: Solving Amazon SQL Interview Questions | 100 Days Challenge

[@AmbarGharat]

Great Video! 4th question solution seem bit complicated. I used different approach :
-- iv. Find out the employees who earn greater than the average salary for their department.
-- Method 1:
WITH CTE AS(
SELECT
employee_id,
salary,
AVG(salary) OVER(PARTITION BY department) as avg_sal
FROM
emp_fact AS e
JOIN
department AS d
ON
d.department_id = e.department_id)
SELECT
employee_id
FROM
CTE
WHERE
salary > avg_sal;
--Method 2:
WITH AvgSalDep AS (
SELECT
e.department_id,
d.department,
ROUND(AVG(salary),2) AS Avg_sal
FROM
emp_fact AS e
JOIN
department As d
ON
d.department_id = e.department_id
GROUP BY
d.department,e.department_id)
SELECT
e.employee_id,
e.salary,
asd.Avg_sal
FROM
emp_fact AS e
JOIN
department AS d
ON
d.department_id = e.department_id
JOIN
AvgSalDep As asd
ON
asd.department_id = d.department_id
WHERE
e.Salary > asd.Avg_sal;

[@GangadharAllam-f6g]

instead of using advanced sql functions in question 2 we can solve it using this query
select *
from emp_fact
where salary = (select salary
from emp_fact
order by salary
limit 1
offset 2)

[@mohanbharadwaj7684]

is this is also right.

[@girishjala5691]

Can u please make a video on window functions & CTE

[@zero_analyst]

Sure will make one!

[@Saswat-nh1cq]

Hello sir, great job, kudos to you, just a request kindly upload the same data that you are using ,in the first question my query was correct but our o/p was not the same so i had to check the data, then i got to know that in the emp_fact table one record was missing.