Tags

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


T-SQL Query | [ The Football Puzzle ]  – In this puzzle we have to find 100th Champion which has most number of championships overall. Please check out the sample input and expected output for details.

Sample Input

club_id club_name championship_year year_100th_champion
1 FENERBAHCE 2007 1
2 GALATASARAY 2006 0
3 BESIKTAS 2003 1
1 FENERBAHCE 2005 0
1 FENERBAHCE 2004 0
2 GALATASARAY 2002 0
2 GALATASARAY 2000 0
2 GALATASARAY 1999 0
2 GALATASARAY 1998 0
2 GALATASARAY 1997 0
1 FENERBAHCE 1996 0
1 FENERBAHCE 2001 0
1 FENERBAHCE 1989 0
1 FENERBAHCE 1985 0

Expected Output

club_name
FENERBAHCE

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

Script

Use the below script to generate the source table and fill them up with the sample data.


--Create table

Create table tr_football_league
(
club_id INT,
club_name Varchar(32),
championship_year INT,
year_100th_champion INT
)

--Insert Data
insert into tr_football_league values (1, 'FENERBAHCE', 2007, 1) ;
insert into tr_football_league values (2, 'GALATASARAY', 2006, 0) ;
insert into tr_football_league values (3, 'BESIKTAS', 2003, 1) ;
insert into tr_football_league values (1, 'FENERBAHCE', 2005, 0) ;
insert into tr_football_league values (1, 'FENERBAHCE', 2004, 0) ;
insert into tr_football_league values (2, 'GALATASARAY', 2002, 0) ;
insert into tr_football_league values (2, 'GALATASARAY', 2000, 0) ;
insert into tr_football_league values (2, 'GALATASARAY', 1999, 0) ;
insert into tr_football_league values (2, 'GALATASARAY', 1998, 0) ;
insert into tr_football_league values (2, 'GALATASARAY', 1997, 0) ;
insert into tr_football_league values (1, 'FENERBAHCE', 1996, 0);
insert into tr_football_league values (1, 'FENERBAHCE', 2001, 0) ;
insert into tr_football_league values (1, 'FENERBAHCE', 1989, 0) ;
insert into tr_football_league values (1, 'FENERBAHCE', 1985, 0) ;

--Verify Data
SELECT club_id, club_name , championship_year , year_100th_champion FROM tr_football_league

Update May 14 | Solutions



--


---------------------------------------
--Sol 1
---------------------------------------

;WITH CTE AS
(
	SELECT club_name,
	year_100th_champion,
	COUNT(*) over(PARTITION BY club_name) cnt
	FROM
	tr_football_league
)
,CTE2 AS
(
	SELECT a.club_name,COUNT(a.cnt) Maxy FROM CTE a INNER JOIN tr_football_league b ON a.club_name = b.club_name
	WHERE b.year_100th_champion = 1
	GROUP BY a.club_name
)
SELECT club_name FROM CTE2
WHERE Maxy = (SELECT MAX(Maxy) FROM CTE2)

---------------------------------------
--Sol 2
---------------------------------------


SELECT club_name FROM 
(
      SELECT club_name, ROW_NUMBER() OVER (ORDER BY counts desc) ranker
      FROM
      (
            SELECT club_name, max(cnt) counts
            FROM
            (
                  SELECT club_name,
                  year_100th_champion,
                  COUNT(*) over(PARTITION BY club_name) cnt
                  FROM
                  tr_football_league
            ) a
            GROUP BY club_name
            HAVING SUM(year_100th_champion) > 0  
      ) art
) r WHERE ranker = 1




--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com