Get Customers With a Specific Product In Their Top Products

user_5432399 0
Viewed 33

I have a query that gets the count of products for a customer like this

SELECT top(4)
    count([Product]) as p
    , Product 
FROM CustomerProducts
WHERE CustomerID = '123'
GROUP BY Product
ORDER BY p desc

This works and will give me for instance and will give me for instance

   154 Bike
   100 Truck
   90  Tracktor
   80  Buggy

Now I need to get all the customers who have bike in their top 4. I have tried to do this unsuccessfully with a sub query.

SELECT CustomerID
WHERE Product
EXISTS IN {customers top 4 products}

This is the query I am trying to achieve

sql-server
2 Answers

Use a windowing function:

SELECT
  CustomerId
FROM
  (
    SELECT
      CustomerID
     ,Product
     ,RANK() OVER (PARTITION BY CustomerId ORDER BY COUNT(*) DESC) AS ProductRank
    FROM
      CustomerProducts
    GROUP BY
      CustomerID
     ,Product
  ) ProductSummary
WHERE
  ProductRank <= 4
    AND Product = 'Bike'

You may want to use DENSE_RANK depending on how you want to define "top 4".

user_139429860

You can do this with an APPLY, however, if it is a large table please do not run something like this in production (as this would likely be a very heavy query).

Also query is not tested. But should work.

SELECT cp.CustomerID
FROM CustomerProducts cp
CROSS APPLY (
    SELECT TOP 4 xp.Product, count(*)
    FROM CustomerProducts xp
    WHERE xp.CustomerId = cp.CustomerId
    GROUP BY xp.Product
    ORDER BY COUNT(*) DESC
) xrp
WHERE xrp.Product = 'Bike'
user_6439510
Related