SHARE
TWEET

Trevo Weekly KPI

lowchi-e Dec 3rd, 2019 84 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. select
  2. A.AWeek,
  3. A.Signups,
  4. A.Hosts,
  5. A.Cars,
  6. A.CarswithDocUploaded,
  7. A.CarsApproved,
  8. B.FulfilledBookings,
  9. B.AcceptedBookings,
  10. B.CompletedBookings,
  11. B.gross_amount,
  12. B.comp_gross
  13. from
  14. (select
  15. extract('week' from u.created_at) as AWeek,
  16. count(distinct u.id) as Signups,
  17. count(distinct l.user_id) as Hosts,
  18. count(distinct l.id) as Cars,
  19. count(distinct case when l.status = 'approved' then l.id end) as CarsApproved,
  20. count(distinct ld.listing_id) as CarswithDocUploaded
  21. from users u
  22. left outer join listings l
  23. on l.user_id = u.id
  24. left outer join listing_documents ld
  25. on ld.listing_id = l.id
  26. group by 1) A
  27. join (
  28. select
  29. extract('week' from b.created_at) as BWeek,
  30. count(distinct case when b.status not in ('cancelled', 'payment_failed', 'withdrawn', 'rejected') then b.id end) as FulfilledBookings,
  31. count(distinct case when b.status  in ('accepted','completed') then b.id end) as AcceptedBookings,
  32. count(distinct case when b.status = 'completed' then b.id end) as CompletedBookings,
  33. sum(case when b.status not IN('cancelled', 'payment_failed', 'withdrawn', 'rejected') then b.gross_amount end) as gross_amount,
  34. sum(case when b.status not IN('cancelled', 'payment_failed', 'withdrawn', 'rejected', 'accepted') then b.gross_amount end) as comp_gross
  35. from bookings b
  36. group by 1) B
  37. on B.Bweek = A.Aweek
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top