T-SQL Interview Questions for Senior Developers

By:   |   Updated: 2016-11-02   |   Comments (12)   |   Related: More > Professional Development Interview Questions Developer


Problem

We are going to hire a new SQL Server developer and we are looking for people with strong T-SQL skills to handle many tough business challenges. What are good questions to test upcoming candidates for their T-SQL knowledge and skills?

Solution

We can Google "sql server developer interview questions" and there are not many advanced coding questions. So in this tip, I have collected a few questions that may be a good start for testing a candidate's capability in T-SQL.

I have two criteria for choosing potential questions:

  1. Questions should be concise and easy to understand and not cause confusion for candidates. I personally dislike questions longer than 3 lines.
  2. Questions should have multiple ways to solve, such as that you can solve it differently with T-SQL based on SQL Server version.

There can be more than one solution to each question, the priority is on the end result. Of course, performance of the solution is important, but that's not the focus of this tip and performance-related questions can be good topics for face-to-face discussions with candidates in areas such as index choice, execution plans, code optimization, etc.

Question 1

Find the Nth row based on a column value. For example, we have a student score table, we want to find students with the 5th highest score.

The result should be: student_id 10 and 7 that have the 5th highest score.

-- 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 the Nth row based on a column value.

Solution to Question 1 

-- 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 a value that shows at least [N] times consecutively in a table.

The result should be: number 1 which appears 3 times consecutively (for id = 3, 4, 5).

-- 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 a value that shows at least [N] times consecutively in a table.

Solution to Question 2

-- 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

The result will be: 

result will be

Question 3

Find the first Wednesday of each month in 2016.

The result should be: "2016-01-06" is the first Wednesday of Jan, "2016-02-03" is first Wednesday of February, etc.

Solution to Question 3

The key here is that we first dynamically create a "table" for the whole calendar year of 2016 and then query against this calendar table to find the first Wednesday for each month.

-- 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))

Question 4

Calculate the running total of a column.

Assume we have the following table for monthly sales.

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

If we query this, we get the following data.

Calculate the running total of a column.

The result should be: we want a query to return the following result with an additional column [Total_Sales], which is accumulated sales up to that month.

The results for calculating the running total of a column.

Solution to Question 4

-- 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;

After running the query, we will get the expected result. 

Calculate the running total of a column after running the query.

Question 5

Group data dynamically based on time window length.

Let's first prepare the data.

-- 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

The result should be: like this for @N=15:

Group data dynamically based on time window length of 15.

If we change @N=20, the result would be like this:

Group data dynamically based on time window length of 20.

Solution to Question 5

-- 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

Summary

Good interview questions are always critical for finding capable candidates. In this tip, we only deal with regular T-SQL, there are other areas such as XQuery for XML, MDX or DAX for Analysis Services, and even C# for SSIS scripting tasks.

In this tip, the solutions are not particularly performance oriented, but more result oriented thus we are not thinking about indexes for better performance.

We used a few advanced T-SQL techniques such as recursive CTEs, window function, sub-queries and cross joins, etc. to solve the various questions which are either from my previous work or based on questions from various forums.

If a candidate can skillfully use multiple methods to solve a problem based on SQL Server BOL only (Google not allowed), it can be safe to bet the candidate is competent.

Next Steps

You may take a look at some other good tips about interview questions here:



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeffrey Yao Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2016-11-02

Comments For This Article




Wednesday, July 24, 2019 - 2:44:27 AM - Santosh Back To Top (81848)

Question 1 solution 2 has a mistake. It should be dense_rank() instead of rank().

declare @N int = 6;
 ; with c as (
 select rnk=dense_rank() over (order by score desc), *
 from dbo.MathScore
 )
 select rnk, student_id, score
 from c
 where rnk = @N;

Monday, October 2, 2017 - 2:03:44 AM - jeff_yao Back To Top (66785)

 @anth, num is not needed in the group by clause. Can you elaborate why num column is needed?

Sunday, October 1, 2017 - 5:20:19 AM - anth Back To Top (66764)

I think Solution 2 for Question 2 is incorrect. There is missing num in group by clause

It should be:

; 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 num, (id - rnk)
having count(*)  >= @N)
select t.* from dbo.t 
inner join c2
on t.id between c2.min_id and c2.max_id;

Friday, November 11, 2016 - 5:46:24 PM - jeff_yao Back To Top (43750)

Hi Brian, for solution 1 to Q2, c.id = t.id-1 is to find NEXT record in id sequence (if exists) whose  [num] = c.num.

Recursive CTE (or anyrecursive technique, like recursive stored procedure) is a little bit difficult to understand directly or easily, but it is very concise in terms of coding when solving some issues.

 

Friday, November 11, 2016 - 4:37:06 PM - Brian Berg Back To Top (43749)

 Hi Jeff,  Thank you for the insightful article.  I was wondering if you could break down Question 2 solution logic a little more.  For instance I see how it works but I am curious how you knew that you needed to join on c.id =t.id - 1?  I don't consider myself a senior developer so I wanted to understand more the process of breaking this problem down further to understand the solution.  

 

Thursday, November 3, 2016 - 1:05:00 PM - jeff_yao Back To Top (43694)

@Natalia, thank you for finding the bug for me, I purposely leave it there just to see whether you read and test the script. :-) (I am kidding)

Many thanks again, @Natalia.

 

Wednesday, November 2, 2016 - 3:28:24 PM - Natalia Back To Top (43686)

 Question 1 solution 1 has a mistake. Correct sign should be LESS THAN EQUAL 

 

declare @N int = 1;

select * from dbo.MathScore m1

where (select count(distinct score) from dbo.MathScore m2 where m1.Score <= m2.Score) = @N;

go

 

 

 

Wednesday, November 2, 2016 - 2:28:30 PM - jeff_yao Back To Top (43685)

Thanks for all the good comments.

@Thomas Franz, your solution to Q2 is not flexible to me because if I want @N=10, then you need to change your query, right?

@Curran Davis, you catch me, and you are right. Your comment will help other readers to see that I am a fool of myself (sometimes) :-) Thx.

@ThomAce, your solution works fine to me, another great one.

@Joe, what Qs you want to ask is totally up to you based on your business requirements. But I do know all big companies, such as FLAG (Facebook, LinkedIn, Amazon, Google), ask algorithm coding questions for their developer candidates.

 

Wednesday, November 2, 2016 - 12:51:07 PM - Joe Back To Top (43684)

These really aren't the type of questions I'd be asking at all. They're great if you want to know if someone can figure out how to do obscure and uncommon tasks in T-SQL, but most of development work is commonplace selects and updates. I'd be asking about how to determine which columns to index, how to normalize data, how to create and read traces and query plans, that sort of thing. Apps don't go off the rails because people can't find the first Wednesday in March, they go off the rails because developers build schemas that create data problems and they create queries that run all day.

Wednesday, November 2, 2016 - 11:15:06 AM - ThomAce Back To Top (43683)

 Hi,

 

It's an amazing post indeed. It makes me inspired and learning a lot.

However, you can do the first (because I had no more time for playing) quest with either this way:

;WITH BlaBla AS
(
    SELECT
        TOP 5
        score
    FROM
        dbo.MathScore
    GROUP BY
        score
    ORDER BY
        score
    DESC       
)
SELECT student_id, score FROM dbo.MathScore WHERE score = (SELECT TOP 1 score FROM BlaBla ORDER BY score ASC)

 

What is your opinion? I know, it's not so elegant, but works.

Wednesday, November 2, 2016 - 8:09:40 AM - Curran Davis Back To Top (43681)

 

 Hello,

Thanks for this helpful article. I wanted to point out an error in your first example.

The first solution will only find the student with 4 scores higher than theirs, not necessarily the fifth highest score, so for example if the first 4 students had scored 100 on the test, the output would be student 6 with a score of 98, which is actually the second highest score. The other two solutions should work as expected.

 

regards,

C. Davis.

 

Wednesday, November 2, 2016 - 4:25:48 AM - Thomas Franz Back To Top (43675)

nice questions.

I have some other solutions for Q2 and Q3:

Q2 (Find a value that shows at least [N] times consecutively in a table):

WITH c AS
   (SELECT id,
           num1 = num,
           num2 = LAG(num, 1) OVER (ORDER BY id),
           num3 = LAG(num, 2) OVER (ORDER BY id)
     from dbo.t
   )
SELECT *
  FROM c
 WHERE num1 = c.num2
   AND num1 = c.num3
;

Q3 (first wednesday in every month of 2016):

WITH c AS (SELECT mon, DATEFROMPARTS(2016, t.mon, 1) first_day, DATEPART(WEEKDAY,DATEFROMPARTS(2016, t.mon, 1)) weekday
             FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) t(mon)
          )
SELECT CASE WHEN c.weekday = 3 THEN c.first_day
            WHEN c.weekday > 3 THEN DATEADD(DAY, 10 - DATEPART(WEEKDAY, c.first_day), first_day)
            ELSE                    DATEADD(DAY,  3 - DATEPART(WEEKDAY, c.first_day), first_day)
       END first_wednesday_in_month
  FROM c
 ORDER BY c.mon














get free sql tips
agree to terms