TTT - REV + RESV - New V2.0

select I.Amonth as Amonth,
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('month' from b.created_at) as Amonth,
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