By: Jeffrey Yao | 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:
- Questions should be concise and easy to understand and not cause confusion for candidates. I personally dislike questions longer than 3 lines.
- 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
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
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:
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.
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.
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.
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:
If we change @N=20, the result would be like this:
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 Developer T-SQL Interview Questions
- SQL Server Developer T-SQL Functions Interview Questions
- Interview Questions for Hiring PowerShell Database Developers
- Junior SQL Server Developer Interview Questions
- If you have good interview questions which you have used, especially XQuery, MDX/DAX or R, please submit to MSSSQLTips.com to share with the community
- If you have better solutions for the questions in this tip, please put them in the comment section below.
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips