Advertisements

SQL Puzzle | The Separation (Chr & Int) Puzzle


SQL Puzzle | The Separation (Chr & Int) Puzzle

In this puzzle you have to separate one column into 5 columns. The input column is alpha & numeric characters. The sequence of the characters should not change. E.g. If you get a char at the start then all the continuous chars will go into the 1st column.

Please check out the sample input values and sample expected output below.

Can you do that in simple query ?. Functions and recursions are not allowed.

Sample Input

Id Data
1 28465dh268ro2785
2 2fjlggh2fsdfk348
3 23fjfd358506vmj6

Expected Output

Id Data 1 2 3 4 5
1 28465dh268ro2785 28465 dh 268 ro 2785
2 2fjlggh2fsdfk348 2 fjlggh 2 fsdfk 348
3 23fjfd358506vmj6 23 fjfd 358506 vmj 6

Script

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

--

CREATE TABLE Split5Data
(
	 Id INT IDENTITY(1,1) PRIMARY KEY
	,Data VARCHAR(1000)
)
GO

INSERT INTO Split5Data VALUES 
('28465dh268ro2785'),
('2fjlggh2fsdfk348'),
('23fjfd358506vmj6')
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


--
 
;WITH CTE AS
(
	SELECT Id,Data, SUBSTRING( Data ,  MIN(Number) , MAX(Number) - MIN(Number) + 1 ) Chr 
		,ROW_NUMBER() OVER (PARTITION BY Id ORDER BY MIN(Number)) rnk 
	FROM
	(
		SELECT * , SUM(cols) OVER (PARTITION BY Id ORDER BY Number) grouper FROM 
		(
			SELECT * ,  CASE WHEN c = lag(c) 
						over(PARTITION BY ID order by Number) THEN 0 ELSE 1 END cols   FROM 
			(
				SELECT * , SUBSTRING(Data,Number,1) rt , CASE WHEN TRY_CAST(SUBSTRING(Data,Number,1) AS INT) IS NULL THEN 1 ELSE 0 END c FROM Split5Data
				CROSS APPLY
				(
					SELECT DISTINCT number FROM master..spt_values
					WHERE number > 0 and number <= LEN(Data)
				)r			
			)w
		)e
	)r
	GROUP BY Id,grouper,Data
)
SELECT Id,Data,[1],[2],[3],[4],[5]
FROM CTE
PIVOT (MAX(Chr) FOR rnk IN ([1],[2],[3],[4],[5])) p
ORDER BY Id

--

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

Advertisements

SQL Puzzle | The Two Underscore Puzzle

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | The Two Underscore Puzzle

In this puzzle you have to extract the data between first and second underscore(_). The length of the data between these two underscore will be random. Please check out the sample input values and sample expected output below.

Can you provide any other logic?

Sample Input

Id Data
1 1234567890123_ABCD_AN_XX_SRCV_A
2 1234567890123_ABCDE_AN_XX_FARB_B
3 eshfew_12_45

Expected Output

Id Data
1 ABCD
2 ABCDE
3 12

Script

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


--

CREATE TABLE TwoUnderscores
(
	 Id INT IDENTITY(1,1) PRIMARY KEY
	,Data VARCHAR(1000)
)
GO

INSERT INTO TwoUnderscores VALUES 
('1234567890123_ABCD_AN_XX_SRCV_A'),
('1234567890123_ABCDE_AN_XX_FARB_B'),
('eshfew_12_45')
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


--
 
SELECT Id, 
   SUBSTRING(Data,  CHARINDEX('_',Data,1) + 1,  
             CHARINDEX('_',Data,CHARINDEX('_',Data,1) + 1 ) - CHARINDEX('_',Data,1) - 1) Data
FROM TwoUnderscores


--

Execution


--
 
/*------------------------
SELECT Id, 
     SUBSTRING(Data,  CHARINDEX('_',Data,1) + 1,  
          CHARINDEX('_',Data,CHARINDEX('_',Data,1) + 1 ) - CHARINDEX('_',Data,1) - 1) Data
FROM TwoUnderscores
------------------------*/
Id          Data
----------- ------------------
1           ABCD
2           ABCDE
3           12

(3 row(s) affected)
--

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 Published Post Puzzle

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | The Published Post Puzzle

In this puzzle you have to find out the published posts. We also have to check for post from wp_postmeta table, here for each post id we should not have any meta_key = ‘Wanted’ and one meta_key should be ‘DocID’. Please check out the sample input values and sample expected output below.

Can you do that in simple query ?

Sample Input

wp_post table

ID Title Staus
123 Title 123 published
124 Title 124 published
125 Title 125 draft
126 Title 126 published
127 Title 127 published

wp_postmeta table

meta_id post_id meta_key meta_value
1 123 DocID xyz123
2 123 wanted yes
3 123 another hello
4 124 DocID xyz124
5 124 name Bill
6 124 another hello
7 125 DocID xyz125
8 125 wanted yes
9 125 another hello
10 126 DocID xyz126
11 126 name Trevor
12 126 another hello
13 127 MK1 MV1
14 127 name Trevor
15 127 another hello

Expected Output

ID Title Staus
124 Title 124 published
126 Title 126 published

Script

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

--

CREATE TABLE wp_post	
(
	 ID	INT 
	,Title VARCHAR(1000)
	,Staus VARCHAR(1000)
)
GO

INSERT INTO wp_post VALUES
('123',	'Title 123',	'published'),	
('124',	'Title 124',	'published'),
('125',	'Title 125',	'draft'),
('126',	'Title 126',	'published'),
('127',	'Title 127',	'published')
GO

CREATE TABLE wp_postmeta	
(		
	 meta_id INT	
	,post_id INT	
	,meta_key VARCHAR(1000)	 
	,meta_value VARCHAR(1000)
)
GO

INSERT INTO wp_postmeta VALUES
(1	,123	,'DocID'	,'xyz123'),
(2	,123	,'wanted'	,'yes'),
(3	,123	,'another'	,'hello'),		
(4	,124	,'DocID'	,'xyz124'),
(5	,124	,'name'	,'Bill'),
(6	,124	,'another'	,'hello'),
(7	,125	,'DocID'	,'xyz125'),
(8	,125	,'wanted'	,'yes'),
(9	,125	,'another'	,'hello'),
(10	,126	,'DocID'	,'xyz126'),
(11	,126	,'name'	,'Trevor'),
(12	,126	,'another'	,'hello'),
(13	,127	,'MK1'	,'MV1'),
(14	,127	,'name'	,'Trevor'),
(15	,127	,'another'	,'hello')
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


--
 

SELECT *
FROM wp_post p
WHERE p.Staus ='published'
AND NOT EXISTS ( SELECT 1 FROM wp_postmeta m WHERE ( m.meta_key = 'wanted' AND m.meta_key IS NOT NULL ) AND m.post_id = p.Id )
AND EXISTS ( SELECT 1 FROM wp_postmeta m WHERE ( m.meta_key = 'DocID' AND m.meta_key IS NOT NULL ) AND m.post_id = p.Id  )

--

Execution


--
 

/*------------------------
SELECT *
FROM wp_post p
WHERE p.Staus ='published'
AND NOT EXISTS ( SELECT 1 FROM wp_postmeta m WHERE ( m.meta_key = 'wanted' AND m.meta_key IS NOT NULL ) AND m.post_id = p.Id )
AND EXISTS ( SELECT 1 FROM wp_postmeta m WHERE ( m.meta_key = 'DocID' AND m.meta_key IS NOT NULL ) AND m.post_id = p.Id  )
------------------------*/
ID          Title                                                                                                                                                                                                                                                            Staus
----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
124         Title 124                                                                                                                                                                                                                                                        published
126         Title 126                                                                                                                                                                                                                                                        published

(2 row(s) affected)

--

Solution – 2


--
 


Select ID FROM wp_post where [staus] = 'Published' AND ID in 
(
Select post_ID from wp_postmeta WHERE meta_key = 'DocID'
EXCEPT
Select post_ID from wp_postmeta WHERE meta_key ='wanted'
)



--

Solution – 3


--
 


Select p.* from wp_post p 
INNER JOIN wp_postmeta m1 ON m1.post_id = p.id
LEFT JOIN wp_postmeta m2 ON m2.meta_key ='wanted' and m1.post_id = m2.post_id
WHERE m1.meta_key = 'DocID' AND m2.post_id is null
AND p.Staus = 'Published'


--

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 Completed Part Puzzle

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | The Completed Part Puzzle

In this puzzle you have to find out which part is finished(Completed). If the completed columns value is 1 for any part that means that the part is completed. We have to find out how many entries are there for each part and which one is completed. We have to print C in case any part is finished(Completed).

Please check out the sample input values and sample expected output below.

Can you do that in a single select ?

Sample Input

PartNm Completed
PartA 0
PartA 1
PartB 0
PartC 0
PartC 0

Expected Output

PartNm NumberOfRecords Completed
PartA 2 C
PartB 1
PartC 2

Script

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

--

CREATE TABLE Parts
(
	PartNm VARCHAR(50) NULL,
	Completed BIT NULL
)
GO

INSERT INTO Parts (PartNm,Completed)
VALUES 
('PartA',0),
('PartA',1),
('PartB',0),
('PartC',0),
('PartC',0)

--
--

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


--
 
SELECT 
		PartNm,
		COUNT(*) NumberOfRecords ,
		CASE WHEN SUM(CAST(Completed AS INT)) = '1' THEN 'C' ELSE '' END Completed
FROM Parts 
GROUP BY PartNm

--

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 Distinct Records Multi-Col Puzzle

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | The Row Number Puzzle

In this puzzle you have to find out distinct values from multiple columns. In the input table you have 2 columns names c1 and c2. E.g.

c1 -> a
c2 -> b

c1 -> b
c2 -> a

The above will be considered as a single value , for these kind of cases we need the first value. In this case it is a,b for c1 and c2 respectively.

Please check out the sample input values and sample expected output below.

Sample Input

c1 c2
a b
b a
c d
e f
g g
g g

Expected Output

c1 c2
a b
c d
e f
g g

Script

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

--


--


CREATE TABLE DistinctRecordsMultipleColumns
(
	 c1 VARCHAR(10)
	,c2 VARCHAR(10)
)
GO
 
INSERT INTO DistinctRecordsMultipleColumns VALUES
('a','b'),
('b','a'),
('c','d'),
('e','f'),
('g','g'),
('g','g')
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


--
 
;WITH CTE AS 
(
	SELECT * , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rnk FROM DistinctRecordsMultipleColumns
)
SELECT c1,c2 FROM 
(
	SELECT a.*
		FROM 
				   CTE a
		INNER JOIN CTE b
			ON a.c1 = b.c2
			and a.c2 = b.c1
		WHERE a.c1 <= a.c2
	UNION ALL
		SELECT k.* 
		FROM 
				  CTE k
		LEFT JOIN CTE k1
		ON k.c1 = k1.c2 AND k1.c1 = k.c2
		WHERE k1.c1 IS NULL
)k
GROUP BY c1,c2
ORDER BY MIN(rnk)

--

 

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