Interview SQL Problems

 

How to find nth highest salary in sql

 with HighestSalary AS
 (
 select id,[name],salary,
 dense_rank() over( order by salary desc) as Salary_Rank
 from employee
 )
 /* 5th highest salary */
 select top 1 HighestSalary.id, HighestSalary.[name],HighestSalary.salary from  HighestSalary where HighestSalary.Salary_Rank=5

SQL query to get organization hierarchy

Declare @ID int ;
Set @ID = 5;
with OrganizationHierarchy AS
 (

 select e.id,e.[name],e.managerId
 from employee e
 where e.id = @ID
union all

select e.id,e.[name],e.managerId
from employee e
join  OrganizationHierarchy h
 on e.id =h.managerId
 )
 select e.id,e.[name],m.[name] as Manager from OrganizationHierarchy e
   join OrganizationHierarchy m
   on e.managerId = m.id

Delete duplicate rows in sql

 with DuplicatesEmp As
 (
 select *,
 ROW_NUMBER() over(partition by id order by id) rank_number
 from DuplicateEmployees
 )
delete  from DuplicatesEmp where rank_number>1

Get Customers latest order

 WITH LatestOrders AS (
    SELECT 
        OrderId,
        CustomerId,
        OrderDate,
        ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate DESC) AS rn
    FROM 
        Orders
)
SELECT 
    o.OrderId,
    c.CustomerName,
    o.OrderDate
FROM 
    LatestOrders o
    inner join Customers c
    on c.CustomerId =o.CustomerId
WHERE 
    rn = 1;

SQL query to find employees hired in last n months

Select *
FROM Employees
Where DATEDIFF(MONTH, HireDate, GETDATE()) Between 1 and N

SQL Query to find department with highest number of employees

  SELECT d.departmentName, COUNT(*) AS EmployeeCount
FROM department d
JOIN employee e
ON e.departmentId = d.departmentId
GROUP BY d.departmentName;

Return an employee bonus such that the next employee's bonus is equal to the sum of all previous employees' bonuses.

SELECT 
    Id,
    [Name],
    Bonus,
    SUM(Bonus) OVER (ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS PreviousBonusSum
FROM 
    Employees
ORDER BY 
    Id;

Display highest and lowest salary

select * 
, max(salary) over(partition by dept order by salary desc) as highest_sal
, min(salary) over(partition by dept order by salary desc
                  range between unbounded preceding and unbounded following) as lowest_sal
from employee;

Each team plays with every other team TWICE

WITH matches AS
    (SELECT row_number() over(order by team_name) AS id, t.*
     FROM teams t)
SELECT team.team_name AS team, opponent.team_name AS opponent
FROM matches team
JOIN matches opponent ON team.id <> opponent.id
ORDER BY team;

To be continued...