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!

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;