CODEWITHSUNDEEP
sqlite
gwhiz
gwhiz

Reputation: 623

SQLite query is hanging

So I have a table called athletes which has five fields: athlete_ids, sport, gold, silver, bronze. From this table we create a view called most_played_sports which contains all sports that has more than 500 athletes. The task at hand is to find distinct pairs of sports (sport_1, sport_2) using the above view such that the medal count for sport_1 is strictly less than sport_2.

The query I tried is:

create view if not exists most_played_sports as select sport, sum(gold)+sum(silver)+sum(bronze) as medal_count from athletes
    group by sport
    having count(id) >= 500;

select distinct athletes.sport as sport_1, most_played_sports.sport as sport_2 
    from athletes cross join most_played_sports
    where sport_1 > sport_2 and 
    ((select medal_count from most_played_sports where sport=sport_1) < (select medal_count from most_played_sports where sport=sport_2));

The second query is returning some results but is hanging. Is there a better way to do this task than I what I tried?

Upvotes: 0

Views: 318

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

I think you want a self-join:

select mps.sport as sport_1, mps2.sport as sport_2
from most_played_sports mps join
     most_played_sports mps2
     on mps.medal_count < mps2.medal_count;

The question does not suggest any need to use the original table.

Upvotes: 1

Related Questions