Calculate rolling year totals in sql

Question

I am gathering something that is essentially am "enrollment date" for users. The "enrollment date" is not stored in the database (for a reason too long to explain here), so I have to deduce it from the data. I then want to reuse this CTE in numerous places throughout another query to gather values such as "total orders 1 year before enrollment" and "total orders 1 year after enrollment".

I haven't gotten this code to run, as it's much more complex in my actual data set (this code is paraphrased from the actual code) and I have a feeling it's not the best way to do this. As you can see, my date conditionals are mostly just placeholders, but I think it should be obvious what I am trying to do.

That said, I think this would mostly work. My question is, is there a better way to do this? Additionally, could I combine the rolling year before and rolling year after into one table somehow? (maybe window functions)? This is part of a much bigger query, so the more consolidation I could do, the better it would seem.

For what it's worth, the subquery to derive the "enrollment date" is also more complex than shown here.

With enroll as (Select 
    user_id,
    MIN(date) as e_date
    FROM `orders` o
        WHERE (subscribed = True)
    group by user_id
  )

Select* 
   from users
      left join (select 
             user_id,
             SUM(total_paid) 
             from orders where date > (select enroll.e_date where user_id = user_id)  AND date < (select enroll.e_date where user_id = user_id + 365 days)
             and order_type = 'special'
             group by user_id
      ) as rolling_year_after on rolling_year_after.user_id = users.user_id
      left join (select 
             user_id,
             SUM(total_paid)
             from orders where date < (select enroll.e_date where user_id = user_id) and date > (select enroll.e_date where user_id = user_id - 365 days)
             and order_type = 'special'
             group by user_id
      ) as rolling_year_before on rolling_year_before.user_id = users.user_id

userSc9k1 · Answer

Maybe something like this, not sure if its more performant, but looks a bit cleaner:

With enroll as (Select 
    user_id,
    MIN(date) as e_date
    FROM `orders` o
        WHERE (subscribed = True)
    group by user_id
  )
  
, rolling_year as (

select 
             user_id,
             SUM(CASE WHEN date between enroll.edate and enroll.edate + 365 days then (total_paid) else 0 end) as rolling_year_after,
             SUM(CASE WHEN date between enroll.edate - 365 days and enroll.edate then (total_paid) else 0 end) as rolling_year_before
             from orders 
             left join enroll 
             on order.user_id = enroll.user_id
             where order_type = 'special'
             group by user_id
)

Select * 
   from users
   left join rolling_year
      on users.user_id  = rolling_year.user_id

Calculate rolling year totals in sql

1 Answers