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!
Advertisement
Answer
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;