SQL Puzzle | The Count Puzzle for Specific Cases

31 Wednesday Jan 2018

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

Tags

SQL Puzzle | The Count Puzzle

In this puzzle you have to check for each Id we have to count a, b, c and d values. If for each Id these values are not present then do not include them. For more details please check the sample input and expected output.

Sample Input

Id	Vals
1	a
1	b
1	c
1	d
2	a
2	d
3	d
4	g

Expected Output

Id	cnts
1	4
2	2
3	1
4	0

Script

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

--

CREATE TABLE Table1
(
     Id VARCHAR(10) 
    ,Vals VARCHAR(10)
)
GO
 
INSERT INTO Table1 VALUES
(1, 'a'),
(1, 'b'),
(1, 'c'),
(1, 'd'),
(2, 'a'),
(2, 'd'),
(3, 'd')
GO
 
INSERT INTO Table1 VALUES (4,'g')
GO

SELECT * FROM Table1
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 0 | USING SELECT & CASE

--

select Id,ISNULL(SUM(IIF(Vals IN ('a','b','c','d'),1,0)),0) CountVals 
FROM Table1
group by Id

--

Output-1

--                            

Id         CountVals
---------- -----------
1          4
2          2
3          1
4          0


(4 rows affected)

                                
--

SOLUTION 1 | USING Outer Apply AND LEFT JOIN

--

SELECT n.Id, COUNT(t1.Vals) CountVals FROM 
(
	SELECT * FROM (SELECT Id FROM Table1 GROUP BY Id) t1
	OUTER APPLY ( VALUES ('a'),('b'),('c'),('d')) AS y(t) 
)n
LEFT JOIN Table1 t1 ON t1.Vals = n.t AND t1.Id = n.Id
GROUP BY n.Id
GO


--

Output-1

--                            

Id         CountVals
---------- -----------
1          4
2          2
3          1
4          0


(4 rows affected)

                                
--

SOLUTION 2 | USING PIVOT AND CASE

--

;WITH CTE AS
(
    SELECT * , 'Yes' Ya FROM Table1
)
,CTE1 AS
(
	SELECT Id, [a],[b],[c],[d] 
	FROM CTE
	PIVOT
	(
		MAX(Ya) FOR Vals IN ([a],[b],[c],[d])
	)t
)
SELECT Id , 
		 CASE WHEN [a] = 'Yes' THEN 1 ELSE 0 END + 
		 CASE WHEN [b] = 'Yes' THEN 1 ELSE 0 END + 
		 CASE WHEN [c] = 'Yes' THEN 1 ELSE 0 END + 
		 CASE WHEN [d] = 'Yes' THEN 1 ELSE 0 END Cnt
FROM CTE1


--

Output-2

--                            

Id         CountVals
---------- -----------
1          4
2          2
3          1
4          0


(4 rows affected)



                               
--

SOLUTION 3 | USING MAX , CASE AND Group BY

--

SELECT Id , 
		 CASE WHEN [a] = 'Yes' THEN 1 ELSE 0 END + 
		 CASE WHEN [b] = 'Yes' THEN 1 ELSE 0 END + 
		 CASE WHEN [c] = 'Yes' THEN 1 ELSE 0 END + 
		 CASE WHEN [d] = 'Yes' THEN 1 ELSE 0 END Cnt
FROM 
(
	SELECT
		Id
		,MAX(CASE WHEN Vals = 'a' THEN 'Yes' ELSE 'No' END) [a] 
		,MAX(CASE WHEN Vals = 'b' THEN 'Yes' ELSE 'No' END) [b]
		,MAX(CASE WHEN Vals = 'c' THEN 'Yes' ELSE 'No' END) [c]
		,MAX(CASE WHEN Vals = 'd' THEN 'Yes' ELSE 'No' END) [d]
	FROM Table1
	GROUP BY Id
)k

--

Output-3

--                            

Id         CountVals
---------- -----------
1          4
2          2
3          1
4          0


(4 rows affected)





                                
--

SOLUTION 4 |

--

SELECT t.Id,SUM(CASE WHEN CountVals IS NULL THEN 0 ELSE 1 END) cnts from Table1 t
LEFT JOIN
(
	SELECT n.Id, COUNT(n.Vals) CountVals FROM 
	Table1 n 
	WHERE vals IN ('a','b','c','d')
	GROUP BY n.Id
)k on t.Id = k.Id
GROUP BY t.Id
GO


--

Output-4

--                            

Id         CountVals
---------- -----------
1          4
2          2
3          1
4          0


(4 rows affected)





                                
--

SOLUTION 5 | Using CASE and SUM

--

SELECT
	Id
	,SUM(CASE WHEN Vals = 'a' THEN 1 ELSE 0 END)  
	+SUM(CASE WHEN Vals = 'b' THEN 1 ELSE 0 END)
	+SUM(CASE WHEN Vals = 'c' THEN 1 ELSE 0 END)
	+SUM(CASE WHEN Vals = 'd' THEN 1 ELSE 0 END) Cnt
FROM Table1
GROUP BY Id
GO


--

Output-5

--                            

Id         CountVals
---------- -----------
1          4
2          2
3          1
4          0


(4 rows 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 Yes No Pivot Puzzle

31 Wednesday Jan 2018

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

≈ 3 Comments

Tags

SQL Puzzle | The Yes No Pivot Puzzle

In this puzzle you have to check for each Id whether a, b, c and d values are present or not. If yes then you have output ‘Yes’ else ‘No’ for each a, b, c and d column. For more details please check the sample input and expected output.

Sample Input

Id	Vals
1	a
1	b
1	c
1	d
2	a
2	d
3	d

Expected Output

Id	a	b	c	d
1	Yes	Yes	Yes	Yes
2	Yes	No	No	Yes
3	No	No	No	Yes

Script

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

--

CREATE TABLE Table1
(
	 Id VARCHAR(10)	
	,Vals VARCHAR(10)
)
GO

INSERT INTO Table1 VALUES
(1, 'a'),
(1, 'b'),
(1, 'c'),
(1, 'd'),
(2, 'a'),
(2, 'd'),
(3, 'd')
GO

SELECT * FROM Table1
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 PIVOT

--

;WITH CTE AS
(
	SELECT * , 'Yes' Ya FROM Table1
)
SELECT Id, ISNULL([a],'No') [a],ISNULL([b],'No') [b],ISNULL([c],'No') [c],ISNULL([d],'No') [d] FROM CTE
PIVOT
(
	MAX(Ya) FOR Vals IN ([a],[b],[c],[d])
)t


--

Output

--                            

Id         a    b    c    d
---------- ---- ---- ---- ----
1          Yes  Yes  Yes  Yes
2          Yes  No   No   Yes
3          No   No   No   Yes

(3 rows affected)


                                
--

SOLUTION 2 | USING GROUP BY and MAX

--

SELECT 
	Id
	,MAX(CASE WHEN Vals = 'a' THEN 'Yes' ELSE 'No' END) [a] 
	,MAX(CASE WHEN Vals = 'b' THEN 'Yes' ELSE 'No' END) [b]
	,MAX(CASE WHEN Vals = 'c' THEN 'Yes' ELSE 'No' END) [c]
	,MAX(CASE WHEN Vals = 'd' THEN 'Yes' ELSE 'No' END) [d]
FROM Table1
GROUP BY Id

--

Output

--                            

Id         a    b    c    d
---------- ---- ---- ---- ----
1          Yes  Yes  Yes  Yes
2          Yes  No   No   Yes
3          No   No   No   Yes

(3 rows 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 | Club Different Data from two tables Puzzle

30 Tuesday Jan 2018

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

≈ 1 Comment

SQL Puzzle | Club data from two table where data is different puzzle

In this puzzle you have to club data from two table where data is different puzzle. For more details please check the sample input and expected output.

Sample Input

Table1

aId	val
1	a
2	b
3	c
4	d

Table2

aId	val
1	a
2	x
3	y
4	d

Expected Output

Id	val
2	b
3	c
2	x
3	y

Script

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

--

CREATE TABLE testTable1
(
	 aId INT
	,val VARCHAR(1)
)
GO

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

CREATE TABLE testTable2
(
	 aId INT
	,val VARCHAR(1)
)
GO

INSERT INTO testTable2 VALUES
(1,'a'),
(2,'x'),
(3,'y'),
(4,'d')
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 ISNULL(a.aId,b.aId) Id, ISNULL(a.val,b.val) val 
FROM testTable1 a
FULL OUTER JOIN testTable2 b ON a.val = b.val
WHERE a.val IS NULL OR b.val IS NULL

--

Output

--                            

Id          val
----------- ----
2           b
3           c
2           x
3           y

(4 rows 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

What you mean by Hot and Cold Data?

29 Monday Jan 2018

Posted by Pawan Kumar Khowal in Download SQL Interview Q's, SQL Concepts, SQL Performance Tuning, SQL SERVER, SQL Server Interview Questions

≈ Leave a comment

Tags

What you mean by Hot and Cold Data?

In this post we shall understand – What is Hot and Cold Data?

Now a days it is one of the questions people frequently ask’s in the technical interviews? So let’s understand what they are.

Hot Data

Hot data is the data that we access frequently. Consider this as the critical information that required to be accessed quickly and most of the time organizations use this information for making their decisions. For this type of data where performance matters, meaning that needs to be accessed quickly.

Hot data normally resides on fastest storage so that we get good performance.

Cold Data

Cold data is the data that we do not access frequently. Meaning data that doesn’t need to be accessed for long time like months, quarters or years.

This kind of data is perfect for cold storage.

Why we need different kinds of data or what advantage they bring to the table?

Below are the few reasons for the same-

1. Separating cold data from hot data helps the main storage from overloading.
2. We can use the cheap/commodity hardware for cold storage so it helps in decreasing the total storage cost.
3. It also help in managing the large data set as we will have to manage smaller data set for hot data.

Refer below for more details –

http://www.ibmbigdatahub.com/blog/your-big-data-hot-warm-or-cold
https://cloud.google.com/solutions/partners/komprise-archiving-cold-data

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

NEW FEATURES IN SQL SERVER 2016 | Complete List

26 Friday Jan 2018

Posted by Pawan Kumar Khowal in Download SQL Interview Q's, SQL Concepts, SQL Performance Tuning, SQL SERVER, SQL Server Interview Questions

≈ Leave a comment

Tags

NEW FEATURES IN SQL SERVER 2016 | Complete List

In this post we shall check out all the new features Microsoft introduced in SQL Server 2016

#	New Feature	URL

1	DROP IF EXISTS	https://msbiskills.com/2018/01/06/new-t-sql-features-in-sql-server-2016-i/
2	STRING_SPLIT()	https://msbiskills.com/2018/01/07/new-t-sql-features-in-sql-server-2016-ii-string_split/
3	CREATE OR ALTER	https://msbiskills.com/2018/01/08/new-t-sql-features-in-sql-server-2016-iii-create-or-alter/
4	DBCC CLONEDATABASE(Service Pack 1)	https://msbiskills.com/2018/01/08/new-t-sql-features-in-sql-server-2016-iv-dbcc-clonedatabase/
5	DATEDIFF_BIG()	https://msbiskills.com/2018/01/09/new-t-sql-features-in-sql-server-2016-v-datediff_big/
6	STRING_ESCAPE()	https://msbiskills.com/2018/01/09/new-t-sql-features-in-sql-server-2016-vi-string_escape/
7	USE HINT query hint argument	https://msbiskills.com/2018/01/10/new-t-sql-features-in-sql-server-2016-vii-use-hint-query-hint-argument/
8	TRUNCATE TABLE WITH ( PARTITIONS ( … ) )	https://msbiskills.com/2018/01/11/new-t-sql-features-in-sql-server-2016-viii-truncate-table-with-partitions/
9	Session_Context()	https://msbiskills.com/2018/01/11/new-t-sql-features-in-sql-server-2016-ix-session_context/
10	Row Level Security	coming soon….
11	Always Encrypted	coming soon….
12	Stretch Database	coming soon….
13	Temporal table	https://msbiskills.com/2018/01/21/new-t-sql-features-in-sql-server-2016-xi-temporal-tables/
13.1	Temporal table:HIDDEN COLUMN	https://msbiskills.com/2018/03/08/new-t-sql-features-in-sql-server-2016-temporal-table-hidden-column/
14	Built in JSON Support	https://msbiskills.com/2018/01/22/new-t-sql-features-in-sql-server-2016-xiv-json-support-in-sql-server-2016/
15	Dynamic Data Masking	https://msbiskills.com/2018/01/23/new-t-sql-features-in-sql-server-2016-xviii-dynamic-data-masking/
16	QueryStore	coming soon….
17	Polybase	https://msbiskills.com/2018/01/25/new-t-sql-features-in-sql-server-2016-xix-polybase/
18	R-Integration	coming soon….
19	FormatMessage()	https://msbiskills.com/2018/01/11/new-t-sql-features-in-sql-server-2016-x-formatmessage/
20	OPENJSON Function	https://msbiskills.com/2018/01/22/new-t-sql-features-in-sql-server-2016-xii-openjson-function-sql-server/
21	ISJSON Function	https://msbiskills.com/2018/01/22/new-t-sql-features-in-sql-server-2016-xiii-isjson/
22	JSON_VALUE Function	https://msbiskills.com/2018/01/22/new-t-sql-features-in-sql-server-2016-xv-json_value/
23	JSON_MODIFY Function	https://msbiskills.com/2018/01/23/new-t-sql-features-in-sql-server-2016-xvii-json_modify/

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

Improving my SQL BI Skills

Monthly Archives: January 2018

SQL Puzzle | The Count Puzzle for Specific Cases

SQL Puzzle | The Yes No Pivot Puzzle

SQL Puzzle | Club Different Data from two tables Puzzle

What you mean by Hot and Cold Data?

NEW FEATURES IN SQL SERVER 2016 | Complete List

Share this

Share this

Share this

Share this

Share this