SQL Complex Queries , Query Optimization and Interview Questions SQL Server 2016

Script used:

--Over operator

SELECT row_number() over(ORDER BY prod.productid) rownum, cat.CategoryName ,
prod.ProductName
FROM Categories cat
INNER JOIN products prod ON cat.CategoryID=prod.CategoryID

SELECT ROW_NUMBER() over(PARTITION BY cat.categoryid ORDER BY prod.productid) ProductWiseRowNum,
row_number() over(ORDER BY (SELECT 1)) rownum, cat.CategoryName,prod.ProductName
FROM Categories cat
INNER JOIN products prod ON cat.CategoryID=prod.CategoryID

SELECT dense_rank() over(ORDER BY cat.categoryid) CategoryWiseRowNum,
cat.CategoryName,
ROW_NUMBER() over(PARTITION BY cat.categoryid
ORDER BY prod.productid) ProductWiseRowNum,
prod.ProductName
FROM Categories cat
INNER JOIN products prod ON cat.CategoryID=prod.CategoryID

SELECT prod.ProductName,
sum(ord.Quantity*ord.UnitPrice) [TotalAmout]
FROM [Order Details] ord
INNER JOIN Products prod ON ord.ProductID=prod.ProductID
GROUP BY prod.ProductName

--Running totals - Aggregation with Over Operator - 2012

SELECT productname,
TotalAmout,
sum([TotalAmout]) OVER(ORDER BY productname) AS RunningTotal
FROM
(SELECT prod.ProductName,
sum(ord.Quantity*ord.UnitPrice) [TotalAmout]
FROM [Order Details] ord
INNER JOIN Products prod ON ord.ProductID=prod.ProductID
GROUP BY prod.ProductName) t

SELECT cat.CategoryName,
productname,
TotalAmout,
sum([TotalAmout]) over(PARTITION BY cat.categoryid
ORDER BY productname) AS RunningTotal
FROM
(SELECT prod.CategoryID,
prod.ProductName,
sum(ord.Quantity*ord.UnitPrice) [TotalAmout]
FROM [Order Details] ord
INNER JOIN Products prod ON ord.ProductID=prod.ProductID
GROUP BY prod.ProductName,
prod.CategoryID) t
INNER JOIN Categories cat ON t.CategoryID=cat.CategoryID

--Lead and Lag
SELECT cust.CustomerID,
cust.CompanyName,
ord.OrderDate,
LEAD(ord.OrderDate) over(PARTITION BY cust.companyname
ORDER BY ord.orderdate) [NextOrderDate],
LAG(ord.OrderDate) over(PARTITION BY cust.companyname
ORDER BY ord.orderdate) [PreviousOrderDate]
FROM orders ord
INNER JOIN Customers cust ON ord.CustomerID=cust.CustomerID

--Paging
DECLARE @PageNumber int=3 DECLARE @PageSize int=10 DECLARE @StartPage int,@EndPage int
SET @StartPage=@PageNumber*@PageSize-@PageSize+1
SET @EndPage=@PageNumber*@PageSize

SELECT *
FROM
(SELECT ROW_NUMBER() Over(ORDER BY ord.orderid) AS RowNum,*
FROM orders ord) t
WHERE t.RowNum BETWEEN @StartPage AND @EndPage

SELECT @StartPage,@EndPage

DECLARE @PageNumber int=3 DECLARE @PageSize int=10 DECLARE @StartPage int,@EndPage int
SET @StartPage=@PageNumber*@PageSize-@PageSize+1
SET @EndPage=@PageNumber*@PageSize

SELECT *
FROM
(SELECT ROW_NUMBER() Over(ORDER BY(SELECT 1)) AS RowNum,*
FROM orders ord) t
WHERE t.RowNum BETWEEN @StartPage AND @EndPage

SELECT *
FROM orders ord
ORDER BY ord.orderid
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY

DECLARE @PageNumber int=3 DECLARE @PageSize int=10 DECLARE @StartPage int,@EndPage int
SET @StartPage=@PageNumber*@PageSize-@PageSize+1
SET @EndPage=@PageNumber*@PageSize
SELECT *
FROM orders ord
ORDER BY ord.orderid
OFFSET @PageNumber*@PageSize-@PageSize ROWS
FETCH NEXT @PageNumber*@PageSize ROWS ONLY

--First_value and Last_Value
SELECT cust.CustomerID,
cust.CompanyName,
ord.OrderDate,
First_Value(ord.OrderDate) over(PARTITION BY cust.companyname
ORDER BY (select 1)) [FirstOrderDate],
Last_Value(ord.OrderDate) over(PARTITION BY cust.companyname
ORDER BY (select 1)) [LastOrderDate]
FROM orders ord
INNER JOIN Customers cust ON ord.CustomerID=cust.CustomerID

WITH CTE AS
(SELECT cust.CustomerID,
cust.CompanyName,
ord.OrderDate,
LEAD(ord.OrderDate) over(PARTITION BY cust.companyname
ORDER BY ord.orderdate) [NextOrderDate],
LAG(ord.OrderDate) over(PARTITION BY cust.companyname
ORDER BY ord.orderdate) [PreviousOrderDate]
FROM orders ord
INNER JOIN Customers cust ON ord.CustomerID=cust.CustomerID)
,
CTE2
AS(
SELECT CustomerID,CompanyName,AVG(NextDateDifference) [AvgDiff]
,IIF(AVG(NextDateDifference)<30,'1',IIF(AVG(NextDateDifference)>=30 and AVG(NextDateDifference)<90,'2',
IIF(AVG(NextDateDifference)>=90,'3','4'))) AS [Tag]
FROM (SELECT CustomerID,
CompanyName,
ISNULL(DATEDIFF(dd,OrderDate,isnull(NextOrderDate,OrderDate)),0) [NextDateDifference],
ISNULL(DATEDIFF(dd,isnull(PreviousOrderDate,OrderDate),OrderDate),0) [PreviousDateDifference],
OrderDate,
NextOrderDate,
PreviousOrderDate FROM CTE) t
GROUP BY CustomerID,CompanyName
)
SELECT customerid,CompanyName,AvgDiff,
CHOOSE([Tag],'Important','Recommended','Normal','Ignore') [Flag]
from CTE2

select iif(1=2,'a','b')
select choose(3,'hello','friend','ghost')
declare @var1 varchar(10),@var2 varchar(10)
set @var1='Best'
set @var2=null
select @var1 + ' ' + @var2,concat(@var1,' ',@var2)

--Complex Match Join
SELECT * INTO #CITY FROM
(
SELECT 1 AS ID,'ind' CITY
UNION ALL
SELECT 2 AS ID,'aus' CITY
UNION ALL
SELECT 3 AS ID,'sri' CITY
UNION ALL
SELECT 4 AS ID,'Eng' CITY
)VW

select * from #CITY

SELECT c.id fromid,c.city fromcity,c1.id toid,c1.city tocity
INTO #team
FROM #CITY c
INNER JOIN #CITY c1 ON c.id <> c1.id

select * from #team

SELECT c.* FROM #team c
INNER JOIN #team c1 ON c.fromid = c1.toid AND c.toid = c1.fromid AND c1.fromid <=c1.toid
ORDER BY c.tocity

drop table #CITY
drop table #team

--Second Highest Salary
declare @n int;
set @n=2;
Select e1.*
from Employee e1 where (@n-1)=(Select COUNT(distinct salary) from Employee e2 where e2.Salary>e1.Salary);

--Second Highest Salary Department Wise
;WITH DepartmentWiseSalary AS
(
   SELECT   *,DENSE_RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS RowNum
   FROM   Employee
)
SELECT   *
FROM   DepartmentWiseSalary
WHERE   RowNum = 2;

--Getting All Level Child
;WITH cteEmployee (EmpId, FirstName, LastName, ManagerID, Level)
AS
(
SELECT   EmpId, FirstName, LastName, ManagerID, 0 AS Level
FROM   Employee
WHERE   ManagerID = 2

UNION ALL

SELECT   e.EmpId, e.FirstName, e.LastName, e.ManagerID, Level + 1
FROM   Employee e
           INNER JOIN cteEmployee AS d ON e.ManagerID = d.EmpId
)
SELECT   EmpId, FirstName, LastName, ManagerID,Level
FROM   cteEmployee;

--Finding Duplicates Record
WITH CTE
AS
(
SELECT ROW_NUMBER() OVER(ORDER BY (Select 1)) [RowNum],* FROM EMPLOYEE
)
SELECT * FROM CTE c1
WHERE
1<(SELECT count(c2.RowNum) FROM CTE c2 WHERE c1.EmpId=c2.EmpId)

--PIVOT Count of employee joining as per YEAR
SELECT   *
FROM
       (
       SELECT   Department,
                   YEAR(DOJ) AS [Year],
                   COUNT(EmpId) AS [EmployeeCount]
       FROM       Employee
       GROUP BY   Department, YEAR(DOJ)
       ) TT
       PIVOT
       (
           SUM([EmployeeCount])
           FOR [Year] IN ([2006],[2007],[2008],[2009],[2010],[2011])
       ) PT

--Count SPACE
DECLARE    @strName VARCHAR(1000)
SET       @strName = ' White Space is where the world and all distraction falls away '
PRINT       (LEN(@strName) - LEN(REPLACE(@strName, ' ', '')))
PRINT       (DATALENGTH(@strName) - DATALENGTH(REPLACE(@strName, ' ', '')))

--Optimize Below Query
SELECT       *
FROM       Student AS S
WHERE       DOB IN
           (
               SELECT       MAX(DOB)
               FROM       Student sp
               WHERE       YEAR(S.DOB) = YEAR(sp.DOB)
               GROUP BY   YEAR(sp.DOB)
           )
ORDER BY   DOB

--Optimized Query
WITH CTE
AS
(
SELECT       YEAR(DOB) [Year],max(DOB) [DOB]
               FROM       Student sp
               GROUP BY YEAR(DOB)
)
SELECT       *
FROM       Student AS S
join CTE ON       s.DOB =CTE.DOB

Download Backup and Script