Sliding Window Average For Multiple Time Periods - SQL Server
I have a SQL query that returns the average time to signup for o开发者_开发问答ur users who signed up in the last 30 days:
select avg(datediff(dd, acquisitiontime,createdate)) from users where
createdate > getdate() - 30 and
acquisitionmedium = 'cpc' and
acquisitionsource = 'google' and
acquisitiontime is not null
I want to watch how this has changed over time.
How do I change this query so that I can spit out a table with (Month, Avg Time to Signup for that Month)?
select
DATEADD(month, -n.number, getdate()) OneMonthFromThisDate,
avg(datediff(dd, acquisitiontime, createdate)) AverageInThisMonth
from users
join master..spt_values n on n.type='P' and n.number between 1 and 24
where createdate > DATEADD(month, -n.number, getdate())
and createdate <= DATEADD(month, 1-n.number, getdate())
and acquisitionmedium = 'cpc'
and acquisitionsource = 'google'
and acquisitiontime is not null
group by n.number, DATEADD(month, -n.number, getdate())
order by n.number
This puts the most recent first.
精彩评论