Garaph - Game Database Direct SQL Access

Game Database Direct SQL Access

SELECT SalesLTD Median FROM
(SELECT a1.TitleEnglish, a1.SalesLTD, COUNT(a1.SalesLTD) Rank
FROM (Select SoftwareWeekly.GameID, ReleaseDate, SoftwareInfo.TitleEnglish, SoftwareInfo.TitleJapanese, SoftwareInfo.Publisher, SoftwareWeekly.SalesLTD, max(Week) as AsOf
FROM SoftwareWeekly, (SELECT GameID, max(SalesLTD) as SalesLTD FROM SoftwareWeekly WHERE SoftwareWeekly.Week <= '2004-01-05'
GROUP BY GameID) HighSales, SoftwareInfo
where
SoftwareWeekly.SalesLTD = HighSales.SalesLTD
and
SoftwareWeekly.GameID = HighSales.GameID
and
SoftwareWeekly.Tracker='Famitsu'
and
SoftwareInfo.Platform='GCN'
and
SoftwareWeekly.GameID = SoftwareInfo.GameID
GROUP BY SoftwareWeekly.GameID
ORDER BY SoftwareWeekly.SalesLTD DESC) a1, (Select SoftwareWeekly.GameID, ReleaseDate, SoftwareInfo.TitleEnglish, SoftwareInfo.TitleJapanese, SoftwareInfo.Publisher, SoftwareWeekly.SalesLTD, max(Week) as AsOf
FROM SoftwareWeekly, (SELECT GameID, max(SalesLTD) as SalesLTD FROM SoftwareWeekly WHERE SoftwareWeekly.Week <= '2004-01-05'
GROUP BY GameID) HighSales, SoftwareInfo
where
SoftwareWeekly.SalesLTD = HighSales.SalesLTD
and
SoftwareWeekly.GameID = HighSales.GameID
and
SoftwareWeekly.Tracker='Famitsu'
and
SoftwareInfo.Platform='GCN'
and
SoftwareWeekly.GameID = SoftwareInfo.GameID
GROUP BY SoftwareWeekly.GameID
ORDER BY SoftwareWeekly.SalesLTD DESC) a2
WHERE a1.SalesLTD < a2.SalesLTD OR (a1.SalesLTD=a2.SalesLTD AND a1.TitleEnglish <= a2.TitleEnglish)
group by a1.TitleEnglish, a1.SalesLTD
order by a1.SalesLTD desc) a3
WHERE Rank = (SELECT (COUNT(*)+1) DIV 2 FROM (Select SoftwareWeekly.GameID, ReleaseDate, SoftwareInfo.TitleEnglish, SoftwareInfo.TitleJapanese, SoftwareInfo.Publisher, SoftwareWeekly.SalesLTD, max(Week) as AsOf
FROM SoftwareWeekly, (SELECT GameID, max(SalesLTD) as SalesLTD FROM SoftwareWeekly WHERE SoftwareWeekly.Week <= '2004-01-05'
GROUP BY GameID) HighSales, SoftwareInfo
where
SoftwareWeekly.SalesLTD = HighSales.SalesLTD
and
SoftwareWeekly.GameID = HighSales.GameID
and
SoftwareWeekly.Tracker='Famitsu'
and
SoftwareInfo.Platform='GCN'
and
SoftwareWeekly.GameID = SoftwareInfo.GameID
GROUP BY SoftwareWeekly.GameID
ORDER BY SoftwareWeekly.SalesLTD DESC) MedianTable);

Connected successfully

Median
50856

Permanent link to this query.

Garaph