sqlsql-serverjoinself-join

Top game with highest sales for each year


I'm using SQL Server with a table about video game sales from the years (1977-2020). The query below is what I've been using to solve the problem: finding the #1 game with the highest sales for each year. When executed the output gives me multiple of the same years and video games that are 10yrs ahead apart from where they should be.

SELECT VG1.Name, VG4.Release_Date, ROUND(SUM(VG3.Global_sales),2) AS Total_Sales
FROM Video_Game_Sales VG1
INNER JOIN Video_Game_Sales VG2 ON VG1.Name = VG2.Name
INNER JOIN  Video_Game_Sales VG3 ON VG2.Global_sales = VG3.Global_sales
INNER JOIN Video_Game_Sales VG4 ON VG3.Release_Date = VG4.Release_Date
GROUP BY VG4.Release_Date, VG1.Name
ORDER BY VG4.Release_Date, ROUND(SUM(VG3.Global_sales),2) DESC;

I want only one game for each year between 1977-2020 with the highest total sold.

Desired results:

Release_year Title Total_Sales
1977 Combat 1.25
1978 Space Invaders 2.53
1980 Asteroids 4.31

Solution

  • Assuming there is no duplicate records for each game (otherwise, you'll need to handle this first), the first step is to get the top selling games for each year (we'll filter the desired years on this subquery). Then we join this result back with the original table, in order to get the Name of the game.

    Query:

    SELECT
      top_sellers.Release_Year,
      sales.Name AS Title,
      top_sellers.Global_sales AS Total_Sales
    FROM (
      SELECT YEAR(Release_Date) AS Release_Year, MAX(Global_sales) AS Global_sales
      FROM Video_Game_Sales
      WHERE YEAR(Release_Date) BETWEEN '1977' AND '2020'
      GROUP BY YEAR(Release_Date)
    ) AS top_sellers
    JOIN Video_Game_Sales AS sales
    ON top_sellers.Global_sales = sales.Global_sales
    AND top_sellers.Release_Year = YEAR(sales.Release_Date)
    ORDER by Release_Year ASC
    

    Input:

    Name Release_Date Global_sales
    Red Dead Redemption 2 2018-10-26 50000000
    Super Smash Bros. Ultimate 2018-12-7 30440000
    Call of Duty: Modern Warfare 2019-10-25 30000000
    Pokémon Sword / Shield 2019-11-15 25680000
    Animal Crossing: New Horizons 2020-03-20 41590000

    Output:

    Release_Year Title Total_Sales
    2018 Red Dead Redemption 2 50000000
    2019 Call of Duty: Modern Warfare 30000000
    2020 Animal Crossing: New Horizons 41590000

    Demo in sqlfiddle.