SELECT DATEPART(yyyy, DATEADD(mm, 3, mydate)) AS FY



-- Step 1: Just the relevant fields
select userid, orderid, orderdate,
revenue = sum(extprice), ordersequence = row_number() over (partition by userid order by orderdate),
FirstOrderDate = cast(null as datetime)
into #t
FROM [FSAStore_DW].[dbo].[Fact_Sales]
where OrderStatusID  in (1,3,5,21)
and orderitemtype in ('Product','Shipping','Coupon','Discount')
and userid is not null
and StoreID = 1
group by userid, orderid, orderdate

-- Step 2: Customer lifetime stats


select userid, LTO = count(distinct orderid), LTR = sum(revenue), FOD = min(orderdate)
into #t1
from #t
group by userid

-- Step 3: Update FOD
update #t
set FirstOrderDate = b.FOD
from #t a inner join #t1 b
on a.UserID = b.UserID

-- Step 4: Aggregates
select
AcquisitionPeriod = DATEPART(yyyy, DATEADD(mm, -3, FirstOrderDate)),
Customers = count(distinct userid),

Retention12M = count(distinct case when datediff(dd,firstorderdate,orderdate) between 0 and 365 and ordersequence > 1 then userid else null end),
Revenue12M = sum(case when datediff(dd,firstorderdate,orderdate) between 0 and 365 then Revenue else 0 end),
Orders12M = count(distinct case when datediff(dd,firstorderdate,orderdate) between 0 and 365 then orderid else null end),
ValuePerCust12M = sum(case when datediff(dd,firstorderdate,orderdate) between 0 and 365 then Revenue else 0 end)/count(distinct userid),

Retention18M = count(distinct case when datediff(dd,firstorderdate,orderdate) between 0 and 545 and ordersequence > 1 then userid else null end),
Revenue18M = sum(case when datediff(dd,firstorderdate,orderdate) between 0 and 545 then Revenue else 0 end),
Orders18M = count(distinct case when datediff(dd,firstorderdate,orderdate) between 0 and 545 then orderid else null end),
ValuePerCust18M = sum(case when datediff(dd,firstorderdate,orderdate) between 0 and 545 then Revenue else 0 end)/count(distinct userid),


Retention24M = count(distinct case when datediff(dd,firstorderdate,orderdate) between 0 and 730 and ordersequence > 1 then userid else null end),
Revenue24M = sum(case when datediff(dd,firstorderdate,orderdate) between 0 and 730 then Revenue else 0 end),
Orders24M = count(distinct case when datediff(dd,firstorderdate,orderdate) between 0 and 730 then orderid else null end),
ValuePerCust24M = sum(case when datediff(dd,firstorderdate,orderdate) between 0 and 730 then Revenue else 0 end)/count(distinct userid),

Retention36M = count(distinct case when datediff(dd,firstorderdate,orderdate) between 0 and 1095 and ordersequence > 1 then userid else null end),
Revenue36M = sum(case when datediff(dd,firstorderdate,orderdate) between 0 and 1095 then Revenue else 0 end),
Orders36M = count(distinct case when datediff(dd,firstorderdate,orderdate) between 0 and 1095 then orderid else null end),
ValuePerCust36M = sum(case when datediff(dd,firstorderdate,orderdate) between 0 and 1095 then Revenue else 0 end)/count(distinct userid),

Retention48M = count(distinct case when datediff(dd,firstorderdate,orderdate) between 0 and 1460 and ordersequence > 1 then userid else null end),
Revenue48M = sum(case when datediff(dd,firstorderdate,orderdate) between 0 and 1460 then Revenue else 0 end),
Orders48M = count(distinct case when datediff(dd,firstorderdate,orderdate) between 0 and 1460 then orderid else null end),
ValuePerCust48M = sum(case when datediff(dd,firstorderdate,orderdate) between 0 and 1460 then Revenue else 0 end)/count(distinct userid),

RetentionTD = count(distinct case when ordersequence > 1 then userid else null end),
OrdersTD = count(distinct orderid),
RevenueTD = sum(revenue),
ValueTD = sum(revenue)/count(distinct userid),
AllCustomers = CAST(0 AS INT)
INTO #report1
from #t
group by DATEPART(yyyy, DATEADD(mm, -3, FirstOrderDate))
order by DATEPART(yyyy, DATEADD(mm, -3, FirstOrderDate))


select
Period = DATEPART(yyyy, DATEADD(mm, -3, OrderDate)),
Customers = count(distinct userid)
INTO #report2
from #t
group by DATEPART(yyyy, DATEADD(mm, -3, OrderDate))
order by DATEPART(yyyy, DATEADD(mm, -3, OrderDate))

UPDATE #report1
SET AllCustomers = b.Customers
FROM #report1 a INNER JOIN #report2 b
on a.AcquisitionPeriod  = b.period

--- Step 5 FINAL RESULT
TRUNCATE TABLE dbo.LTV_RetentionReport_Ext
INSERT INTO dbo.LTV_RetentionReport_Ext

SELECT * FROM #report1