T-SQL Interview Questions for Senior Developers

-- question 1: find nth largest record
use tempdb
 if object_id('dbo.MathScore', 'U') is not null
 drop table dbo.MathScore
create table dbo.MathScore (student_id int primary key, score int);
go
-- populate the table
insert into dbo.MathScore (student_id, score)
values (1, 80), (2, 78), (3, 85), (4, 91), (5, 69), (6, 98), (7, 82), (8, 76), (9, 90), (10, 82)
go
-- check result
select * from dbo.MathScore
order by Score desc

-- find student_id with 5th highest score
-- solution 1
declare @N int = 5;
select * from dbo.MathScore m1
where (select count(distinct score) from dbo.MathScore m2 where m1.Score >= m2.Score) = @N;
go
-- solution 2, using Window function and is applicable to sql server 2005+ 
declare @N int = 5;
 ; with c as (
 select rnk=rank() over (order by score desc), *
 from dbo.MathScore
 )
 select student_id, score
 from c
 where rnk = @N;
 go
 -- solution 3
 declare @N int = 5;
 select  * from dbo.MathScore 
 where score in (select distinct top (@N) score from dbo.MathScore order by score desc 
     except 
     select distinct top (@N-1) score from dbo.MathScore order by score desc 
     )
 order by score desc
 go

-- question 2: find records that appear consecutively at least [N] times with same column value
use tempdb
if object_id('dbo.t', 'U') is not null
 drop table dbo.t;
create table dbo.t (id int identity primary key, num int);
go
insert into dbo.t (num)
values (1), (2), (1), (1), (1), (2), (2), (1);
go
-- check value
select * from dbo.t

-- find the all numbers that appear at least N times consecutively.
-- solution 1: using recursive cte
declare @N int =3;
; with c as (
select id, num, lvl=0
from dbo.t 
union all
select t.id, t.num, lvl=c.lvl+1
from dbo.t
inner join c
on c.id = t.id -1
and c.num = t.num
)
select distinct t.* from c 
right join dbo.t 
on t.id between (c.id-@N+1) and c.id
where c.lvl =@N-1
order by num, id;

go
-- solution 2, using window function
declare @N int = 3;
; with c as (select *, rnk=row_number() over (order by num) from dbo.t)
, c2 as (select min_id=min(id), max_id=max(id) 
from c
group by (id - rnk)
having count(*)  >= @N)
select t.* from dbo.t 
inner join c2
on t.id between c2.min_id and c2.max_id;
go

-- find the 1st Wed of each month in 2016
-- solution 1
-- ref to language independend manner of datepart() function at http://sqlmag.com/t-sql/datetime-calculations-part-2
; with LVL0 as (select 1 as c  union all select 1 as c union all select 1 union all select 1 union all select 1 )
, LVL1 as (select 1 as c from LVL0 cross join LVL0 L0)
, LVL2 as (select 1 as c from LVL1 cross join LVL1 L1)
, calendar as (select rn = ROW_NUMBER() over (order by c) from LVL2)
, wkday as ( select dt=dateadd(day, rn-1, '20160101') from calendar
    where datepart(weekday, dateadd(day, rn-1, '20160101')+@@datefirst-1) = 3 -- language independend for datepart(), 3 means Wed
    and datepart(year,  dateadd(day, rn-1, '20160101')) = 2016)
, mthday as (select dt, rnk = rank() over (partition by datepart(month, dt) order by dt asc ) from wkday)
select dt from mthday where rnk =1
-- solution 2
; with c as (
select dt =dateadd(day, n-1, '20160101') from   
(select top (366) n=row_number() over (order by (select 1)) from master..spt_values cross join master..spt_values v2) T(n)
where datepart(weekday, dateadd(day, n-1, '20160101')+@@datefirst-1) = 3  -- language independend for datepart(), 3 means Wed
and dateadd(day, n-1, '20160101') <= '20161231'
)
select min(dt)
from c 
group by (datepart(month, dt))

use tempdb
if object_id('dbo.t', 'U') is not null
 drop table dbo.t;
create table dbo.t (Mth int, Sales int);
go
insert into dbo.t (Mth, Sales)
values (1, 10), (2, 20), (3, 30), (4, 40), (5, 50), (6, 60)
go 
-- check value
select * from dbo.t

-- solution 1, applicable to sql server 2005+, with CROSS APPLY
SELECT t1.Mth, t1.Sales, Total_Sales = YTD
from dbo.t t1
cross apply (select sum(sales) from dbo.t where t.Mth <=t1.Mth) M(YTD)
-- solution 2, applicable to sql server 2012+, with Window function
select Mth, Sales, sum(Sales) over (order by Mth asc Rows unbounded preceding) as Total_Sales
from dbo.t;
-- solution 3, almost the same as solution 1, with subquery, but can be used for sql server 2000+
SELECT t1.Mth, t1.Sales, Total_Sales = (select sum(Sales) from dbo.t where t.Mth <= t1.Mth)
from dbo.t t1
-- solution 4, with a CTE
; with c as (SELECT t1.Mth,  Total_Sales = sum(t2.sales)
from dbo.t t1
inner join dbo.t t2
on t2.Mth <= t1.Mth
group by t1.Mth
)
select t.*, c.Total_Sales
from dbo.t 
inner join c
on t.Mth = c.Mth;

-- Question 5. Group data dynamically via time window
use tempdb
if object_id('dbo.t', 'U') is not null
 drop table dbo.t;
create table dbo.t (id int identity primary key, num int, logdate datetime);
go
-- populate the table with 60 min data
declare @i int =0;
set nocount on;
declare @dt datetime=getdate()
begin tran
 while (@i < 60)
 begin
  insert into dbo.t (num, logdate)
  select @i+1, dateadd(minute, @i, getdate())
  set @i = @i + 1;
 end
commit tran
-- check table
select * from dbo.t;
go

-- find the avg, max and min, sum of column [num] for every [N] min
-- solution 1, need a second statement to find the min(logdate)
declare @N int= 15;
declare @dt datetime;
select @dt = min(logdate) from dbo.t; -- we have to use
select [min]=min(num), [max]=max(num), cnt=count(*), [avg]=avg(num), [sum]=sum(num), start_time=min(logdate), end_time=max(logdate)
from dbo.t
group by datediff(minute, @dt, logdate)/@N;
go
-- solution 2, one sql statement with use of CTE
declare @N int = 15 -- every 15 min
; with min_dt as (select mindt = min(logdate) from dbo.t)
, c as (
select num, logdate, window_num = datediff(minute, mindt, logdate) / @N
from dbo.t
cross apply min_dt 
)
select [min]=min(num), [max]=max(num), cnt=count(*), [avg]=avg(num), [sum]=sum(num), start_time=min(logdate), end_time=max(logdate)
from c
group by window_num
order by window_num asc;
go
-- solution 2 -- variant 1 (group by difference)
declare @N int = 15 -- every 15 min
; with min_dt as (select mindt = min(logdate) from dbo.t)
, c as (
select num, logdate, mindt
from dbo.t
cross apply min_dt 
)
select [min]=min(num), [max]=max(num), cnt=count(*), [avg]=avg(num), [sum]=sum(num), start_time=min(logdate), end_time=max(logdate)
from c
group by datediff(minute, mindt, logdate)/@N;
go

-- solution 2 -- variant 2 (removing cross apply but use a subquery to find the min(logdate) 
declare @N int = 15 -- every 10 min
;WITH c as (
select num, logdate, mindt=(select mindt = min(logdate) from dbo.t)
from dbo.t
)
select [min]=min(num), [max]=max(num), cnt=count(*), [avg]=avg(num), [sum]=sum(num), start_time=min(logdate), end_time=max(logdate)
from c
group by datediff(minute, mindt, logdate)/@N;
go