Weekly Best Seller

Question

Weekly Best Seller

Jako de Wet

Hall of Fame

Points: 3123
More actions
April 23, 2014 at 7:35 am

#306632

Hi there
I have a table containing products sales by Year and Week. I would like to know how many times a certain product was the top seller.
My thinking was to loop through each year and then week creating a temporary list of the top seller for each year/week and then counting based on the product id after that. Is there a way to achieve the result without all the loops?
Here is some sample data:
Year : Week : Product Id : Position
2010 : 1 : 1 : 1
2010 : 1 : 2 : 2
2010 : 1 : 3 : 3
2010 : 1 : 4 : 4
2010 : 2 : 2 : 1
2010 : 2 : 4 : 2
2010 : 2 : 3 : 3
2010 : 2 : 1 : 4
2010 : 2 : 5 : 5
2011 : 4 : 2 : 1
2011 : 4 : 1 : 2
2011 : 4 : 3 : 3
2011 : 6 : 1 : 1
2011 : 6 : 2 : 2
2014 : 1 : 4 : 1
2014 : 1 : 3 : 2
2014 : 2 : 2 : 1
2014 : 2 : 1 : 2
2014 : 2 : 5 : 3
2014 : 3 : 1 : 1
2014 : 3 : 2 : 2
2014 : 3 : 3 : 3
For instance, the result for Product Id = 1 should be 3 as it was number one in:
2010 : 1
2011 : 6
2014 : 3
Any ideas?
Thank you

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

probably be easier if you can supply some table create scripts / sample data and the results you would expect based on the sample data provided.

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

I agree with JLS above. Please help us help you. See the article at the first link in my signature line below under "Helpful Links" for what we're talking about.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bartedgerton SSCrazy Points: 2062 More actions · Answer 3

SELECT

[Product ID]

, COUNT(IIF([Position] = 1,1,NULL)) [Top Seller Count]

FROM

GROUP BY [Product ID]

Jako de Wet Hall of Fame Points: 3123 More actions · Answer 4

@bartedgerton-2: Sorry, my post was a bit mis-leading, I dont actually have the position at that level.

Here are some sample scripts:

Table

CREATE TABLE [dbo].[SalesData](

[SaleYear] [int] NULL,

[SaleWeek] [tinyint] NULL,

[ProductId] [int] NULL,

[Quantity] [int] NULL

) ON [PRIMARY]

data

INSERT INTO SalesData

VALUES

(2010, 1, 1, 35),

(2010, 1, 2, 25),

(2010, 1, 3, 17),

(2010, 1, 4, 5),

(2010, 2, 2, 40),

(2010, 2, 4, 21),

(2010, 2, 3, 13),

(2010, 2, 1, 10),

(2010, 2, 5, 9),

(2011, 4, 2, 31),

(2011, 4, 1, 12),

(2011, 4, 3, 7),

(2011, 6, 1, 22),

(2011, 6, 2, 10),

(2014, 1, 4, 65),

(2014, 1, 3, 45),

(2014, 2, 2, 20),

(2014, 2, 1, 32),

(2014, 2, 5, 65),

(2014, 3, 1, 35),

(2014, 3, 2, 22),

(2014, 3, 3, 12)

The output I would like to have for Product Id = 1 is 3.

Thank you.

bartedgerton SSCrazy Points: 2062 More actions · Answer 5

So you just have to find the top selling product by period first:

WITH topSale AS (

SELECT

SaleYear

, SaleWeek

, MAX(Quantity) [MaxQty]

FROM [dbo].[SalesData]

GROUP BY SaleYear

, SaleWeek

),

SELECT

b.ProductId

, COUNT(b.ProductId) [Top Seller Count]

FROM topSale a

INNER JOIN [dbo].[SalesData] b ON

a.SaleYear = b.SaleYear

and a.SaleWeek = b.SaleWeek

and a.Quantity = b.MaxQty

GROUP BY b.ProductId

pietlinden SSC Guru Points: 63509 More actions · Answer 6

Probably should have done this with a CTE, but anyway, I think this works:

SELECT mx.SaleYear, mx.SaleWeek, mx.highestSale, sd.ProductID

FROM

(SELECT SaleYear

, SaleWeek

, MAX(Quantity) AS highestSale

FROM SalesData

GROUP BY SaleYear, SaleWeek) mx

INNER JOIN SalesData sd ON (

mx.SaleYear = sd.SaleYear

AND mx.SaleWeek = sd.SaleWeek

AND mx.highestSale = sd.Quantity

)

ORDER BY sd.ProductID

, mx.SaleYear

, mx.SaleWeek;

David Burrows SSC Guru Points: 65143 More actions · Answer 7

;WITH cte (ProductId,Pos) AS (

SELECT ProductId,

RANK() OVER(PARTITION BY SaleYear,SaleWeek ORDER BY Quantity DESC)

FROM SalesData)

SELECT ProductId,SUM(1-SIGN(Pos-1))

FROM cte

GROUP BY ProductId

Far away is close at hand in the images of elsewhere.
Anon.

Jako de Wet Hall of Fame Points: 3123 More actions · Answer 8

Thanks David

Your one works nicely, the ,SUM(1-SIGN(Pos-1)) section returns 0 always, but I have tweaked it to fit my needs.

David Burrows SSC Guru Points: 65143 More actions · Answer 9

Jako de Wet (4/24/2014)
SUM(1-SIGN(Pos-1)) section returns 0 always

Curious :ermm:

Returned correct results when run against your test data.

The only way you will get zero is if the product's quantity was never top for any week.

Far away is close at hand in the images of elsewhere.
Anon.

Jako de Wet Hall of Fame Points: 3123 More actions · Answer 10

Hi David

Sorry about that, it works, didn't look at the results properly.

Thanks, works a treat.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 11

David Burrows (4/23/2014)
;WITH cte (ProductId,Pos) AS ( SELECT ProductId, RANK() OVER(PARTITION BY SaleYear,SaleWeek ORDER BY Quantity DESC) FROM SalesData) SELECT ProductId,SUM(1-SIGN(Pos-1)) FROM cte GROUP BY ProductId

This is clever: SUM(1-SIGN(Pos-1))

But this might be easier to understand for most people:

SUM(CASE WHEN Pos = 1 THEN 1 ELSE 0 END)

I just wanted to give an option.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

David Burrows SSC Guru Points: 65143 More actions · Answer 12

Luis Cazares (4/24/2014)
But this might be easier to understand for most people

True 🙂

Doubt there is much difference in performance anyway 😉

Far away is close at hand in the images of elsewhere.
Anon.