Source/NuGetGallery.Operations/Scripts/DownloadReport_RecentPopularity.sql


SELECT TOP(100) Dimension_Package.PackageId, SUM(DownloadCount) 'Downloads'
FROM Fact_Download
INNER JOIN Dimension_Package ON Dimension_Package.Id = Fact_Download.Dimension_Package_Id
INNER JOIN Dimension_Date ON Dimension_Date.Id = Fact_Download.Dimension_Date_Id
WHERE Dimension_Date.[Date] >= CONVERT(DATE, DATEADD(day, -42, GETDATE()))
  AND Dimension_Date.[Date] < CONVERT(DATE, GETDATE())
  AND Dimension_Package.PackageListed = 1
GROUP BY Dimension_Package.PackageId
ORDER BY SUM(DownloadCount) DESC