T-SQL Query | [ The Grouping Puzzle ]

06 Wednesday May 2015

Posted by Pawan Kumar Khowal in SQL SERVER, T SQL Puzzles

Tags

Complex SQL Challenges, complex sql statement(puzzle), Complex TSQL Challenge, Interesting Interview Questions, Interview Qs.SQL SERVER Questions, Interview questions on Joins, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Khowal, Learn complex SQL, Learn SQL, Learn T-SQL, Objective Puzzle, Pawan, Pawan Khowal, Pawan Kumar, Pawan Kumar Khowal, PL/SQL Challenges, puzzle sql developer, Puzzles, Queries for SQL Interview, SELECT Puzzle, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Interview Questions, SQL Joins, SQL pl/sql puzzles, SQL Puzzles, SQL Queries, SQL Quiz, SQL Server Database, SQL SERVER Interview questions, SQL Skills, SQL Sudoku, SQL Top clause, SQL Trikcy question, sql/database interview for puzzle sql developer, SQLSERVER, T SQL Puzzles, T-SQL Challenge, TOP Clause, Tough SQL Challenges, Tough SQL Puzzles, Tricky Questions, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries

T-SQL Query | [ The Grouping Puzzle ]

Puzzle Statement

The Puzzle is self explanatory. Please check out the sample input and expected output for details.

Sample Input

day	leva	levb
18	A	B
19	A	B
20	B	A
21	A	B
22	A	B
23	A	B

Expected Output

FROM	TO	Leva	Levb
18	19	A	B
20	20	B	A
21	23	A	B

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.

—


declare @tbl as table(day int,leva varchar(40),levb varchar(40))
insert into @tbl
select 18 ,'A' ,'B' union all
select 19, 'A', 'B' union all
select 20, 'B', 'A' union all
select 21, 'A', 'B' union all
select 22 ,'A' ,'B' union all
select 23, 'A', 'B'

—

Update May 6 | Solution 1


--

SELECT MIN(day) [FROM] , MAX(day) [TO] , MIN(Leva) Leva , MAX(Levb) Levb
FROM
(
     SELECT * , DAY - ROW_NUMBER() OVER (PARTITION BY leva,levb ORDER BY %%Physloc%%) rnk FROM @tbl
) A
GROUP BY rnk
ORDER BY [FROM]

--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

Http://MSBISkills.com

Pawan Kumar Khowal

T-SQL Query | [ The ZIP Grouping Puzzle ]

06 Wednesday May 2015

Posted by Pawan Kumar Khowal in SQL SERVER, T SQL Puzzles

≈ Leave a comment

Tags

Complex SQL Challenges, Complex TSQL Challenge, Interesting Interview Questions, Interview Qs.SQL SERVER Questions, Interview questions on Joins, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Khowal, Learn complex SQL, Learn SQL, Learn T-SQL, Objective Puzzle, Pawan, Pawan Khowal, Pawan Kumar, Pawan Kumar Khowal, PL/SQL Challenges, puzzle sql developer, Puzzles, Queries for SQL Interview, SELECT Puzzle, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Interview Questions, SQL Joins, SQL pl/sql puzzles, SQL Puzzles, SQL Puzzles wanted, SQL Queries, SQL Quiz, SQL Server Database, SQL SERVER Interview questions, SQL Skills, SQL Sudoku, SQL Top clause, SQL Trikcy question, sql/database interview for puzzle sql developer, SQLSERVER, T SQL Puzzles, T-SQL Challenge, TOP Clause, Tough SQL Challenges, Tough SQL Puzzles, Tricky Questions, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries

T-SQL Query | [ The ZIP Grouping Puzzle ]

Puzzle Statement

We have a table called zip33
We have group data based on code and then Zip Start & Zip End if Zip Start < Zip End
Please check out the sample input and expected output for details.

Sample Input

zip	code
56790000	A101
56800000	A101
56810000	A101
56820000	A101
56890000	A101
56900000	A102
56901111	A102
56901168	A103
56910000	A102
56920000	A102

Expected Output

Code	ZipStart	ZipEnd
A101	56790000	56890000
A102	56900000	56901111
A102	56910000	56920000
A103	56901168	56901168

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 zip33 (zip varchar(9), code varchar(4))

insert zip33 select '056790000' ,'A101'
insert zip33 select '056800000' ,'A101'
insert zip33 select '056810000' ,'A101'
insert zip33 select '056820000' ,'A101'
insert zip33 select '056890000' ,'A101'
insert zip33 select '056900000' ,'A102'
insert zip33 select '056901111' ,'A102'
insert zip33 select '056901168' ,'A103'
insert zip33 select '056910000' ,'A102'
insert zip33 select '056920000' ,'A102'

—

Update May 6 | Solution 1


--


;WITH CTE AS
(
	SELECT * , ROW_NUMBER() OVER (ORDER BY %%Physloc%%) - DENSE_RANK() OVER ( PARTITION BY code ORDER BY zip) rnk FROM zip33
)
SELECT MIN(Code) Code , MIN(zip) ZipStart , MAX(zip) ZipEnd FROM CTE 
GROUP BY rnk


--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com

T-SQL Query | [ The Consecutive Wins Puzzle – 2 ]

06 Wednesday May 2015

Posted by Pawan Kumar Khowal in SQL SERVER, T SQL Puzzles

≈ 1 Comment

Tags

Complex SQL Challenges, Complex TSQL Challenge, Interesting Interview Questions, Interview Qs.SQL SERVER Questions, Interview questions on Joins, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Khowal, Learn complex SQL, Learn SQL, Learn T-SQL, Objective Puzzle, Pawan, Pawan Khowal, Pawan Kumar, Pawan Kumar Khowal, PL/SQL Challenges, puzzle sql developer, Puzzles, Queries for SQL Interview, SELECT Puzzle, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Interview Questions, SQL Joins, SQL pl/sql puzzles, SQL Puzzles, SQL Queries, SQL Quiz, SQL Server Database, SQL SERVER Interview questions, SQL Skills, SQL Sudoku, SQL Top clause, SQL Trikcy question, sql/database interview for puzzle sql developer, SQLSERVER, T SQL Puzzles, T-SQL Challenge, TOP Clause, Tough SQL Challenges, Tough SQL Puzzles, Tricky Questions, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries

T-SQL Query | [ The Consecutive Wins Puzzle – 2 ]

Original Puzzle URL – http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=23880

Puzzle Statement

We have a table called GameResults.
Here we have to find the winning streak of a player(s) that spans multiple events/dates. For example…the data below should show that player 1000 had a 4 game winning streak, player 2000 had a 5 game winning streak and player 3000 had a 3 game winning streak.
Please check out the sample input and expected output for details.

Sample Input

TournamentId	PlayerId	GameDate	Result
1	1000	01-01-2000	W
1	1000	01-01-2000	L
1	1000	01-01-2000	W
1	1000	01-01-2000	W
1	1000	01-01-2000	L
1	2000	01-01-2000	L
1	2000	01-01-2000	W
1	2000	01-01-2000	L
1	2000	01-01-2000	L
1	2000	01-01-2000	W
2	1000	01-02-2000	W
2	1000	01-02-2000	W
2	1000	01-02-2000	W
2	1000	01-02-2000	W
2	1000	01-02-2000	L
2	2000	01-02-2000	W
2	2000	01-02-2000	W
2	2000	01-02-2000	W
2	2000	01-02-2000	W
2	2000	01-02-2000	L
3	3000	15-02-2000	L
3	3000	15-02-2000	L
3	3000	15-02-2000	W
3	3000	15-02-2000	W
3	3000	15-02-2000	W
3	2000	15-02-2000	L
3	2000	15-02-2000	L
3	2000	15-02-2000	W
3	2000	15-02-2000	W
3	2000	15-02-2000	W

Expected Output

PlayerId	ConsecutiveWins
1000	4
2000	5
3000	3

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 [dbo].[GameResults](
	[TournamentId] [int] NULL,
	[PlayerId] [int] NULL,
	[GameDate] [date] NULL,
	[Result] [varchar](5) NULL
)

INSERT INTO GameResults (TournamentId, PlayerId, GameDate,Result)
VALUES 
(1, 1000,'1/1/2000', 'W'),
(1, 1000,'1/1/2000', 'L'),
(1, 1000,'1/1/2000', 'W'),
(1, 1000,'1/1/2000', 'W'),
(1, 1000,'1/1/2000', 'L'),
(1, 2000,'1/1/2000', 'L'),
(1, 2000,'1/1/2000', 'W'),
(1, 2000,'1/1/2000', 'L'),
(1, 2000,'1/1/2000', 'L'),
(1, 2000,'1/1/2000', 'W'),
(2, 1000,'2/1/2000', 'W'),
(2, 1000,'2/1/2000', 'W'),
(2, 1000,'2/1/2000', 'W'),
(2, 1000,'2/1/2000', 'W'),
(2, 1000,'2/1/2000', 'L'),
(2, 2000,'2/1/2000', 'W'),
(2, 2000,'2/1/2000', 'W'),
(2, 2000,'2/1/2000', 'W'),
(2, 2000,'2/1/2000', 'W'),
(2, 2000,'2/1/2000', 'L'),
(3, 3000,'2/15/2000', 'L'),
(3, 3000,'2/15/2000', 'L'),
(3, 3000,'2/15/2000', 'W'),
(3, 3000,'2/15/2000', 'W'),
(3, 3000,'2/15/2000', 'W'),
(3, 2000,'2/15/2000', 'L'),
(3, 2000,'2/15/2000', 'L'),
(3, 2000,'2/15/2000', 'W'),
(3, 2000,'2/15/2000', 'W'),
(3, 2000,'2/15/2000', 'W')

--

Update May 6 | Solution 1


--


;WITH CTE1 AS
(
    SELECT * , ROW_NUMBER() OVER  (PARTITION BY PlayerId ORDER BY GameDate) rnk 
    FROM GameResults	
)
,CTE2 AS
(
    SELECT *, 
        CASE WHEN Result = lag(Result) 
        over(PARTITION BY PlayerId order by rnk) THEN 0 ELSE 1 END cols 
    FROM CTE1 c2         
)
,CTE3 AS
(
	SELECT * , SUM(cols) OVER (PARTITION BY PlayerId ORDER BY rnk) grouper FROM CTE2
)
,CTE4 AS
(
	SELECT PlayerId, grouper , COUNT(grouper) ConsecutiveWins FROM CTE3
	GROUP BY PlayerId,grouper
)
SELECT PlayerId , MAX(ConsecutiveWins) ConsecutiveWins FROM CTE4 GROUP BY PlayerId





--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com

Improving my SQL BI Skills

Daily Archives: May 6, 2015

T-SQL Query | [ The Grouping Puzzle ]

T-SQL Query | [ The ZIP Grouping Puzzle ]

T-SQL Query | [ The Consecutive Wins Puzzle – 2 ]

Share this

Share this

Share this