Tags
Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Learn complex SQL, Learn SQL, Learn T-SQL, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL pl/sql puzzles, SQL Puzzles, SQL Server 2012 Analysis Services, SQL Server Data Tools, SQL SERVER Interview questions, SQL SERVER Puzzles, SQL SERVER2005/2008, SQL Sudoku, SQLSERVER, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries
T-SQL Query | [ The Send & Ack Puzzle ]
Original puzzle link – http://beyondrelational.com/blogs/tc/archive/2009/06/04/tsql-challenge-9.aspx
The puzzle is simple. In this puzzle we were required to find the first and last IDs for consecutive rows with same values of Send and Ack states. Please check out the sample input and expected output for details.
Sample Input
| ID | CreationDate | Content | SendState | AckState |
| 1 | 24-11-2013 | Msg #1 | 0 | 0 |
| 2 | 24-11-2013 | Msg #2 | 0 | 0 |
| 3 | 24-11-2013 | Msg #3 | 1 | 1 |
| 4 | 24-11-2013 | Msg #4 | 1 | 1 |
| 5 | 24-11-2013 | Msg #5 | 1 | 1 |
| 6 | 25-11-2013 | Msg #6 | 1 | 0 |
| 7 | 25-11-2013 | Msg #7 | 1 | 0 |
| 8 | 25-11-2013 | Msg #8 | 1 | 0 |
| 9 | 25-11-2013 | Msg #9 | 1 | 0 |
| 10 | 25-11-2013 | Msg #10 | 1 | 1 |
Expected output
| ID | ID | SendState | AckState |
| 1 | 2 | 0 | 0 |
| 3 | 5 | 1 | 1 |
| 6 | 9 | 1 | 0 |
| 10 | 10 | 1 | 1 |
Rules/Restrictions
- The solution should be should use “SELECT” statement or “CTE”.
- Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com
Script
Use the below script to generate the source table and fill them up with the sample data.
-- CREATE TABLE Grouper ( ID INT IDENTITY(1,1), CreationDate DATETIME, Content NVARCHAR(10), SendState BIT, AckState BIT ) INSERT INTO Grouper (CreationDate,Content,SendState,AckState) SELECT GETDATE()-1.0,'Msg #1',0,0 UNION SELECT GETDATE()-0.9,'Msg #2',0,0 UNION SELECT GETDATE()-0.8,'Msg #3',1,1 UNION SELECT GETDATE()-0.7,'Msg #4',1,1 UNION SELECT GETDATE()-0.6,'Msg #5',1,1 UNION SELECT GETDATE()-0.5,'Msg #6',1,0 UNION SELECT GETDATE()-0.4,'Msg #7',1,0 UNION SELECT GETDATE()-0.3,'Msg #8',1,0 UNION SELECT GETDATE()-0.2,'Msg #9',1,0 UNION SELECT GETDATE()-0.1,'Msg #10',1,1 --------- |
UPDATE – 20-Apr-2015 – Solution 1
-- ;WITH CTE1 AS ( SELECT g1.*, ROW_NUMBER() OVER (ORDER BY g1.ID) rnk FROM Grouper g1 left JOIN Grouper g2 on g1.id + 1 = g2.id and g1.SendState = g2.SendState and g1.AckState = g2.AckState WHERE g2.ID IS NULL ), CTE2 AS ( SELECT g2.*,ROW_NUMBER() OVER (ORDER BY g2.ID) rnk FROM Grouper g1 RIGHT JOIN Grouper g2 on g1.id = g2.id - 1 and g1.SendState = g2.SendState and g1.AckState = g2.AckState WHERE g1.ID IS NULL ) SELECT c2.ID,c1.ID,c1.SendState,c2.AckState FROM CTE1 c1 INNER JOIN CTE2 c2 on c1.rnk = c2.rnk -- |
Add a comment if you have any other solution in mind. We all need to learn.
Keep Learning
Improving my SQL BI Skills 
;with cte
as
(
select *,ROW_NUMBER() over (partition by sendstate,ackstate order by creationdate) as rwnum from #Grouper
)
select min(id) as Id,max(id) as Id,SendState,AckState from cte
group by ID-rwnum,SendState,AckState
LikeLike
with cte as
(select
*,
IIF(
LAG(SendState) over (order by ID) = SendState
and
LAG(AckState) over (order by ID) = AckState
, 0, 1) StateGroupChange
from Grouper
),
cte_grouper as (
select
*,
SUM(StateGroupChange) over (order by ID) as StateGrouper
from cte
)
select MIN(ID) as MinId, MAX(ID) as MaxId, SendState, AckState
from cte_grouper
group by StateGrouper, SendState, AckState
LikeLiked by 2 people