Tags

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


SQL Puzzle | The Tournament Puzzle | SQL INTERVIEW QUESTION

In this puzzle you have to find out how many have been matches played by p1 and ps with how many matched won by p1 and how many matches won by p2. For more details please refer sample input and expected output

Sample INPUT

P1 P2 WonBy
Pawan Avtaar Pawan
Pawan Avtaar Pawan
Pawan Misha Misha
Avtaar Misha Avtaar
Avtaar Pawan Avtaar

Expected OUPUT

p1 p2 MatchesPlayed Matches WonBy = p1 Matches WonBy = p2
Avtaar Misha 1 1 0
Avtaar Pawan 3 1 2
Misha Pawan 1 1 0

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

--

CREATE TABLE Matches
(
	 P1 VARCHAR(10)
	,P2 VARCHAR(10)
	,WonBy VARCHAR(10)
)
GO

INSERT INTO Matches VALUES
('Pawan','Avtaar','Pawan'),
('Pawan','Avtaar','Pawan'),
('Pawan','Misha','Misha'),
('Avtaar','Misha','Avtaar'),
('Avtaar','Pawan','Avtaar')
GO

SELECT * FROM Matches
GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Solutions 🙂

SOLUTION 1

--

SELECT p1,p2
,COUNT(1) MatchesPlayed
,ISNULL(SUM(CASE WHEN Wonby = p1 THEN 1 END),0) [WonBy = p1]
,ISNULL(SUM(CASE WHEN Wonby = p2 THEN 1 END),0) [WonBy = p2]
FROM
(
	SELECT p1,p2,Wonby 
	FROM Matches WHERE p1<p2
	UNION ALL
	SELECT p2,p1,Wonby 
	FROM Matches WHERE p1>p2
)t
GROUP BY p1,p2

--

OUTPUT 1

--

p1         p2         MatchesPlayed WonBy = p1  WonBy = p2
---------- ---------- ------------- ----------- -----------
Avtaar     Misha      1             1           0
Avtaar     Pawan      3             1           2
Misha      Pawan      1             1           0

(3 rows affected)

--

SOLUTION 2

--

;WITH CTE AS
(
	SELECT * , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rnk
	FROM Matches a
)
,CTE1 AS
(
	SELECT 
		 p1 = IIF(c.rnk <> c1.rnk AND c1.p1 IS NOT NULL AND c1.p2 IS NOT NULL,c1.p1,c.p1)
		,p2 = IIF(c.rnk <> c1.rnk AND c1.p1 IS NOT NULL AND c1.p2 IS NOT NULL,c1.p2,c.p2)
		,c.WonBy
		,ROW_NUMBER() OVER (PARTITION BY c.rnk ORDER BY c.rnk) rnk1
	FROM CTE c
	LEFT JOIN CTE c1 ON c.P1 = c1.P2 AND c.P2 = c1.P1 AND c.rnk > c1.rnk
)
SELECT p1,p2
,COUNT(1) MatchesPlayed
,ISNULL(SUM(CASE WHEN Wonby = p1 THEN 1 END),0) [WonBy = p1]
,ISNULL(SUM(CASE WHEN Wonby = p2 THEN 1 END),0) [WonBy = p2]
FROM CTE1
WHERE rnk1 = 1
GROUP BY p1,p2 
ORDER BY p1

--

OUTPUT 2

--

p1         p2         MatchesPlayed WonBy = p1  WonBy = p2
---------- ---------- ------------- ----------- -----------
Avtaar     Misha      1             1           0
Pawan      Avtaar     3             2           1
Pawan      Misha      1             0           1
 

(3 rows affected)

--

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Add a comment if you have any 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