# Resolved: Customer LTV SQL Query

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

## Question:

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

You need to generate the dates you want to be shown. The `revenue1day` can be today or yesterday. The same for the month column.
```with tbl as (
Select 100 cuid, 302.6 revenue, date "2022-11-30"  purchase_date

),
gen as (
Select cuid,null,purchase_date
from (Select Distinct cuid from tbl),
unnest(generate_date_array("2022-11-15","2023-01-10")) as purchase_date

)

Select *  except(revenue)
from
(
Select cuid, purchase_date,revenue,
sum(revenue) over (partition by cuid,purchase_date) as revenue_today,
sum(revenue) over (partition by cuid,date_trunc(purchase_date,month)) as revenue_thismonth,
sum(revenue) over (partition by cuid order by unix_date(purchase_date) range between 1 preceding and 1 preceding) as revenue_yesterday,
sum(revenue) over (partition by cuid order by unix_date(purchase_date) range between 6 preceding and 0 preceding) as revenue_last7days, # last 6 days including today
sum(revenue) over (partition by cuid order by extract(year from purchase_date)*12+extract(month from purchase_date) range between 12 preceding and 1 preceding) as revenue_previous12months,

from
(
Select * from tbl
where revenue is not null
union all select * from gen
)
)
where revenue is null
order by purchase_date```