TTT (DAILY) - REVENUES

select cars.date, cars.cars_P_Day, booking.Bookings , (booking.Bookings -booking.D2D) as not_D2D , booking.D2D, booking.unique_cars, booking.nmembers,
distance.distance as distance, booking.dur as duration, booking.comp_gross as gross_rev, booking.comp_net as net_rev, booking.coupon as coupon_rev
from
(select 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-10-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 dates.ymd
order by dates.ymd desc) cars
left join
(Select B.date as date, Count(distinct B.bid) as Bookings, Count(distinct B.bdid) as D2D, SUM(B.dur) as dur, Count(distinct B.car_id) as unique_cars,
Count(distinct B.users) as nmembers,
Sum(B.comp_gross) as comp_gross, sum(comp_net) as comp_net, sum(coupon) as coupon from
(select
distinct b.id as bid, bd.delivery_id as bdid,
date_trunc('day', b.request_end_at)::date as date,
b.listing_id as car_id,b.user_id as users,
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
from bookings b
left join booking_deliveries bd on bd.booking_id = b.id
where b.status not IN('cancelled', 'payment_failed', 'withdrawn', 'rejected', 'accepted')
group by 1,2
order by 1 desc) B
group by 1
order by 1 desc) booking
on booking.date = cars.date
left join
(
select L.date as date, Sum(L.distance) as distance from
(select distinct b.id, date_trunc('day', b.created_at)::date as date,
Case when m2.ends - m1.start between '0' and '1000' then m2.ends - m1.start Else 0 End as distance
from
bookings b left join
(select bi.booking_id as bookingid, bi.mileage as start from booking_inspections bi  where bi.type='start') m1
on b.id = m1.bookingid
left join
(select bi.booking_id as bookingid, bi.mileage as ends from booking_inspections bi  where bi.type='end') m2
on m1.bookingid = m2.bookingid
where b.status not IN('cancelled', 'payment_failed', 'withdrawn', 'rejected', 'accepted')
and b.id is not null
and b.booking_start_at is not null
order by 1 desc)L
group by 1
order by 1 desc) distance
on distance.date = booking.date
group by 1,2,3,4,5,6,7,8,9,10,11,12
order by 1 desc