TTT (DAILY) - RENTERS

select S.date as date, S.signup as renter_Signup, D.DocUploaded as DocUploaded,
D.DocApproved as DocApproved, (D.DocUploaded - D.DocApproved) as DocRejected,
F.MFTs as MFT_renter, H.Active_renters as Active_renters , P.bookings as bookings  from
(select
date_trunc('day', u.created_at)::date as date,
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
date_trunc('day', uk.created_at)::date as date,
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.date = d.date
left join
(select
date_trunc('day', b.created_at)::date as date,
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.date = S.date
left join
(select  A.date as date, count(distinct case when B.first_res = A.bookingid then A.userid end) as MFTs from
((select distinct b.id as bookingid,
date_trunc('day', b.created_at)::date as date,
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 not IN('cancelled', 'payment_failed', 'withdrawn', 'rejected', 'accepted')
group by userid
order by 2 asc) B
on B.userid = A.userid)
group by A.date) F
on F.date = S.date
left join
(select
date_trunc('day', b.created_at)::date as date,
count(distinct b.id) as bookings
from bookings b
where b.status not IN ('cancelled', 'payment_failed', 'withdrawn', 'rejected', 'accepted')
group by 1
order by 1 desc) P
on S.date = P.date
order by 1 desc