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 p1p2 )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 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 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/