TTT - AUTO - NEW Weekly Index

select mem.Aweek,mem.hosts_Signup as mem_Signup,mem.DocUploaded as mem_DocUploaded,mem.DocApproved as mem_DocApproved,mem.renter_First_Reservation,mem.Active_renters,
Hos.hosts_Signup,Hos.DocUploaded as hos_Signup,Hos.DocUploaded as Hos_DocUploaded,Hos.DocApproved as hos_DocUploaded,Hos.Host_First_Rent,Hos.Active_Hosts,
cars.cars_listed, cars.Individual_trevo_Sheild_Cars, cars.Individual_no_insurance_Cars,cars.Fleet_Cars,cars.Active_Trevosheild_Cars,cars.Active_NO_Trevosheild_Cars,cars.Active_Fleet_Cars,
Rev.Reservations,Rev.Individual_trevo_Sheild_Resv, Rev.Individual_no_insurance_resv, Rev.Fleet_resv,Rev.Individual_trevo_Sheild_dur,Rev.Individual_no_insurance_dur,Rev.Fleet_dur,
Rev.comp_gross,Rev.Individual_trevo_Sheild_comp_gross,Rev.Individual_no_insurance_comp_gross,Rev.Fleet_comp_gross,Rev.Individual_trevo_Sheild_comp_net,Rev.Individual_no_insurance_comp_net,
Rev.Fleet_comp_net,Rev.Individual_trevo_Sheild_coupon,Rev.Individual_no_insurance_coupon,Rev.Fleet_coupon, mem.Ayear, cars.Insearch_cars,cars.Individual_Insearch_trevo_Sheild_Cars,
cars.Individual_Insearch_no_insurance_Cars, cars.Insearch_Fleet_Cars
from

/*################################### Members */

(select S.Aweek as Aweek, S.Ayear as Ayear, S.signup as hosts_Signup,D.DocUploaded as DocUploaded, D.DocApproved as DocApproved, F.MFTs as renter_First_Reservation, H.Active_renters as Active_renters  from
(select
extract('week' from u.created_at) as Aweek,
extract('year' from u.created_at) as Ayear,
count(distinct case when u.status not in ('null') then u.id end) as signup
from users u
group by 1,2) S
left join
(select
extract('week' from uk.created_at) as Aweek,
extract('year' from uk.created_at) as Ayear,
count(distinct case when uk.status not in ('null') then uk.user_id end) DocUploaded,
count(distinct case when uk.status = 'approved' then uk.user_id end) DocApproved
from user_kyc uk
group by 1,2) D
on S.Aweek = D.Aweek and S.Ayear = D.Ayear
left join
(select
extract('week' from b.created_at) as Aweek,
extract('year' from b.created_at) as Ayear,
count(distinct case when b.status in ('completed') then b.user_id end) as Active_renters
from bookings b
group by 1,2) H
on H.Aweek = S.Aweek and H.Ayear = S.Ayear
left join
(select  A.Aweek as Aweek, A.Ayear as Ayear, count(distinct case when B.first_res = A.bookingid then A.userid end) as MFTs from
((select distinct b.id as bookingid, extract('week' from b.created_at) as Aweek, extract('year' from b.created_at) as Ayear,  b.user_id userid
from bookings b left join users u on u.id = b.user_id
where b.status = 'completed'
group by b.id
order by b.id desc) A
left join
(select b.user_id as userid , min(b.id) as first_res
from bookings b where b.status ='completed'
group by userid
order by 2 asc) B
on B.userid = A.userid)
group by A.Aweek,A.Ayear) F
on F.Aweek = S.Aweek and F.Ayear = S.Ayear ) mem

/*################################### Hosts */

left join

(select S.Aweek as Aweek, S.Ayear as Ayear, S.signup as hosts_Signup,D.DocUploaded as DocUploaded , D.DocApproved as DocApproved,  F.MFTs as Host_First_Rent, H.Active_Hosts  as Active_Hosts from
(select
extract('week' from l.created_at) as Aweek,
extract('year' from l.created_at) as Ayear,
count(distinct case when l.status not in ('null') then l.id end) as signup
from listings l
group by 1,2) S
left join
(select
extract('week' from ld.created_at) as Aweek,
extract('year' from ld.created_at) as Ayear,
count(distinct case when ld.status not in ('null') then ld.listing_id end) DocUploaded,
count(distinct case when ld.status = 'approved' then ld.listing_id end) DocApproved
from listing_documents ld
group by 1,2) D
on S.Aweek = D.Aweek and S.Ayear = D.Ayear
left join
(select
extract('week' from b.created_at) as Aweek,
extract('year' from b.created_at) as Ayear,
count(distinct case when b.status in ('completed') then b.listing_id end) as Active_hosts
from bookings b
group by 1,2) H
on H.Aweek = S.Aweek and H.Ayear = S.Ayear
left join
(select  A.Aweek as Aweek, A.Ayear as Ayear, count(distinct case when B.first_res = A.bookingid then B.listing_id end) as MFTs from
((select distinct b.id as bookingid, extract('week' from b.created_at) as Aweek, extract('year' from b.created_at) as Ayear, b.listing_id as listing_id
from bookings b
where b.status = 'completed'
group by b.id
order by b.id desc) A
left join
(select b.listing_id as listing_Id , min(b.id) as first_res
from bookings b where b.status ='completed'
group by b.listing_id
order by 2 asc) B
on B.listing_Id = A.listing_Id)
group by A.Aweek,A.Ayear) F
on F.Aweek = S.Aweek and F.Ayear = S.Ayear) Hos
on Hos.Aweek = mem.Aweek and Hos.Ayear = mem.Ayear

/*################################### Cars */

left join
(select A.week as Aweek,
A.year as Ayear,
A.cars_listed as cars_listed,
A.Individual_trevo_Sheild_Cars as Individual_trevo_Sheild_Cars,
A.Individual_no_insurance_Cars as Individual_no_insurance_Cars,
A.Fleet_Cars as Fleet_Cars,
C.Insearch_cars as Insearch_cars,
C.Individual_Insearch_trevo_Sheild_Cars as Individual_Insearch_trevo_Sheild_Cars,
C.Individual_Insearch_no_insurance_Cars as Individual_Insearch_no_insurance_Cars,
C.Insearch_Fleet_Cars as Insearch_Fleet_Cars,
B.Individual_trevo_Sheild_cars as Active_Trevosheild_Cars,
B.Individual_no_insurance_cars as Active_NO_Trevosheild_Cars,
B.Fleet_cars as Active_Fleet_Cars
from
(select
extract('week' from P.date) as week,
extract('year' from P.date) as year,
count(distinct Q.lid) as cars_listed,
count(distinct case when (Q.listing_type = 'Individual' and Q.insurance_package = 'Trevo-Shield') then Q.lid end) as Individual_trevo_Sheild_Cars,
count(distinct case when (Q.listing_type = 'Individual' and Q.insurance_package = 'Normal') then Q.lid end) as Individual_no_insurance_Cars,
count(distinct case when Q.listing_type = 'Fleet' then Q.lid end) as Fleet_Cars
from
(select distinct l.id as lid, dates.ymd as date, count(distinct l.id) as cars_P_Day
from listings  l
join (SELECT generate_series(timestamp '2019-10-01', NOW() , interval '1 day')::date ymd) dates on
dates.ymd > l.created_at::date
where l.status='approved' and l.deleted_at is null
group by 1,2
order by dates.ymd desc) P
left join
(select distinct l.id as lid,l.status as status,
cu.id as cuid,
li.insurance_package_id as insurance_id,
CASE WHEN cu.id IS NOT NULL THEN 'Fleet' ELSE 'Individual' END listing_type,
CASE WHEN li.insurance_package_id=2 THEN 'Trevo-Shield' ELSE 'Normal' END insurance_package,
date_trunc('day', l.created_at)::date as date,
extract('year' from l.created_at) as Ayear
from listings l
LEFT JOIN company_users cu ON cu.user_id=l.user_id
LEFT join listing_insurance li on li.listing_id = l.id
WHERE l.status='approved' AND l.deleted_at IS null) Q
on P.lid = Q.lid
group by 1,2) A
left join
(select I.Aweek as Aweek,
I.Ayear as Ayear,
count(I.bid) as Reservations,
count(distinct case when (I.listing_type = 'Individual' and I.insurance_package = 'Trevo-Shield') then I.car_id end) as Individual_trevo_Sheild_cars,
count(distinct case when (I.listing_type = 'Individual' and I.insurance_package = 'Normal') then I.car_id end) as Individual_no_insurance_cars,
count(distinct case when I.listing_type = 'Fleet' then I.car_id end) as Fleet_cars
from
(select
distinct b.id as bid,
extract('week' from b.created_at) as Aweek,
extract('year' from b.created_at) as Ayear,
b.listing_id as car_id,
sum(case when b.status not IN('cancelled', 'payment_failed', 'withdrawn', 'rejected', 'accepted') then b.gross_amount end) as comp_gross,
sum(case when b.status not IN('cancelled', 'payment_failed', 'withdrawn', 'rejected', 'accepted') then b.net_amount end) as comp_net,
sum(case when b.status not IN('cancelled', 'payment_failed', 'withdrawn', 'rejected', 'accepted') then b.gross_amount end) -
sum(case when b.status not IN('cancelled', 'payment_failed', 'withdrawn', 'rejected', 'accepted') then b.net_amount end) as coupon,
ROUND((EXTRACT(EPOCH FROM b.booking_end_at) - EXTRACT(EPOCH FROM b.booking_start_at))/3600) as dur,
l.status as listing_status, cu.id as cuid, li.insurance_package_id as insurance_id,
CASE WHEN cu.id IS NOT NULL THEN 'Fleet' ELSE 'Individual' END listing_type,
CASE WHEN li.insurance_package_id=2 THEN 'Trevo-Shield' ELSE 'Normal' END insurance_package
from bookings b
left join listings l on l.id = b.listing_id
LEFT JOIN company_users cu ON cu.user_id=l.user_id
LEFT join listing_insurance li on li.listing_id = l.id
where b.status not IN('cancelled', 'payment_failed', 'withdrawn', 'rejected', 'accepted')
and b.booking_end_at is not null
group by 1,2,l.status,cuid,li.insurance_package_id) as I
group by 1,2
order by 1 desc)B
on A.week = B.Aweek and A.year = B.Ayear
left join
(select
extract('week' from A.date) as week,
extract('year' from A.date) as year,
count(distinct B.lid) as Insearch_cars,
count(distinct case when (B.listing_type = 'Individual' and B.insurance_package = 'Trevo-Shield') then B.lid end) as Individual_Insearch_trevo_Sheild_Cars,
count(distinct case when (B.listing_type = 'Individual' and B.insurance_package = 'Normal') then B.lid end) as Individual_Insearch_no_insurance_Cars,
count(distinct case when B.listing_type = 'Fleet' then B.lid end) as Insearch_Fleet_Cars
from
(select distinct l.id as lid, dates.ymd as date, count(distinct l.id) as cars_P_Day
from listings  l
join listing_availability la on la.listing_id=l.id
join (SELECT generate_series(timestamp '2019-09-01', NOW() , interval '1 day')::date ymd) dates on
dates.ymd between la.start_date::date and la.end_date::date
where l.status='approved' and l.deleted_at is null
group by 1,2
order by dates.ymd desc) A
left join
(select distinct l.id as lid,l.status as status,
cu.id as cuid,
li.insurance_package_id as insurance_id,
CASE WHEN cu.id IS NOT NULL THEN 'Fleet' ELSE 'Individual' END listing_type,
CASE WHEN li.insurance_package_id=2 THEN 'Trevo-Shield' ELSE 'Normal' END insurance_package,
date_trunc('day', l.created_at)::date as date,
extract('year' from l.created_at) as Ayear
from listings l
LEFT JOIN company_users cu ON cu.user_id=l.user_id
LEFT join listing_insurance li on li.listing_id = l.id
WHERE l.status='approved' AND l.deleted_at IS null) B
on A.lid = B.lid
group by 1,2) C
on C.week = A.week and C.year = A.year
group by 1,2,3,4,5,6,7,8,9,10,11,12,13) cars
on cars.Aweek = Hos.Aweek and cars.Ayear = Hos.Ayear

/*################################### Revenue and Reservations */

left join
(select I.Aweek as Aweek,
I.Ayear as Ayear,
count(I.bid) as Reservations,
count(distinct case when (I.listing_type = 'Individual' and I.insurance_package = 'Trevo-Shield') then I.bid end) as Individual_trevo_Sheild_Resv,
count(distinct case when (I.listing_type = 'Individual' and I.insurance_package = 'Normal') then I.bid end) as Individual_no_insurance_resv,
count(distinct case when I.listing_type = 'Fleet' then I.bid end) as Fleet_resv,
sum(case when (I.listing_type = 'Individual' and I.insurance_package = 'Trevo-Shield') then I.dur end) as Individual_trevo_Sheild_dur,
sum(case when (I.listing_type = 'Individual' and I.insurance_package = 'Normal') then I.dur end) as Individual_no_insurance_dur,
sum(case when I.listing_type = 'Fleet' then I.dur end) as Fleet_dur,
sum(I.comp_gross) as comp_gross,
sum(case when (I.listing_type = 'Individual' and I.insurance_package = 'Trevo-Shield') then I.comp_gross end) as Individual_trevo_Sheild_comp_gross,
sum(case when (I.listing_type = 'Individual' and I.insurance_package = 'Normal') then I.comp_gross end) as Individual_no_insurance_comp_gross,
sum(case when I.listing_type = 'Fleet' then I.comp_gross end) as Fleet_comp_gross,
sum(case when (I.listing_type = 'Individual' and I.insurance_package = 'Trevo-Shield') then I.comp_net end) as Individual_trevo_Sheild_comp_net,
sum(case when (I.listing_type = 'Individual' and I.insurance_package = 'Normal') then I.comp_net end) as Individual_no_insurance_comp_net,
sum(case when I.listing_type = 'Fleet' then I.comp_net end) as Fleet_comp_net,
sum(case when (I.listing_type = 'Individual' and I.insurance_package = 'Trevo-Shield') then I.coupon end) as Individual_trevo_Sheild_coupon,
sum(case when (I.listing_type = 'Individual' and I.insurance_package = 'Normal') then I.coupon end) as Individual_no_insurance_coupon,
sum(case when I.listing_type = 'Fleet' then I.coupon end) as Fleet_coupon
from
(select
distinct b.id as bid,
extract('week' from b.created_at) as Aweek,
extract('year' from b.created_at) as Ayear,
b.listing_id as car_id,
sum(case when b.status not IN('cancelled', 'payment_failed', 'withdrawn', 'rejected', 'accepted') then b.gross_amount end) as comp_gross,
sum(case when b.status not IN('cancelled', 'payment_failed', 'withdrawn', 'rejected', 'accepted') then b.net_amount end) as comp_net,
sum(case when b.status not IN('cancelled', 'payment_failed', 'withdrawn', 'rejected', 'accepted') then b.gross_amount end) -
sum(case when b.status not IN('cancelled', 'payment_failed', 'withdrawn', 'rejected', 'accepted') then b.net_amount end) as coupon,
ROUND((EXTRACT(EPOCH FROM b.booking_end_at) - EXTRACT(EPOCH FROM b.booking_start_at))/3600) as dur,
l.status as listing_status, cu.id as cuid, li.insurance_package_id as insurance_id,
CASE WHEN cu.id IS NOT NULL THEN 'Fleet' ELSE 'Individual' END listing_type,
CASE WHEN li.insurance_package_id=2 THEN 'Trevo-Shield' ELSE 'Normal' END insurance_package
from bookings b
left join listings l on l.id = b.listing_id
LEFT JOIN company_users cu ON cu.user_id=l.user_id
LEFT join listing_insurance li on li.listing_id = l.id
where b.status not IN('cancelled', 'payment_failed', 'withdrawn', 'rejected', 'accepted')
group by 1,l.status,cuid,li.insurance_package_id) as I
group by 1,2
order by 1 asc) Rev
on Rev.Aweek = Hos.Aweek and Rev.Ayear = Hos.Ayear
order by 1,37 asc