CODEWITHSUNDEEP
sqlpostgresql
Rushi Patel
Rushi Patel

Reputation: 3

Find Individuals who have purchased 10 times within a rolling 1 year period

So let's say I have 2 tables. One table is for consumers, and another is for sales.

Consumers

ID Name ...
1 John Johns ...
2 Cathy Dans ...

Sales

ID consumer_id purchase_date ...
1 1 01/03/05 ...
2 1 02/04/10 ...
3 1 03/04/11 ...
4 2 02/14/07 ...
5 2 09/24/08 ...
6 2 12/15/09 ...

I want to find all instances of consumers who made more than 10 purchases within any 6 month rolling period.

SELECT
    consumers.id
        , COUNT(sales.id)
  FROM
      consumers
          JOIN sales ON consumers.id = sales.consumer_id
 GROUP BY
     consumers.id
HAVING
    COUNT(sales.id) >= 10
 ORDER BY
     COUNT(sales.id) DESC

So I have this code, which just gives me a list of consumers who have made more than 10 purchases ALL TIME. But how do I incorporate the rolling 6 month period logic?!

Any help or guidance on which functions can help me accomplish this would be appreciated!

Upvotes: 0

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can use window functions to count the number of sales in a six-month period. Then just filter down to those consumers:

select distinct consumer_id
from (select s.*,
             count(*) over (partition by consumer_id 
                            order by purchase_date
                            range between current row and interval '6 month' following
                           ) as six_month_count
      from sales s
     ) s
where six_month_count > 10;

Upvotes: 1

Related Questions