TTT -Weekly-Rahil

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
from

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

(select S.Aweek as Aweek, 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,
count(distinct case when u.status not in ('null') then u.id end) as signup
from users u
group by 1) S
left join
(select
extract('week' from uk.created_at) as Aweek,
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) D
on S.Aweek = D.Aweek
left join
(select
extract('week' from b.created_at) as Aweek,
count(distinct case when b.status in ('completed') then b.user_id end) as Active_renters
from bookings b
group by 1) H
on H.Aweek = S.Aweek
left join
(select  A.Aweek as Aweek, 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,  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) F
on F.Aweek = S.Aweek ) mem

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

left join

(select S.Aweek as Aweek, 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,
count(distinct case when l.status not in ('null') then l.id end) as signup
from listings l
group by 1) S
left join
(select
extract('week' from ld.created_at) as Aweek,
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) D
on S.Aweek = D.Aweek
left join
(select
extract('week' from b.created_at) as Aweek,
count(distinct case when b.status in ('completed') then b.listing_id end) as Active_hosts
from bookings b
group by 1) H
on H.Aweek = S.Aweek
left join
(select  A.Aweek as Aweek, 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,  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) F
on F.Aweek = S.Aweek) Hos
on Hos.Aweek = mem.Aweek

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

left join
(select A.week as Aweek,
Count(distinct A.lid) as cars_listed,
count(distinct case when (A.listing_type = 'Individual' and A.insurance_package = 'Trevo-Shield') then A.lid end) as Individual_trevo_Sheild_Cars,
count(distinct case when (A.listing_type = 'Individual' and A.insurance_package = 'Normal') then A.lid end) as Individual_no_insurance_Cars,
count(distinct case when A.listing_type = 'Fleet' then A.lid end) as 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 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,
extract('week' from l.created_at) as week
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) A
left join
(select I.Aweek as Aweek,
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,
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,l.status,cuid,li.insurance_package_id) as I
group by 1
order by 1 desc)B
on A.week = B.Aweek
group by A.week, B.Individual_trevo_Sheild_cars,B.Individual_no_insurance_cars,B.Fleet_cars) cars
on cars.Aweek = Hos.Aweek

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

left join
(select I.Aweek as Aweek,
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,
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
order by 1 asc) Rev
on Rev.Aweek = Hos.Aweek
order by 1 asc