Tags

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


SQL PUZZLE | The Distinct and MAX Value Puzzle – A SINGLE SELECT Challenge | SQL Interview Question

In this puzzle you have to find out the unique Ids present in the table. You also have to find out the SUM of Max values of vals columns for each Id and RId. For more details please see the sample input and expected output.

Sample Input

Id rID Vals
121 9 1
121 9 8
122 9 14
122 9 0
122 9 1
123 9 1
123 9 2
123 9 10

Expected Output

Distinct_Ids rid TotalOfMaxVals
3 9 32

Script – DDL and INSERT Sample Data

--

CREATE TABLE MyTabel
(
	  Id INT
	, rID INT
	, Vals INT
)
GO

INSERT INTO MyTabel VALUES 
 (121, 9, 1)
,(121, 9, 8)
,(122, 9, 14)
,(122, 9, 0)
,(122, 9, 1)
,(123, 9, 1)
,(123, 9, 2)
,(123, 9, 10)
GO

SELECT * FROM MyTabel
GO

--

SOLUTION – 1 | A SINGLE SELECT SOLUTION 🙂

--

SELECT TOP 1 
		 SUM(COUNT(DISTINCT Id)) OVER() Distinct_Ids 
		,rid
		,SUM(MAX(Vals)) OVER() TotalOfMaxVals
FROM MyTabel
GROUP BY Id,rid

--

Output – 1

--

Distinct_Ids rid         TotalOfMaxVals
------------ ----------- --------------
3            9           32

(1 row affected)


--

SOLUTION – 2 – A Two SELECT Solution

--

;WITH CTE AS 
(
    SELECT *,ROW_NUMBER() OVER (PARTITION BY Id,rid ORDER BY Vals DESC) rnk
    FROM MyTabel
)
SELECT
    COUNT(DISTINCT Id) AS Distinct_Ids,
	rid,
    SUM(Vals) AS TotalOfMaxVals
FROM CTE WHERE rnk = 1
GROUP BY rid

--

Output – 2

--

Distinct_Ids rid         TotalOfMaxVals
------------ ----------- --------------
3            9           32

(1 row affected)

--

SOLUTION – 3 – A Two SELECT Solution using CROSS APPLY

--

SELECT  COUNT(DISTINCT Id) AS Distinct_Ids,
		RId,
		SUM(Maxy) AS TotalOfMaxVals 
FROM 
(
	SELECT DISTINCT Id,RId
	FROM MyTabel
)x
CROSS APPLY
(
	SELECT MAX(Vals) Maxy
	FROM MyTabel a
	WHERE x.Id = a.Id AND x.rID = a.rID
)z
GROUP BY RId

--

Output – 3

--

Distinct_Ids RId         TotalOfMaxVals
------------ ----------- --------------
3            9           32

(1 row 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