1

Below is my hive query trying to find out the highest scoring home team for every season.

select t1.season , max(t1.TOTAL_Goals) as Highest_Score
  from
 (select season, home_team_id, sum(home_goals) TOTAL_Goals
    from game_kpark
   group by season, home_team_id
 ) as t1
 group by t1.season

The result of the code above is the table below

t1.season   highest_score

20122013    118
20132014    179
20142015    174
20152016    173
20162017    204
20172018    185

If I include t1.home_team_id after SELECT and GROUP BY at the end, it returns all team's added-up score for every season, not the highest score.

How do I correctly write a query to see the corresponding team of the highest score for every season?

leftjoin
  • 36,950
  • 8
  • 57
  • 116
Keon Park
  • 53
  • 1
  • 4

1 Answers1

0

Use rank() analytic function:

select s.season, s.home_team_id, s.TOTAL_Goals
from
(
select s.season, s.home_team_id, s.TOTAL_Goals, 
       rank() over(partition by season order by s.TOTAL_Goals desc) as rnk
  from
 (--team season totals
  select season, home_team_id, sum(home_goals) TOTAL_Goals 
    from game_kpark
   group by season, home_team_id
 ) s
) s
where rnk=1; --filter teams with highest rank per season
leftjoin
  • 36,950
  • 8
  • 57
  • 116