Resolved: Customer LTV SQL Query

In this post, we will see how to resolve Customer LTV SQL Query


I m trying to figure out SQL query for calculating Customer Long term value. Here is the current table tblCustomerRevenue:
cuid revenue purchase-date
100 302.6 2022-11-30

Goal is to get the customer order value for every date/every customer the following metrics: 1 Day total, 1 week, 1 month, 1 year purchase totals like below. Can someone please point me in the right direction? Thank you
cuid Date revenue1day revenue1week revenue1month revenue1yr
100 2023-01-01 0 0 . 0 302.6
100 2023-01-02 0 0 . 0 302.6
100 2023-01-03 0 0 . 0 302.6

Best Answer:

You need to generate the dates you want to be shown. The revenue1day can be today or yesterday. The same for the month column.

If you have better answer, please add a comment about this, thank you!