advanced top 100 list query

[WB]

I am trying to create a list of top 100 customers by spending for each
store.

I can successfully create the list of top 100 customers for all stores, but
when I try and group by store, not all stores are represented.

Is it possible?

WB

 

[Guest]

Yep, you betcha!

Post your table structure.

 

[John Spencer]

It is possible and it requires a coordinated subquery. Since you haven't
posted any details of your table structure... Here is a greatly simplified
example - assumes a Purchase table with

CustomerID
StoreID
and AmountPurchased

SELECT Distinct StoreID, CustomerID
FROM Purchases as P
WHERE CustomerID in
(SELECT TOP 100 CustomerID
FROM Purchases as P1
WHERE P1.StoreID = P.StoreID
GROUP BY CustomerID
ORDER BY Sum(AmountsPurchased) DESC, CustomerID)

If you want ties, then drop the CustomerID from the Order By clause

 

[WB]

I was able to use your example and create the query; however, it doesn't
seem to produce results in a timely manner. It takes over 7 minutes and I
just stop the query. In my version of the "Purchases" table there are close
to 20k records. Shouldn't the query execute faster than it is? Seems like
with that many records it is calling the sub-query that many times as well.

I even tried using query analyzer with SQL Server and it isn't any faster.

WB

 

[John Spencer]

Coordinated subqueries are slow. When you add in aggregating the data, then
they are even slower.

First, make sure you have indexes on all the relevant fields. In my example
that would be storeid and customerid.

You can test if the query is working by adding a where clause that restricts
the query to just one store

SELECT Distinct StoreID, CustomerID
FROM Purchases as P
WHERE CustomerID in
(SELECT TOP 100 CustomerID
FROM Purchases as P1
WHERE P1.StoreID = P.StoreID
GROUP BY CustomerID
ORDER BY Sum(AmountsPurchased) DESC, CustomerID)
WHERE P.StoreID = 121

You can also try nesting queries. This might be faster.
Save the following as qOne
SELECT StoreID, CustomerID
, Sum(AmountsPurchased) as TotalPurchase
FROM Purchases
GROUP BY StoreID, CustomerID

Then use that saved query
SELECT Distinct StoreID, CustomerID
FROM Purchases as P
WHERE CustomerID in
(SELECT TOP 100 CustomerID
FROM qOne
WHERE qOne.StoreID = P.StoreID
ORDER BY TotalPurchase DESC, CustomerID)

 

[WB]

Yes, I do have indexes setup on the relevant fields.

I tried changing the date range from this year to this week and it did
return some results that look correct. I can create the query and "input"
each store for a list of Top x customers, I just wanted a cleaner way to
have the system return results for each store so the user doesn't have input
each store separately. I can't believe I am the only person working with
this size of a recordset wanting this type of information. I will try and
work with some other sub-queries to see if that helps performance.

WB