Pages Per Session

gistfile1.txt

select t.impression_date as date, isnull(total_avg_pages, 0) avg_pages_per_session,
isnull(wd_avg_pages, 0) wd_avg_pages_per_session,
isnull(dwd_avg_pages, 0) dwd_avg_pages_per_session
            from (select top 90 cast(getutcdate()- n as date) impression_date from pub.tally) t
            left join (
                        select avg(cast(y.total_impresions as float)) total_avg_pages, 
						avg(cast(y.wd_impresions as float)) wd_avg_pages, 
						avg(cast(y.dwd_impresions as float)) dwd_avg_pages,
						y.session_date
                        from (
                               select session_id, count(x.referer) total_impresions, 
							   sum(case when wid_type = 'wd' then 1 else 0 end ) as wd_impresions,
							   sum(case when wid_type = 'dwd' then 1 else 0 end ) as dwd_impresions,
							   cast(min(x.created)as date) session_date
								from 
									(
									select  session_id, referer, created, 'wd' as wid_type
									from [pub].[widget_display]
									union all
									select  session_id, [referer], created, 'dwd'
									from [pub].[default_widget_display]
									)x
								group by session_id
							)y
                            group by y.session_date
                        )q
                    on q.session_date =  t.impression_date
					order by t.impression_date desc