CODEWITHSUNDEEP
sqlpostgresql
13ISPaulGeorge
13ISPaulGeorge

Reputation: 117

Identifying Users that have ordered >= N Dollars for N Consecutive Months

I'm looking to write a query that returns a result set of accounts that have ordered at least 250 dollars each month for six consecutive months. Any guidance would be greatly appreciated.

SELECT DATE_TRUNC ('month',order_date)::date as order_month
       ,account_name
       ,account_id
       ,SUM(order_amount) as monthly_spend
FROM order_table
WHERE order_date::date >= current_date - interval '6 months'
GROUP BY 1,2,3

Upvotes: 0

Views: 43

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Those that have ordered at least $250 dollars each month for the last six consecutive months.

Aggregation comes to mind. Let me also align the months with the calendar months (that seems like the most likely interpretation of "last six months").

The idea is to filter down to the months that are >= $250 and then be sure there are six of them.

SELECT account_name, account_id, COUNT(*) as num_months,
      SUM(monthly_spend) as total_spend
FROM (SELECT DATE_TRUNC('month', order_date)::date as order_month,
             account_name, account_id, SUM(order_amount) as monthly_spend
      FROM order_table
      WHERE order_date::date >= date_trunc('month', current_date) - interval '6 months' AND
            order_date::date <  date_trunc('month', current_date)
      GROUP BY 1, 2, 3
      HAVING monthly_spend >= 250
     ) ma
GROUP BY 1, 2
HAVING COUNT(*) = 6;

Upvotes: 1

Related Questions