CODEWITHSUNDEEP
sqlsql-server
Husam2509
Husam2509

Reputation: 11

How to select rows if customer has purchased the same product 3x in a row

I have 2 tables, [customer_list] and [purchase_history]. I want to find list of all customers whose most recent three purchases have been a particular product three times in a row.

Table example below:

customer_list          purchase_history
id |  name             id | cust_id| product_id | purchase_date
1  |  Alan              1 |    1   |     AA     |  2019-06-05
2  |  Bob               2 |    1   |     BB     |  2019-1-13
3  |  Carol             3 |    2   |     CC     |  2018-11-23
4  |  David             4 |    1   |     AA     |  2017-03-12
                        5 |    3   |     CC     |  2019-09-25
                        6 |    1   |     FF     |  2019-04-31
                        7 |    1   |     AA     |  2019-02-14
                        8 |    4   |     AA     |  2019-03-05
                        9 |    4   |     AA     |  2019-04-10
                       10 |    2   |     AA     |  2019-02-24
                       11 |    4   |     AA     |  2019-05-16

I'm playing around with code similar to below but appreciate if someone could help out too:

select * from customer_list t1 where EXISTS(
    select * from purchase_history t2 where **latest 3 product_id** is like 'AA' )

My final result should be like this:

  customer_list
  id  | name
  4   | David

Only 'David' should appear in the result because his last 3 most recent purchases were the particular product selected "AA".

I'm running MSSQL 2008 on Windows Server 2008.

Upvotes: 0

Views: 219

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

You can use cross apply and aggregation:

select c.id
from customer_list c cross apply
     (select top (3) ph.*
      from purchase_history ph
      where ph.cust_id = c.id
      order by ph.purchase_date desc
     ) ph3
group by c.id
having min(ph3.product_id) = max(ph3.product_id) and
       min(ph3.product_id) = 'AA';

If you might have fewer than three records in the purchase history, then change the having clause to:

having min(ph3.product_id) = max(ph3.product_id) and
       min(ph3.product_id) = 'AA' and
       count(*) = 3;

Upvotes: 1

cybertier
cybertier

Reputation: 23

I imagine you could use a construct like this to get the maximum amount of sequential orders of the same product per customer:

ROW_NUMBER() OVER (PARTITION BY cust_id, product_id ORDER BY cust_id ASC, purchase_date DESC) as rn

Use this in a subquery and group it by cust_id, product_id and get the max(rn) and you should have what you are looking for.

Upvotes: 0

jefftrotman
jefftrotman

Reputation: 1114

I would start with this to sequence orders by client (in descending purchase date order) and assign a sequential row number.

select ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY cust_id ASC, purchase_date DESC) RowNumber, *
from purchase_history

(You don't the need all the fields. I just left the * in so it was easy to verify.)

Wrap that in an outer query that filters the derived row number to limit to 3 most recent orders by customer and group by customer and product. If all 3 of the most recent orders for the customer are the same product, HAVING COUNT(*) = 3 will get what you the customers you want. You can then join or subquery to get the customer_list columns.

Here's the whole query:

select * from customer_list where id in (
select cust_id  from (
select ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY cust_id ASC, purchase_date DESC) RowNumber, 
cust_id, product_id
from purchase_history
) s1
where RowNumber <= 3
group by cust_id, product_id
having count(*) = 3
)

Upvotes: 0

Related Questions