Geek Answers Handbook

Request for the best sales results for the previous week

I have a database table with three columns.

WeekNumber, ProductName,SalesCount

Examples of data are given in the table below. I want to get the top 10 players (%) for week 26 for the previous week, i.e. Week 25. The only condition is that in both weeks the product must have more than 0.

In the sample data, B, C, D are common products, and C has the highest gain%.

Likewise, I will need the top 10 losers.

What I have tried so far is to make an inner join and get common products between two weeks. However, I cannot get the logic of the best winners.

enter image description here

The output should look like

 Product    PercentGain

  C            400%

  D            12.5%

  B            10%
+4
source share
3

, :

select top 10 product , gain [gain%]
from 
(
SELECT product, ((curr.salescount-prev.salescount)/prev.salescount)*100 gain 
from   
  (select weeknumber, product, salescount from tbl) prev
  JOIN
  (select weeknumber, product, salescount from tbl) curr
on prev.weeknumber = curr.weeknumber - 1
AND prev.product = curr.product
where prev.salescount > 0 and curr.salescount > 0
)A 
order by gain desc

25 26, WHERE:

and prev.weeknumber = 25
+4

SQL-Server 2012 ( ), lag "this" . :

SELECT   TOP 10 product, sales/prev_sales - 1 AS gain
FROM     (SELECT product, 
                 sales, 
                 LAG(sales) OVER (PARTITION BY product 
                                  ORDER BY weeknumber) AS prev_sales
          FROM   mytable) t
WHERE    weeknumber = 26 AND
         sales > 0 AND
         prev_sales > 0 AND
         sales > prev_sales
ORDER BY sales/prev_sales
+1

This is a request.

select top 10 product , gain [gain%]
from 
(
SELECT curr.Product, ( (curr.Sales - prev.Sales ) *100)/prev.Sales gain 
from   
  (select weeknumber, product, sales from ProductInfo where weeknumber = 25 ) prev
  JOIN
  (select weeknumber, product, sales from ProductInfo where weeknumber = 26 ) curr
on    prev.product = curr.product
where prev.Sales > 0 and curr.Sales > 0
)A 
order by gain desc
+1
source

More articles:


All Articles