Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

May 13, 2007

A small sql puzzle

Filed under: Other — H.Tonguç Yılmaz @ 8:40 pm

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?

This love will never end :)

4 Comments »

  1. 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

    Comment by H.Tonguç Yılmaz — May 20, 2007 @ 6:34 am | Reply

  2. 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…

    Comment by kocakahin — May 20, 2007 @ 3:52 pm | Reply

  3. 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

    Comment by ersin — May 22, 2007 @ 1:32 pm | Reply

  4. 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));

    Comment by Harjit — August 25, 2011 @ 6:38 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: