Top game with highest sales for each year: 每年最高销售额的顶级游戏

huangapple go评论72阅读模式
英文:

Top game with highest sales for each year

问题

以下是翻译好的部分:

我正在使用 SQL Server,有一张关于视频游戏销售的表格,涵盖了 1977 年至 2020 年的数据。以下的查询是我一直在使用以解决问题:**找到每年销售额最高的游戏**。当执行该查询时,输出结果给我多次相同的年份以及视频游戏,它们与它们应该在的地方相隔了 10 年。

我只想在 1977 年至 2020 年之间的每一年中,找到销售额最高的游戏。

期望的结果:

|发布年份|标题|总销售额|
|-|-|-|
|1977|Combat|1.25|
|1978|Space Invaders|2.53|
|1980|Asteroids|4.31|
英文:

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

答案1

得分: 1

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

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.

huangapple
  • 本文由 发表于 2023年2月24日 11:05:18
  • 转载请务必保留本文链接:https://go.coder-hub.com/75552272.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定