Analysis: Avg time between constituent visits

Aaron,

I love this idea.  We haven't done anything like this, but figuring out the average time between performances for each patron tickled my SQL fancy this morning.  Using the local ticket history table, I came up with the following:

with tkt_list as (
select distinct
		 DENSE_RANK() OVER(PARTITION BY a.customer_no order by a.perf_dt) as row_no
		,a.perf_dt
		,a.perf_name 
		,a.customer_no 
	from dbo.LT_TKT_HIST_DCPA a (nolock)) --change to local ticket history table

select
	 a.customer_no  
	,AVG(DATEDIFF(dd,a.perf_dt,b.perf_dt)) as avg_time_between
from tkt_list a (nolock)
left join tkt_list b (nolock)
	on a.customer_no = b.customer_no 
		and b.row_no = a.row_no + 1
group by a.customer_no 

This give me one row per customer in the local history table and the average number of days between their performances.  Obviously you would want to restrict the performances being looked at in some meaningful way. But I think this is the general idea you were going for.

Thanks for getting my brain moving this morning :)

- Levi

Aaron,

I love this idea.  We haven't done anything like this, but figuring out the average time between performances for each patron tickled my SQL fancy this morning.  Using the local ticket history table, I came up with the following:

with tkt_list as (
select distinct
		 DENSE_RANK() OVER(PARTITION BY a.customer_no order by a.perf_dt) as row_no
		,a.perf_dt
		,a.perf_name 
		,a.customer_no 
	from dbo.LT_TKT_HIST_DCPA a (nolock)) --change to local ticket history table

select
	 a.customer_no  
	,AVG(DATEDIFF(dd,a.perf_dt,b.perf_dt)) as avg_time_between
from tkt_list a (nolock)
left join tkt_list b (nolock)
	on a.customer_no = b.customer_no 
		and b.row_no = a.row_no + 1
group by a.customer_no 

This give me one row per customer in the local history table and the average number of days between their performances.  Obviously you would want to restrict the performances being looked at in some meaningful way. But I think this is the general idea you were going for.

Thanks for getting my brain moving this morning :)

- Levi

Hi Levi!

How's it going? This is awesome! I'll do some tweaking to your query to suppress seasons etc. This is a great starting point! Thanks again. 

I'll let you know how I go.