Query to get top product gainers by sales over previous week

Question

I have a database table with three columns.

WeekNumber, ProductName, SalesCount

Sample data is shown in below table. I want top 10 gainers(by %) for week 26 over previous week i.e. week 25. The only condition is that the product should have sales count greater than 0 in both the weeks.

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

Similarly, I will need top 10 losers also.

What I have tried till now is to make a inner join and get common products between two weeks. However, I am not able to get the top gainers logic.

The output should be like

 Product    PercentGain

  C            400%

  D            12.5%

  B            10%

Answer 1

This will give you a generic answer, not just for any particular week:

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

If you are interested in weeks 25 and 26, then just add the condition below in the WHERE clause:

and prev.weeknumber = 25