CODEWITHSUNDEEP
sqlpostgresql
jovicbg
jovicbg

Reputation: 1553

Total amount of spent in last few months in SQL

I have this table (this is sample, original is about 800000 rows): 

    purchase_datetime      customer_id    value    purchase_id
    2013-01-08 17:13:29      45236262       92        2526373
    2013-01-03 15:42:35      45236262       16        2565373
    2013-05-08 17:40:13      45236262       42        2522373
    2013-03-08 09:04:52      45236262       636       2563373
    2013-12-08 12:12:24      45236262       23        2505573
    2013-07-08 22:35:53      35536272       73        2526423
    2013-07-08 09:52:03      35536272        4        5526373
    2013-010-08 16:23:29     52626262       20        2226373
...
    2013-04-08 17:49:31      52626262       27        4526373
    2013-12-09 20:40:53      52626262       27        4626373

Now I need to find the total amount of spent (value) by the customer for the last 1 month, 3 months, 6 months and 12 months. I have no idea what to do with datetime to select the last months.

Upvotes: 0

Views: 201

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You can use conditional aggregation:

select customer_id,
       sum(case when purchase_datetime >= current_timestamp - interval '1 month'
                then value else 0
           end) as amount_1month,
       sum(case when purchase_datetime >= current_timestamp - interval '3 month'
                then value else 0
           end) as amount_3month,
       sum(case when purchase_datetime >= current_timestamp - interval '6 month'
                then value else 0
           end) as amount_6month
. . .

Upvotes: 2

Related Questions