Calculate Revenue Recognition per Month on SQL

Question

I have a table with the invoices, invoice date, amount, customer ID. However the plans can be annual or monthly. Also not all invoices are at the start of the month, and customers can upgrade/downgrade midterm.

For the monthly invoices i have the following query

select customer_id, MoY, date, MonthlyFee invoiceamt,
    case  when (30-datetime_diff(date, DATE_TRUNC(date, month), day)+1) in (30,31) then (MonthlyFee)
          when (30-datetime_diff(date, DATE_TRUNC(date, month), day)+1 < 30) then (MonthlyFee*(30-(datetime_diff(date, DATE_TRUNC(date, month), day)+1))/30)
          end as curr_month_rev,
    case  when  (30-datetime_diff(date, DATE_TRUNC(date, month), day)+1) in (30,31) then 0
          when  (30-datetime_diff(date, DATE_TRUNC(date, month), day)+1) <30 then (MonthlyFee*(datetime_diff(date, DATE_TRUNC(date, month), day)+1)/30)
          end as next_month_rev
  from internalapp.accounts a
  left join chargeapp.invoices mi on a.id=mi.customer_id
  group by customer_id, MoY, date, invoiceamt, curr_month_rev, next_month_rev

With this I create 2 new columns which split each invoiced amount into the correct month. am still having trouble joining all the relevant information for each month.. As in: Month1 Rev = Curr_month1_rev + next_month0_rev

Gets a little more complicated when we add the fact customers can up/downgrade their subscriptions at any point. So the new invoice is easy, but the amount to be receongised from the previous invoice would be the Price * (newInvDate-OldInvDate)/30. However not sure how to write that into the case statement...

Answer 1

It's achievable using Lead and Lag functions, or using a self join

(select customer_id, MoY, date, MonthlyFee invoiceamt,
    case  when (30-datetime_diff(date, DATE_TRUNC(date, month), day)+1) in (30,31) then (MonthlyFee)
          when (30-datetime_diff(date, DATE_TRUNC(date, month), day)+1 < 30) then (MonthlyFee*(30-(datetime_diff(date, DATE_TRUNC(date, month), day)+1))/30)
          end as curr_month_rec,
    case  when  (30-datetime_diff(date, DATE_TRUNC(date, month), day)+1) in (30,31) then 0
          when  (30-datetime_diff(date, DATE_TRUNC(date, month), day)+1) <30 then (MonthlyFee*(datetime_diff(date, DATE_TRUNC(date, month), day)+1)/30)
          end as next_month_rec
      from accounts a
      left join mi on a.id=mi.customer_id
      group by customer_id, MoY, date, invoiceamt, curr_month_rec, next_month_rec)
Select customer_id, MoY,(sum(curr_month_rec) + (lag(sum(next_month_rec))over(partition by customer_id order by MoY)))Rec_Rev
from PC
where customer_id='04ba2614-cd37-40df-a332-e6828e2a19c3'
group by customer_id, MoY