Tags

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


SQL Puzzle | The DISTINCT COUNT Without DISTINCT Keyword & With SINGLE SELECT | Advanced SQL

This problem was asked to me and Vaibhav by one of my fellow Architect. He asked whether can we can get the distinct count using the partition function. I told them that with partition we cannot use DISTINCT keyword. Vaibhav came with an interesting idea of using dense rank there and that worked like a charm. Basically in this puzzle you have to find out the distinct count of sub products for each product in a single select with all the existing data. Also better if you don’t use the distinct keyword. For more details please see the sample input and expected output.

Sample Input

Id Prod SubProd Dt
1 a sub1 2018-06-27 17:02:30.050
2 a sub1 2018-06-25 17:02:30.050
3 a sub2 2018-06-24 17:02:30.050
4 b sub3 2018-06-22 17:02:30.050
5 c sub4 2018-06-26 17:02:30.050
6 c sub4 2018-06-25 17:02:30.050
7 c sub4 2018-06-24 17:02:30.050

Expected Output

Id Prod SubProd Dt DistinctCount
1 a sub1 2018-06-27 17:02:30.050 2
2 a sub1 2018-06-25 17:02:30.050 2
3 a sub2 2018-06-24 17:02:30.050 2
4 b sub3 2018-06-22 17:02:30.050 1
5 c sub4 2018-06-26 17:02:30.050 1
6 c sub4 2018-06-25 17:02:30.050 1
7 c sub4 2018-06-24 17:02:30.050 1

Script – DDL and INSERT Sample Data

--

CREATE TABLE DISTINCTProducts
(
	 Id INT
	,Prod VARCHAR(10)
	,SubProd VARCHAR(10)
	,Dt DATETIME
)
GO

INSERT INTO DISTINCTProducts VALUES
(1,'a','sub1',GETDATE()),
(2,'a','sub1',GETDATE()-2),
(3,'a','sub2',GETDATE()-3),
(4,'b','sub3',GETDATE()-5),
(5,'c','sub4',GETDATE()-1),
(6,'c','sub4',GETDATE()-2),
(7,'c','sub4',GETDATE()-3)
GO

SELECT * FROM DISTINCTProducts
GO

--

SOLUTION by Vaibhav Goel | Using DENSE_RANK

--

SELECT a.*, DENSE_RANK() OVER(PARTITION BY Prod ORDER BY SubProd DESC) + 
			DENSE_RANK() OVER(PARTITION BY Prod ORDER BY SubProd) - 1 DistinctCount
FROM DISTINCTProducts a

--

OUTPUT – 1

--

Id          Prod       SubProd    Dt                      DistinctCount
----------- ---------- ---------- ----------------------- --------------------
1           a          sub1       2018-06-27 17:02:30.050 2
2           a          sub1       2018-06-25 17:02:30.050 2
3           a          sub2       2018-06-24 17:02:30.050 2
4           b          sub3       2018-06-22 17:02:30.050 1
5           c          sub4       2018-06-26 17:02:30.050 1
6           c          sub4       2018-06-25 17:02:30.050 1
7           c          sub4       2018-06-24 17:02:30.050 1

(7 rows affected)

--

SOLUTION – 2 | using CROSS APPLY

--

SELECT a.*,x.DistinctSubs
FROM DISTINCTProducts a
CROSS APPLY
(
	SELECT COUNT(DISTINCT SubProd) DistinctSubs FROM DISTINCTProducts
	WHERE a.Prod = Prod
)x

--

OUTPUT – 2

--

Id          Prod       SubProd    Dt                      DistinctSubs
----------- ---------- ---------- ----------------------- ------------
1           a          sub1       2018-06-27 17:02:30.050 2
2           a          sub1       2018-06-25 17:02:30.050 2
3           a          sub2       2018-06-24 17:02:30.050 2
4           b          sub3       2018-06-22 17:02:30.050 1
5           c          sub4       2018-06-26 17:02:30.050 1
6           c          sub4       2018-06-25 17:02:30.050 1
7           c          sub4       2018-06-24 17:02:30.050 1

(7 rows affected)


--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

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

Advertisements