One of my friends asked me this question, it is a small sql puzzle, we have a table in the below organization;
create table tr_football_league
( club_id number,
club_name varchar2(32),
championship_year number(4), — only the year
year_100th_champion number(1) — 0 false, 1 true
) ;
— the data to be inserted is here
insert into tr_football_league (1, ‘FENERBAHCE’, 2007, 1) ;
…
so the wanted sql must return which club was champion on its 100th year and also owns the most championship count in the data mentioned above. the answer is so easy, it is my precious club FENERBAHCE, but what do you think about the sql?
Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog 
with just last 20 years data I have written below simple query, I am sure this can be written in a much more efficient way;
drop table tr_football_league purge ;
create table tr_football_league
( club_id number,
club_name varchar2(32),
championship_year number(4),
year_100th_champion number(1) ) ;
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) ;
commit ;
with grp_qry as (select club_name, count(*) cnt from tr_football_league group by club_name)
select distinct club_name “aslan terbiyecisi” from tr_football_league
where club_name = ( select club_name from grp_qry
where cnt = ( select max(cnt) from grp_qry ) )
and club_id in
(select club_id from tr_football_league where year_100th_champion = 1) ;
aslan terbiyecisi
—————–
FENERBAHCE
SELECT club_name
FROM (SELECT *
FROM (SELECT club_name,
year_100th_champion,
COUNT(*) over(PARTITION BY club_name) cnt
FROM tr_football_league)
WHERE year_100th_champion = 1
ORDER BY cnt DESC)
WHERE rownum = 1
In terms of optimizer cost it is 1/3 of the previous one. Yes this love will never end. The love of Analytics…
Same cost,low byte ;)
with grp_qry as (
SELECT CLUB_NAME,COUNT(*) TOP FROM tr_football_league
GROUP BY CLUB_NAME
having COUNT(*)!=COUNT(*)-SUM(year_100th_champion)
ORDER BY TOP DESC
)
SELECT CLUB_NAME FROM grp_qry
WHERE ROWNUM=1
select CLUB_NAME from (select CLUB_NAME,count(*) cnt from tr_football_league where club_name in (select CLUB_NAME from tr_football_league where YEAR_100TH_CHAMPION=1) group by club_name order by CNT desc) where CNT= (select max(cnt) from (select count(*) cnt from tr_football_league where club_name in (select CLUB_NAME from tr_football_league where YEAR_100TH_CHAMPION=1) group by club_name order by CNT desc));