Search code examples
sql-serveradventureworks

Select top n from different categories

So I want to select the top 10 most sold products, per quarter, per year. Quarter being 1/4 of the year. example:

year->2017 1st quarter: top 10 sold products

i would want this kind of output: (where key->ProductKey, ProductVolume->Count of sales of that product, Quarter->Number of the quarter in question, Year->Year in question.

|Key|ProductVolume|Quarter|Year| ....... ....

2nd quarter: top 10 sold products ... year->2016 1st quarter: top 10 sold products 2nd quarter: top 10 sold products

SELECT s.ProductKey as [Key],
    COUNT(s.ProductKey) as [ProductVolume],
    datepart(q, s.OrderDate) as [Quarter],(yyyy, s.OrderDate) as [Year]
FROM (Select p.ProductKey,
             s.OrderDate,
             row_number() over(PARTITION BY datepart(q, s.OrderDate)
                order by 
                p.ProductKey desc) as rn
      FROM Sales as s 
      INNER JOIN SalesProduct as sp
          ON sp.SalesKey=s.SalesKey
      INNER JOIN Product as p
          ON p.ProductKey=sp.ProductKey
      ) as s
WHERE datepart(yyyy, s.OrderDate) 
    BETWEEN YEAR(getdate())-2 AND YEAR(getdate())
GROUP BY datepart(q, s.OrderDate), datepart(yyyy, s.OrderDate), s.ProductKey

this is the result I'm obtaining right now, all the results...

enter image description here

enter image description here

The image above represents the result of this query's, that are the layout of the tables that i'm using in the inner joins.

Select top 1 * from Product;
  Select top 1 * from Sales;
  Select top 1 * from SalesProduct;
Solution

  • Ordered by sales value per product per quarter+year.

    DECLARE @Sales TABLE (
    SalesKey INT,
    OrderDate DATETIME
)

DECLARE @SalesProduct TABLE (
    SalesKey INT,
    ProductKey INT,
    UnitPrice MONEY,
    OrderQuantity TINYINT
)

INSERT INTO @Sales(SalesKey,OrderDate) SELECT 1, '2018-05-18'
INSERT INTO @Sales(SalesKey,OrderDate) SELECT 2, '2018-05-18'

INSERT INTO @SalesProduct(SalesKey,ProductKey,UnitPrice,OrderQuantity) SELECT 1,123,3.50,1
INSERT INTO @SalesProduct(SalesKey,ProductKey,UnitPrice,OrderQuantity) SELECT 1,125,3.50,1
INSERT INTO @SalesProduct(SalesKey,ProductKey,UnitPrice,OrderQuantity) SELECT 2,123,2.50,5

SELECT *
FROM (
    Select
        datepart(year, s.OrderDate) AS [Year],
        datepart(q, s.OrderDate) AS [Quarter],
        sp.ProductKey,
        sum(sp.OrderQuantity) AS [TotalOrdered],
        sum(sp.UnitPrice * sp.OrderQuantity) AS [TotalValue],
        row_number() over(PARTITION BY datepart(year, s.OrderDate),datepart(q, s.OrderDate) order by sum(sp.OrderQuantity) desc) as [Row]
    FROM @Sales as s 
    INNER JOIN @SalesProduct as sp ON sp.SalesKey=s.SalesKey
    WHERE datepart(yyyy, s.OrderDate) BETWEEN YEAR(getdate())-2 AND YEAR(getdate())
    GROUP BY datepart(year, s.OrderDate),datepart(q, s.OrderDate),sp.ProductKey
) dat
WHERE dat.[Row]<=10