TTT - Cars Infra - Add In search- Look Later - Rahil

select A.week as week,
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