Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- select
- A.month,
- ca.CarsApproved as cars,
- B.CompletedBookings as Bookings,
- A.Signups,
- C.MFTs as first_rent,
- B.Active as active,
- B.comp_gross as GMV,
- AV.ten,
- AV.eleven,
- H.Active_hosts as active_Cars
- from
- (select
- extract('month' from u.created_at) as month,
- count(distinct u.id) as Signups,
- count(distinct l.user_id) as Hosts,
- count(distinct l.id) as Cars,
- count(distinct case when l.status = 'approved' then l.id end) as CarsApproved,
- count(distinct ld.listing_id) as CarswithDocUploaded
- from users u
- left outer join listings l
- on l.user_id = u.id
- left outer join listing_documents ld
- on ld.listing_id = l.id
- group by 1) A
- join (
- select
- extract('month' from b.created_at) as month,
- count(distinct case when b.status = 'completed' then b.user_id end) as Active,
- count(distinct case when b.status != 'cancelled' then b.id end) as NonCancelledBookings,
- count(distinct case when b.status = 'accepted' then b.id end) as AcceptedBookings,
- count(distinct case when b.status = 'completed' then b.id end) as CompletedBookings,
- sum(case when b.status not IN('cancelled', 'payment_failed', 'withdrawn', 'rejected', 'accepted') then b.gross_amount end) as comp_gross
- from bookings b
- group by 1) B
- on B.month = A.month
- left join
- (
- select A.month as month, count(distinct case when B.first_res = A.bookingid then A.userid end) as MFTs from
- ((select distinct b.id as bookingid, extract('month' from b.created_at) as month, 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 ='completed'
- group by userid
- order by 2 asc) B
- on B.userid = A.userid)
- group by A.month) C
- on C.month = A.month
- left join
- (select
- extract('month' from uk.created_at) as month,
- count(distinct case when uk.status = 'approved' then uk.id end) as doc_approved
- from user_kyc uk
- group by 1) D
- on D.month = A.month
- left join
- (select
- extract('month' from l.created_at) as month,
- count(distinct case when l.status = 'approved' then l.id end) as CarsApproved
- from listings l
- group by month) ca
- on ca.month = A.month
- left join
- (select A.month as month,
- count(distinct case when A.month = '10' then A.lid end) as ten,
- count(distinct case when A.month = '11' then A.lid end) as eleven,
- count(distinct case when A.month = '12' then A.lid end) as twelve
- from
- (SELECT distinct la.listing_id as lid, extract('month' from la.start_date) as month
- FROM listing_availability la
- order by 1 asc) A
- group by 1) AV
- on AV.month = A.month
- left join
- (select
- extract('month' from b.created_at) as Amonth,
- count(distinct case when b.status in ('accepted','completed') then b.listing_id end) as Active_hosts
- from bookings b
- group by 1) H
- on H.Amonth = A.month
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement