Tags

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


SQL Puzzle | The KIND of DATA Puzzle | SQL Interview Question

In this puzzle you have to identify the type of data present for each Id value. The rules are-.

1. If only numeric values are present for an Id then we should present ‘NUMERIC’ in front of that Id.
2. If only NON numeric values are present for an Id then we should present ‘NON NUMERIC’ in front of that Id.
3. If both numeric and NON numeric values are present for an Id then we should present ‘Combined’ in front of that Id.

Please check the sample input and the expected output.

Sample Input

Id Vals
1 a
1 1
2 1
2 1b
3 2
4 a
4 b
5 a
5 341z
6 1
6 a
6 b
7 1
8 10
8 $

Expected Output

Id Vals TypeOfData
1 a Combined
1 1 Combined
2 1 Combined
2 1b Combined
3 2 NUMERIC
4 a NON NUMERIC
4 b NON NUMERIC
5 a NON NUMERIC
5 341z NON NUMERIC
6 1 Combined
6 a Combined
6 b Combined
7 1 NUMERIC
8 10 Combined
8 $ Combined

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

--

CREATE TABLE TestMultiples
(
	 Id INT
	,Vals VARCHAR(20)
)
GO

INSERT INTO TestMultiples VALUES
(1,'a'),
(1,'1'),
(2,'1'),
(2,'1b'),
(3,'2'),
(4,'a'),
(4,'b'),
(5,'a'),
(5,'341z'),
(6,'1'),
(6,'a'),
(6,'b'),
(7,'1')
GO

INSERT INTO TestMultiples VALUES
(8,'10'),
(8,'$')
GO

SELECT * FROM TestMultiples
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,Vals,
IIF(
 SUM(CASE WHEN a =  'NUMERIC' THEN 1 END) OVER (PARTITION BY Id) 
+SUM(CASE WHEN a =  'NON NUMERIC' THEN 1 END) OVER (PARTITION BY Id) IS NULL, a, 'Combined') TypeOfData  
			FROM
(
	SELECT Id,vals
			 ,   CASE WHEN TRY_CAST(Vals AS NUMERIC(20)) IS NOT NULL THEN 'NUMERIC'  
				 WHEN TRY_CAST(Vals AS NUMERIC(20)) IS NULL THEN 'NON NUMERIC' END a
	FROM TestMultiples
)k



--

Output-1

--                            


Id          Vals                 TypeOfData
----------- -------------------- -----------
1           a                    Combined
1           1                    Combined
2           1                    Combined
2           1b                   Combined
3           2                    NUMERIC
4           a                    NON NUMERIC
4           b                    NON NUMERIC
5           a                    NON NUMERIC
5           341z                 NON NUMERIC
6           1                    Combined
6           a                    Combined
6           b                    Combined
7           1                    NUMERIC
8           10                   Combined
8           $                    Combined

(15 rows affected)


--

SOLUTION – 2 | By Vaibhav Goel – WITHOUT Vals COLUMN 

--


;WITH CTE AS
(
	SELECT Id,vals
             ,   CASE WHEN TRY_CAST(Vals AS NUMERIC(20)) IS NOT NULL THEN 'NUMERIC'  
                  WHEN TRY_CAST(Vals AS NUMERIC(20)) IS NULL THEN 'NON NUMERIC' END a
	FROM TestMultiples
)
SELECT ID
,CASE WHEN cn> 1 THEN 'Combined' WHEN CN = 1 AND m = 'Numeric' THEN 'NUMERIC'
	  WHEN cn = 1 and m = 'NON NUMERIC' THEN 'NON NUMERIC'  END d 
FROM 
(
	Select ID,COUNT(DISTINCT a) cn,MAX(a) m from cte GROUP BY ID
) ab


--

Output-2 – WITHOUT Vals COLUMN

--                            


ID          d
----------- -----------
1           Combined
2           Combined
3           NUMERIC
4           NON NUMERIC
5           NON NUMERIC
6           Combined
7           NUMERIC
8           Combined

(8 rows affected)


--

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

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com