TTT - KID Query

/* All data are in "Month". CTRL+F to replace to "Week" and "IYYY-IW" */

select ren.month, ren.signup, ren.doc_upload, ren.doc_approve, ren.MFT, ren.active_renter, hos.signup, hos.MFT, hos.active_host,
--
lis.cumulative_cars, lis.listed_cars, lis.ind_shield_cars, lis.ind_basic_cars, lis.fleet_cars, lis.available_cars, lis.available_shield_cars,
lis.available_basic_cars, lis.available_fleet_cars, lis.active_cars, lis.active_shield_cars, lis.active_basic_cars, lis.active_fleet_cars,
--
car.signup, car.doc_upload, car.doc_approve, car.MFT,
--
res.requested_booking, res.fulfilled_booking, res.unfulfilled_booking, res.rejected_booking, res.rejected_auto_booking, res.cancel_withdraw_booking,
res.pay_fail_booking, res.came_back, res.completed_booking, res.ind_shield_resv, res.ind_basic_resv, res.fleet_resv, res.D2D, res.non_D2D,
--
dis.total_dist, dis.ind_shield_dist, dis.ind_basic_dist, dis.fleet_dist, dis.total_dur, dis.ind_shield_dur, dis.ind_basic_dur, dis.fleet_dur,
dis.total_adj_dur,  dis.ind_shield_adj_dur, dis.ind_basic_adj_dur, dis.fleet_adj_dur,
--
rev.total_gross, rev.ind_shield_gross, rev.ind_basic_gross, rev.fleet_gross, rev.total_coupon, rev.ind_shield_coupon,
rev.ind_basic_coupon, rev.fleet_coupon, rev.total_net, rev.ind_shield_net, rev.ind_basic_net, rev.fleet_net,
rev.payout_host, rev.real_gross, rev.real_net from
--------------------------------------------
--
----> Renters
--
--------------------------------------------
(select A.month, A.signup, C.doc_upload, C.doc_approve,
D.MFT, E.active_renter from
(select to_char(u.created_at, 'YYYY-MM') as month,
count(distinct u.id) as signup
from users u
group by 1) A
left join
(select to_char(B.overall_date, 'YYYY-MM') as month,
count(distinct case when B.upload_status = 'Yes' then B.id end) as doc_upload,
count(distinct case when B.upload_status = 'Yes' and B.approve_status = 'Yes' then B.id end) as doc_approve from
(select u.created_at, u.id, selfic.status, selfic.date, license.status, license.date, ic.status, ic.date,
case when ic.date > '2020-04-05' and selfic.status is not null and license.status is not null and ic.status is not null then 'Yes'
when selfic.date < '2020-04-06' and selfic.status is not null and license.status is not null then 'Yes'
else 'No' end as upload_status,
case when selfic.status is null and license.status is null and ic.status is null then 'null'
when selfic.date < '2020-04-06' and license.date < '2020-04-06' and selfic.status = 'approved' and license.status = 'approved' then 'Yes'
when ic.date > '2020-04-05' and selfic.status = 'approved' and license.status = 'approved' and ic.status = 'approved' then 'Yes'
else 'No' end as approve_status,
case when (selfic.date > license.date or license.date is null) and (selfic.date > ic.date or ic.date is null) then selfic.date
when (license.date > selfic.date or selfic.date is null) and (license.date > ic.date or ic.date is null) then license.date
when (ic.date > selfic.date or selfic.date is null) and (ic.date > license.date or license.date is null) then ic.date end as overall_date
from users u
left join
(select k.user_id, k.status, k.created_at as date from user_kyc as k
join kyc_types as t on k.kyc_type_id = t.id
where t.slug = 'selfie-ic-passport') as selfic on u.id = selfic.user_id
left join
(select k.user_id, k.status, k.created_at as date from user_kyc as k
join kyc_types as t on k.kyc_type_id = t.id
where t.slug = 'driver-license') as license on u.id = license.user_id
left join
(select k.user_id, k.status, k.created_at as date from user_kyc as k
join kyc_types as t on k.kyc_type_id = t.id
where t.slug = 'ic-passport') as ic on u.id = ic.user_id) B
where B.overall_date is not null
group by 1) C
on A.month = C.month
left join
(SELECT to_char(b.request_start_at, 'YYYY-MM') as month, count(b.id) as MFT FROM bookings b
JOIN users u ON b.user_id = u.id
WHERE b.id IN (SELECT min(id) FROM bookings WHERE status IN ('completed') GROUP BY user_id)
GROUP BY 1) D
on A.month = D.month
left join
(select to_char(b.request_start_at, 'YYYY-MM') as month,
count(distinct case when b.status = 'completed' then b.user_id end) as active_renter
from bookings b
group by 1) E
on A.month = E.month
order by 1 desc) ren
--------------------------------------------
--
----> Hosts
--
--------------------------------------------
left join
(select B.month, B.signup, C.MFT, D.active_host from
(select to_char(A.created_at, 'YYYY-MM') as month, count(distinct A.user_id) as signup from
(SELECT tmp.user_id, tmp.created_at FROM
(SELECT l.user_id, l.created_at, ROW_NUMBER() OVER (PARTITION BY (l.user_id) ORDER BY created_at) rn
FROM listings l) tmp
WHERE rn = 1) A
group by 1) B
left join
(SELECT to_char(b.request_start_at, 'YYYY-MM') as month, count(b.id) as MFT FROM bookings b
JOIN listings l ON b.listing_id = l.id
WHERE b.id in (SELECT min(b.id) FROM bookings b
join listings l on b.listing_id = l.id
WHERE b.status IN ('completed') GROUP BY l.user_id)
GROUP BY 1) C
on B.month = C.month
left join
(select to_char(b.request_start_at, 'YYYY-MM') as month,
count(distinct case when b.status = 'completed' then l.user_id end) as active_host from listings l
join bookings b on b.listing_id = l.id
group by 1) D
on B.month = D.month
order by 1 desc) hos
on ren.month = hos.month
--------------------------------------------
--
----> Listings (Component 1)
--
--------------------------------------------
left join
(select E.month, E.cumulative_cars as cumulative_cars,
sum(L.listed_cars) over (rows between unbounded preceding and current row) as listed_cars,
sum(L.ind_shield_cars) over (rows between unbounded preceding and current row) as ind_shield_cars,
sum(L.ind_basic_cars) over (rows between unbounded preceding and current row) as ind_basic_cars,
sum(L.fleet_cars) over (rows between unbounded preceding and current row) as fleet_cars,
E.available_cars as available_cars, E.ind_shield_cars as available_shield_cars,
E.ind_basic_cars as available_basic_cars, E.fleet_cars as available_fleet_cars,
G.active_cars as active_cars, G.ind_shield_cars as active_shield_cars,
G.ind_basic_cars as active_basic_cars, G.fleet_cars as active_fleet_cars
from
(select B.month, case when sum(D.remove_cum_cars) is null then sum(B.cum_cars) else
sum(B.cum_cars) - sum(D.remove_cum_cars) end as cumulative_cars,
case when sum(D.remove_avai_cars) is null then sum(B.avai_cars)
else sum(B.avai_cars) - sum(D.remove_avai_cars) end as available_cars,
case when sum(D.remove_ind_shield_cars) is null then sum(B.avai_ind_shield_cars)
else sum(B.avai_ind_shield_cars) - sum(D.remove_ind_shield_cars) end as ind_shield_cars,
case when sum(D.remove_ind_basic_cars) is null then sum(B.avai_ind_basic_cars)
else sum(B.avai_ind_basic_cars) - sum(D.remove_ind_basic_cars) end as ind_basic_cars,
case when sum(D.remove_fleet_cars) is null then sum(B.avai_fleet_cars)
else sum(B.avai_fleet_cars) - sum(D.remove_fleet_cars) end as fleet_cars from
(select to_char(A.date, 'YYYY-MM') as month, count(A.lid) as cum_cars, count(distinct A.lid) as avai_cars,
count(distinct case when A.category = 'Individual (Trevo-Shield)' then A.lid end) as avai_ind_shield_cars,
count(distinct case when A.category = 'Individual (Basic)' then A.lid end) as avai_ind_basic_cars,
count(distinct case when A.category = 'Fleet' then A.lid end) as avai_fleet_cars from
(select distinct l.id as lid, dates.ymd as date,
case when cu.id is not null then 'Fleet'
when cu.id is null and li.insurance_package_id = 2 then 'Individual (Trevo-Shield)'
else 'Individual (Basic)' end category
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
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') A
group by 1) B
left join
(select to_char(C.date, 'YYYY-MM') as month, count(C.lid) as remove_cum_cars, count(distinct C.lid) as remove_avai_cars,
count(distinct case when C.category = 'Individual (Trevo-Shield)' then C.lid end) as remove_ind_shield_cars,
count(distinct case when C.category = 'Individual (Basic)' then C.lid end) as remove_ind_basic_cars,
count(distinct case when C.category = 'Fleet' then C.lid end) as remove_fleet_cars from
(select distinct l.id as lid, dates.ymd as date,
case when cu.id is not null then 'Fleet'
when cu.id is null and li.insurance_package_id = 2 then 'Individual (Trevo-Shield)'
else 'Individual (Basic)' end category
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 l.deleted_at::date and la.end_date::date
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 la.start_date <= dates.ymd) C
group by 1) D
on B.month = D.month
group by 1) E
left join
(select F.month, count(distinct F.car_id) as active_cars,
count(distinct case when F.category = 'Individual (Trevo-Shield)' then F.car_id end) as ind_shield_cars,
count(distinct case when F.category = 'Individual (Basic)' then F.car_id end) as ind_basic_cars,
count(distinct case when F.category = 'Fleet' then F.car_id end) as fleet_cars from
(select distinct to_char(b.request_start_at, 'YYYY-MM') as month, b.listing_id as car_id,
case when cu.id is not null then 'Fleet'
when cu.id is null and li.insurance_package_id = 2 then 'Individual (Trevo-Shield)'
else 'Individual (Basic)' end category
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 = 'completed') F
group by 1) G
on E.month = G.month
left join
(select case when I.month is null then K.month else I.month end as month,
case when sum(K.remove_cars) is null then sum(I.all_cars)
when sum(I.all_cars) is null then - sum(K.remove_cars)
else sum(I.all_cars) - sum(K.remove_cars) end as listed_cars,
case when sum(K.remove_ind_shield_cars) is null then sum(I.all_ind_shield_cars)
when sum(I.all_ind_shield_cars) is null then - sum(K.remove_ind_shield_cars)
else sum(I.all_ind_shield_cars) - sum(K.remove_ind_shield_cars) end as ind_shield_cars,
case when sum(K.remove_ind_basic_cars) is null then sum(I.all_ind_basic_cars)
when sum(I.all_ind_basic_cars) is null then - sum(K.remove_ind_basic_cars)
else sum(I.all_ind_basic_cars) - sum(K.remove_ind_basic_cars) end as ind_basic_cars,
case when sum(K.remove_fleet_cars) is null then sum(I.all_fleet_cars)
when sum(I.all_fleet_cars) is null then - sum(K.remove_fleet_cars)
else sum(I.all_fleet_cars) - sum(K.remove_fleet_cars) end as fleet_cars from
(select H.month, count(distinct H.lid) as all_cars,
count(distinct case when H.category = 'Individual (Trevo-Shield)' then H.lid end) as all_ind_shield_cars,
count(distinct case when H.category = 'Individual (Basic)' then H.lid end) as all_ind_basic_cars,
count(distinct case when H.category = 'Fleet' then H.lid end) as all_fleet_cars from
(select l.id as lid, to_char(l.created_at, 'YYYY-MM') as month,
case when cu.id is not null then 'Fleet'
when cu.id is null and li.insurance_package_id = 2 then 'Individual (Trevo-Shield)'
else 'Individual (Basic)' end category
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') H
group by 1) I
full join
(select J.month, count(distinct J.lid) as remove_cars,
count(distinct case when J.category = 'Individual (Trevo-Shield)' then J.lid end) as remove_ind_shield_cars,
count(distinct case when J.category = 'Individual (Basic)' then J.lid end) as remove_ind_basic_cars,
count(distinct case when J.category = 'Fleet' then J.lid end) as remove_fleet_cars from
(select l.id as lid, to_char(l.deleted_at, 'YYYY-MM') as month,
case when cu.id is not null then 'Fleet'
when cu.id is null and li.insurance_package_id = 2 then 'Individual (Trevo-Shield)'
else 'Individual (Basic)' end category
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 not null) J
group by 1) K
on I.month = K.month
group by 1
order by 1) L
on E.month = L.month
order by 1 desc) lis
on ren.month = lis.month
--------------------------------------------
--
----> Listings (Component 2)
--
--------------------------------------------
left join
(select A.month, A.signup, C.doc_upload, A.doc_approve, D.MFT from
(select to_char(l.created_at, 'YYYY-MM') as month, count(distinct l.id) as signup,
count(distinct case when l.status = 'approved' then l.id end) as doc_approve
from listings l
group by 1) A
left join
(select to_char(B.created_at, 'YYYY-MM') as month,
count(distinct case when B.upload_status = 'Yes' then B.id end) as doc_upload from
(select l.created_at, l.id, gran.status, gran.date, tax.status, tax.date,
case when gran.status is not null and tax.status is not null then 'Yes' else 'No' end as upload_status,
case when gran.status is null and tax.status is null then 'null'
when gran.status = 'approved' and tax.status = 'approved' then 'Yes'
else 'No' end as approve_status
from listings l
left join
(select ld.listing_id, ld.status, ld.created_at as date from listing_documents ld
join listing_document_types as t on ld.listing_document_type_id = t.id
where t.slug = 'car-grant') as gran on l.id = gran.listing_id
left join
(select ld.listing_id, ld.status, ld.created_at as date from listing_documents ld
join listing_document_types as t on ld.listing_document_type_id = t.id
where t.slug = 'road-tax') as tax on l.id = tax.listing_id) B
group by 1) C
on A.month = C.month
left join
(SELECT to_char(b.request_start_at, 'YYYY-MM') as month, count(b.id) as MFT FROM bookings b
JOIN listings l ON b.listing_id = l.id
WHERE b.id IN (SELECT min(id) FROM bookings WHERE status IN ('completed') GROUP BY listing_id)
GROUP BY 1) D
on A.month = D.month
order by 1 desc) car
on ren.month = car.month
--------------------------------------------
--
----> Reservations
--
--------------------------------------------
left join
(select A.month, A.unfulfilled_booking + A.accepted_booking + C.completed_booking as requested_booking,
A.accepted_booking + C.completed_booking as fulfilled_booking,
A.unfulfilled_booking, A.rejected_booking, A.rejected_auto_booking, A.cancel_withdraw_booking,
A.pay_fail_booking, E.came_back, C.completed_booking, C.ind_shield_resv, C.ind_basic_resv, C.fleet_resv,
C.D2D, C.non_D2D from
(select to_char(b.created_at, 'YYYY-MM') as month,
count(distinct case when b.status in ('cancelled', 'withdrawn', 'rejected') then b.id end) as unfulfilled_booking,
count(distinct case when b.status = 'rejected' and (b.reason not in ('auto-rejected') or b.reason is null) then b.id end) as rejected_booking,
count(distinct case when b.reason = 'auto-rejected' then b.id end) as rejected_auto_booking,
count(distinct case when b.status in ('cancelled', 'withdrawn') then b.id end) as cancel_withdraw_booking,
count(distinct case when b.status = 'payment_failed' then b.id end) as pay_fail_booking,
count(distinct case when b.status = 'accepted' then b.id end) as accepted_booking
from bookings b
group by 1) A
left join
(select B.month, count(distinct B.bid) as completed_booking,
count(distinct case when B.category = 'Individual (Trevo-Shield)' then B.bid end) as ind_shield_resv,
count(distinct case when B.category = 'Individual (Basic)' then B.bid end) as ind_basic_resv,
count(distinct case when B.category = 'Fleet' then B.bid end) as fleet_resv,
count(distinct B.bdid) as D2D, count(distinct B.bid) - count(distinct B.bdid) as non_D2D from
(select distinct to_char(b.request_start_at, 'YYYY-MM') as month,
b.id as bid, bd.id as bdid, case when cu.id is not null then 'Fleet'
when cu.id is null and li.insurance_package_id = 2 then 'Individual (Trevo-Shield)'
else 'Individual (Basic)' end category
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
left join booking_deliveries bd on bd.booking_id = b.id
where b.status = 'completed') B
group by 1) C
on A.month = C.month
left join
(select to_char(D.s_created_at, 'YYYY-MM') as month, count(D.user_id) as came_back from
(select s.user_id, min(f.payment_id) as failure_payment_id, min(f.created_at) as f_created_at,
s.payment_id as success_payment_id, s.created_at as s_created_at from
(select pm.user_id, p.id as payment_id, p.status, p.created_at from payments p
join payment_methods pm on pm.id = p.payment_method_id
where p.status= 'failed') f
join
(select pm.user_id, p.id as payment_id, p.status, p.created_at from payments p
join payment_methods pm on pm.id = p.payment_method_id
where p.status='succeeded') s
on f.user_id = s.user_id and s.created_at between f.created_at and f.created_at + interval '30 minutes'
group by s.user_id, s.payment_id, s.created_at) D
group by 1) E
on A.month = E.month
order by 1 desc) res
on ren.month = res.month
--------------------------------------------
--
----> Distance & Time
--
--------------------------------------------
left join
(select A.month,
sum(A.dist) as total_dist,
sum(case when A.category = 'Individual (Trevo-Shield)' then A.dist end) as ind_shield_dist,
sum(case when A.category = 'Individual (Basic)' then A.dist end) as ind_basic_dist,
sum(case when A.category = 'Fleet' then A.dist end) as fleet_dist,
sum(A.dur) as total_dur,
sum(case when A.category = 'Individual (Trevo-Shield)' then A.dur end) as ind_shield_dur,
sum(case when A.category = 'Individual (Basic)' then A.dur end) as ind_basic_dur,
sum(case when A.category = 'Fleet' then A.dur end) as fleet_dur,
sum(ceil(A.dur)) as total_adj_dur,
sum(case when A.category = 'Individual (Trevo-Shield)' then ceil(A.dur) end) as ind_shield_adj_dur,
sum(case when A.category = 'Individual (Basic)' then ceil(A.dur) end) as ind_basic_adj_dur,
sum(case when A.category = 'Fleet' then ceil(A.dur) end) as fleet_adj_dur from
(select distinct to_char(b.request_start_at, 'YYYY-MM') as month, b.id as bid,
case when cu.id is not null then 'Fleet'
when cu.id is null and li.insurance_package_id = 2 then 'Individual (Trevo-Shield)'
else 'Individual (Basic)' end category,
(EXTRACT(EPOCH FROM b.request_end_at) - EXTRACT(EPOCH FROM b.request_start_at))/86400 as dur,
case when m2.end - m1.start between '0' and '2000' then m2.end - m1.start Else 0 End as dist
from bookings b
left join
(select bi.booking_id as booking_id, bi.mileage as start from booking_inspections bi where bi.type='start') m1
on b.id = m1.booking_id
left join
(select bi.booking_id as booking_id, bi.mileage as end from booking_inspections bi where bi.type= 'end') m2
on b.id = m2.booking_id
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 = 'completed') A
group by 1
order by 1 desc) dis
on ren.month = dis.month
--------------------------------------------
--
----> Revenue (GMV & Real)
--
--------------------------------------------
left join
(select A.month,
sum(A.comp_gross) as total_gross,
sum(case when A.category = 'Individual (Trevo-Shield)' then A.comp_gross end) as ind_shield_gross,
sum(case when A.category = 'Individual (Basic)' then A.comp_gross end) as ind_basic_gross,
sum(case when A.category = 'Fleet' then A.comp_gross end) as fleet_gross,
sum(A.coupon) as total_coupon,
sum(case when A.category = 'Individual (Trevo-Shield)' then A.coupon end) as ind_shield_coupon,
sum(case when A.category = 'Individual (Basic)' then A.coupon end) as ind_basic_coupon,
sum(case when A.category = 'Fleet' then A.coupon end) as fleet_coupon,
sum(A.comp_net) as total_net,
sum(case when A.category = 'Individual (Trevo-Shield)' then A.comp_net end) as ind_shield_net,
sum(case when A.category = 'Individual (Basic)' then A.comp_net end) as ind_basic_net,
sum(case when A.category = 'Fleet' then A.comp_net end) as fleet_net,
sum(A.payout_amount) as payout_host, sum(A.real_gross) as real_gross, sum(A.real_net) as real_net from
(select distinct to_char(b.request_start_at, 'YYYY-MM') as month, b.id as bid,
case when cu.id is not null then 'Fleet'
when cu.id is null and li.insurance_package_id = 2 then 'Individual (Trevo-Shield)'
else 'Individual (Basic)' end category,
sum(case when b.status = 'completed' then b.gross_amount end) as comp_gross,
sum(case when b.status = 'completed' then b.net_amount end) as comp_net,
sum(case when b.status = 'completed' then b.gross_amount end) -
sum(case when b.status = 'completed' then b.net_amount end) as coupon,
sum(po.amount) as payout_amount, sum(b.gross_amount-po.amount) as real_gross, sum(b.net_amount-po.amount) as real_net
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
left join payouts po ON po.booking_id = b.id
where b.status = 'completed'
group by 1, 2, 3) A
group by 1
order by 1 desc) rev
on ren.month = rev.month
order by 1 desc

-------
----------------
----------------------------
/* Unique User (Yearly) */
select C.year, C.active_renter, D.active_host from
(select extract('year' from b.request_start_at) as year,
count(distinct case when b.status = 'completed' then b.user_id end) as active_renter
from bookings b
group by 1) C
left join
(select extract('year' from b.request_start_at) as year,
count(distinct case when b.status = 'completed' then l.user_id end) as active_host from listings l
join bookings b on b.listing_id = l.id
group by 1) D
on C.year = D.year