SQL Puzzle | The Comma Separated Puzzle

27 Saturday Feb 2016

Posted by Pawan Kumar Khowal in SQL SERVER, SQL Server Interview Questions, T SQL Puzzles, Tricky SQL Queries

Tags

SQL Puzzle | The Comma Separated Puzzle

Puzzle Statement

I came to this puzzle while I was working on a bug. Here your have accept an input parameter which will accept comma separated values and search those values in a table.
E.g. if we are passing a,b then we should get 3 records.

Please go through the sample input and expected output for details.

Sample Input

1	a,b
2	a
3	b
4	c

Expected Output

For ‘a,b’ we should get below output.

1	a,b
2	a
3	b

Rules/RestrictionsThe solution should be should use “SELECT” statement or “CTE”.

ScriptUse below script to create table and insert sample data into it.


--

CREATE TABLE TheCommaSplitPuzzle
(
	 Id SMALLINT
	,Value VARCHAR(100)
)
GO


INSERT INTO TheCommaSplitPuzzle
VALUES
(1,'a,b'),
(2,'a'),
(3,'b'),
(4,'c')
GO

--

SOLUTION 1 | Using CharIndex

--


DECLARE @Input AS VARCHAR(100) = 'a,b'

SELECT Value FROM TheCommaSplitPuzzle
WHERE 
	CHARINDEX(Value,@input,0) > 0

--

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

SQL Puzzle | The Max First Rank Puzzle

26 Friday Feb 2016

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

≈ 1 Comment

Tags

SQL Puzzle | The First Rank Puzzle

Puzzle Statement

Well this is good puzzle. One of my friend came to me with this scenario. Here you have first find how many unique partitions you have for first date. In our example first date – 6/1/2015 have 2 rows as their hedge values are different. Now we have 2 partitions for the first case. So for all the remaining cases we should return 2 rows. In this case for 7/1/2015 we should 2 rows even if there hedge are same.

Please go through the sample input and expected output for details.

Sample Input

6/1/2015	0
6/1/2015	100
7/1/2015	100
7/1/2015	100
7/1/2015	100

Expected Output

6/1/2015	0
6/1/2015	100
7/1/2015	100
7/1/2015	100

Rules/RestrictionsThe solution should be should use “SELECT” statement or “CTE”.

ScriptUse below script to create table and insert sample data into it.


--

CREATE TABLE RnkPerfect
(
	 Dt DATETIME
	,Hedge INT
)
GO

INSERT INTO RnkPerfect
VALUES
('6/1/2015',	0),
('6/1/2015',	100),
('7/1/2015',	100),
('7/1/2015',	100)
GO

INSERT INTO RnkPerfect
VALUES
('7/1/2015',	100)
GO

SELECT * FROM RnkPerfect
GO


--

SOLUTION 1 | Using Ranking Functions

--

;WITH CTE AS
(
	SELECT *
		,ROW_NUMBER() OVER (PARTITION BY dt ORDER BY dt,Hedge) RowNum 		
	FROM RnkPerfect
)
SELECT * FROM CTE WHERE RowNum <= (
SELECT MAX(RowNum) r FROM(
SELECT MIN(Dt) dt FROM CTE) a 
INNER JOIN CTE b ON a.dt = b.Dt ) 

--

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

SQL Puzzle | The YesNo Puzzle

25 Thursday Feb 2016

Posted by Pawan Kumar Khowal in SQL SERVER, SQL Server Interview Questions, T SQL Puzzles, Tricky SQL Queries

≈ Leave a comment

Tags

SQL Puzzle | The YesNo Puzzle

Puzzle Statement

In this puzzle you have to accept an input parameter. The value of the input parameter can be Yes or No. The input table contains 2 columns Id, Bits.

If the user passes No value as the input parameter then we should get all Ids where at least one Bits value is No. E.g. For Id = 1 we have bits Yes & No. So all distinct Ids = 1 should come as the output.

If the user passes Yes value as the input parameter then we should get all Ids where we don’t have a single Bits value as No. E.g. For Id = 2 we have don’t have a single bits value as No. So Id = 2 should come as the output.

Please checkout Sample input and expected output for Yes and No.

1	Yes
2	Yes
1	No
1	No
3	No
4	Yes

Expected Output

For Yes

2	Yes
4	Yes

For No

1	Yes
1	No
3	No

Script

Use below script to create table and insert sample data into it.

--


CREATE TABLE YesNo
(
	 Id SmallInt
	,Bits VarChar(3)
)
GO

INSERT INTO YesNo
VALUES
(1, 'Yes'),
(2, 'Yes'),
(1, 'No'),
(1, 'No'),
(3, 'No'),
(4, 'Yes')
GO

SELECT * FROM YesNo
GO


--

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

SOLUTION 1 | Using Except & Intersect

--

DECLARE @Input AS VARCHAR(3) = 'Yes'

IF @Input = 'Yes'
	BEGIN
		SELECT DISTINCT u.* FROM YesNo u
		INNER JOIN
		(
			SELECT ID FROM YesNo
			EXCEPT
			SELECT ID FROM YesNo WHERE Bits = 'No'
		)y
		ON y.Id = u.Id
	END
ELSE IF @Input = 'No'
	BEGIN
		SELECT u.* FROM YesNo u
		INNER JOIN
		(
			SELECT ID FROM YesNo WHERE Bits = 'Yes'
			INTERSECT
			SELECT ID FROM YesNo WHERE Bits = 'No'
		)y
		ON y.Id = u.Id
		UNION
		SELECT ID , Bits FROM YesNo WHERE Bits = 'No'
	END

--

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

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

SQL Puzzle | The Number Duplicate Puzzle

24 Wednesday Feb 2016

Posted by Pawan Kumar Khowal in Download SQL Interview Q's, SQL SERVER, SQL Server Interview Questions, T SQL Puzzles, Tricky SQL Queries

≈ 3 Comments

Tags

SQL Puzzle | The Number Duplicate Puzzle

Puzzle Statement

This puzzle is simple. In this puzzle you have to add a number to duplicate values present in the table. E.g. if the values are SQL,SQL then the output should be SQL1, SQL2

Sample Input

1	Ramesh
2	Pawan
3	Pawan
4	Sharlee
5	Isha

Expected Output

1	Ramesh
2	Pawan1
3	Pawan2
4	Sharlee
5	Isha

Rules/RestrictionsThe solution should be should use “SELECT” statement or “CTE”.

ScriptUse below script to create table and insert sample data into it.

--

CREATE TABLE NumberDuplicates
(
	 ID SMALLINT
	,NAME VARCHAR(10)
)
GO

INSERT INTO NumberDuplicates
VALUES
(1,'Ramesh'),
(2,'Pawan'),
(3,'Pawan'),
(4,'Sharlee'),
(5,'Isha')

GO

--

SOLUTION 1 | Using Ranking Functions

--

SELECT Id,CASE WHEN cnt&gt;1 THEN CONCAT(Name,rnk) ELSE NAME END Name FROM 
(
	SELECT * 
		, ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY NAME) rnk
		, COUNT(*) OVER (PARTITION BY NAME) cnt
	FROM NumberDuplicates
)a
ORDER BY ID
--

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

SQL Puzzle | The User Access Control Puzzle

07 Sunday Feb 2016

Posted by Pawan Kumar Khowal in Download SQL Interview Q's, SQL Performance Tuning, SQL Server Interview Questions, T SQL Puzzles, Tricky SQL Queries

≈ Leave a comment

Tags

SQL Puzzle | The User Access Control Puzzle

Puzzle Statement

Well I must say this is a very very good puzzle. This puzzle was asked to me by my friend & colleague Deepak. He came to this scenario while working on a task. In this puzzle there are only 6 columns. Its basically a tree structure. column a is a parent. Column b is children of column a and so on. Now if column b has all value 0 then for column a the output will be distinct values from column a and 0 for column b. If you have non zero value in column b for a row then for column b that non zero column value will come and in column a 0 will come. For details check sample input and expected output.

Sample Input

5	5	1	0	0
5	5	2	3	0
5	4	3	4	0
5	4	3	5	1
5	4	4	6	2

Expected Output

3,4

1,2

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE DeepakPuzzle
(      
 a INT
,b INT
,c INT
,d INT
,e INT
,f INT
)
GO

INSERT INTO DeepakPuzzle VALUES
(5,5,1,0,0,0),
(5,5,2,3,0,0),
(5,4,3,4,0,0),
(5,4,3,5,1,0),
(5,4,4,6,2,0)
GO


GO
--

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

SOLUTION 1 | Using Cross APPLY

--

No solution 🙂

--

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

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

Improving my SQL BI Skills

Monthly Archives: February 2016

SQL Puzzle | The Comma Separated Puzzle

SQL Puzzle | The Max First Rank Puzzle

SQL Puzzle | The YesNo Puzzle

SQL Puzzle | The Number Duplicate Puzzle

SQL Puzzle | The User Access Control Puzzle

Share this

Share this

Share this

Share this

Share this