API tools faq
paste
ahmedrahil786

TTT - (Automate) Monthly - Order by Year

ahmedrahil786
Dec 29th, 2019
210
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 11.18 KB | None | 0 0
raw download clone embed print report
  1. select mem.Amonth,mem.hosts_Signup as mem_Signup,mem.DocUploaded as mem_DocUploaded,mem.DocApproved as mem_DocApproved,mem.renter_First_Reservation,mem.Active_renters,
  2. Hos.hosts_Signup,Hos.DocUploaded as hos_Signup,Hos.DocUploaded as Hos_DocUploaded,Hos.DocApproved as hos_DocUploaded,Hos.Host_First_Rent,Hos.Active_Hosts,
  3. cars.cars_listed, cars.Individual_trevo_Sheild_Cars, cars.Individual_no_insurance_Cars,cars.Fleet_Cars,cars.Active_Trevosheild_Cars,cars.Active_NO_Trevosheild_Cars,cars.Active_Fleet_Cars,
  4. Rev.Reservations,Rev.Individual_trevo_Sheild_Resv, Rev.Individual_no_insurance_resv, Rev.Fleet_resv,Rev.Individual_trevo_Sheild_dur,Rev.Individual_no_insurance_dur,Rev.Fleet_dur,
  5. Rev.comp_gross,Rev.Individual_trevo_Sheild_comp_gross,Rev.Individual_no_insurance_comp_gross,Rev.Fleet_comp_gross,Rev.Individual_trevo_Sheild_comp_net,Rev.Individual_no_insurance_comp_net,
  6. Rev.Fleet_comp_net,Rev.Individual_trevo_Sheild_coupon,Rev.Individual_no_insurance_coupon,Rev.Fleet_coupon, mem.Ayear
  7. from
  8.  
  9. /*################################### Members */
  10.  
  11. (select S.Amonth as Amonth, S.Ayear as Ayear, S.signup as hosts_Signup,D.DocUploaded as DocUploaded, D.DocApproved as DocApproved, F.MFTs as renter_First_Reservation, H.Active_renters as Active_renters from
  12. (select
  13. extract('month' from u.created_at) as Amonth,
  14. extract('year' from u.created_at) as Ayear,
  15. count(distinct case when u.status not in ('null') then u.id end) as signup
  16. from users u
  17. group by 1,2) S
  18. left join
  19. (select
  20. extract('month' from uk.created_at) as Amonth,
  21. extract('year' from uk.created_at) as Ayear,
  22. count(distinct case when uk.status not in ('null') then uk.user_id end) DocUploaded,
  23. count(distinct case when uk.status = 'approved' then uk.user_id end) DocApproved
  24. from user_kyc uk
  25. group by 1,2) D
  26. on S.Amonth = D.Amonth and S.Ayear = D.Ayear
  27. left join
  28. (select
  29. extract('month' from b.created_at) as Amonth,
  30. extract('year' from b.created_at) as Ayear,
  31. count(distinct case when b.status in ('completed') then b.user_id end) as Active_renters
  32. from bookings b
  33. group by 1,2) H
  34. on H.Amonth = S.Amonth and H.Ayear = S.Ayear
  35. left join
  36. (select A.Amonth as Amonth, A.Ayear as Ayear, count(distinct case when B.first_res = A.bookingid then A.userid end) as MFTs from
  37. ((select distinct b.id as bookingid, extract('month' from b.created_at) as Amonth, extract('year' from b.created_at) as Ayear, b.user_id userid
  38. from bookings b left join users u on u.id = b.user_id
  39. where b.status = 'completed'
  40. group by b.id
  41. order by b.id desc) A
  42. left join
  43. (select b.user_id as userid , min(b.id) as first_res
  44. from bookings b where b.status ='completed'
  45. group by userid
  46. order by 2 asc) B
  47. on B.userid = A.userid)
  48. group by A.Amonth,A.Ayear) F
  49. on F.Amonth = S.Amonth and F.Ayear = S.Ayear ) mem
  50.  
  51. /*################################### Hosts */
  52.  
  53. left join
  54.  
  55. (select S.Amonth as Amonth, S.Ayear as Ayear, S.signup as hosts_Signup,D.DocUploaded as DocUploaded , D.DocApproved as DocApproved, F.MFTs as Host_First_Rent, H.Active_Hosts as Active_Hosts from
  56. (select
  57. extract('month' from l.created_at) as Amonth,
  58. extract('year' from l.created_at) as Ayear,
  59. count(distinct case when l.status not in ('null') then l.id end) as signup
  60. from listings l
  61. group by 1,2) S
  62. left join
  63. (select
  64. extract('month' from ld.created_at) as Amonth,
  65. extract('year' from ld.created_at) as Ayear,
  66. count(distinct case when ld.status not in ('null') then ld.listing_id end) DocUploaded,
  67. count(distinct case when ld.status = 'approved' then ld.listing_id end) DocApproved
  68. from listing_documents ld
  69. group by 1,2) D
  70. on S.Amonth = D.Amonth and S.Ayear = D.Ayear
  71. left join
  72. (select
  73. extract('month' from b.created_at) as Amonth,
  74. extract('year' from b.created_at) as Ayear,
  75. count(distinct case when b.status in ('completed') then b.listing_id end) as Active_hosts
  76. from bookings b
  77. group by 1,2) H
  78. on H.Amonth = S.Amonth and H.Ayear = S.Ayear
  79. left join
  80. (select A.Amonth as Amonth, A.Ayear as Ayear, count(distinct case when B.first_res = A.bookingid then B.listing_id end) as MFTs from
  81. ((select distinct b.id as bookingid, extract('month' from b.created_at) as Amonth, extract('year' from b.created_at) as Ayear, b.listing_id as listing_id
  82. from bookings b
  83. where b.status = 'completed'
  84. group by b.id
  85. order by b.id desc) A
  86. left join
  87. (select b.listing_id as listing_Id , min(b.id) as first_res
  88. from bookings b where b.status ='completed'
  89. group by b.listing_id
  90. order by 2 asc) B
  91. on B.listing_Id = A.listing_Id)
  92. group by A.Amonth,A.Ayear) F
  93. on F.Amonth = S.Amonth and F.Ayear = S.Ayear) Hos
  94. on Hos.Amonth = mem.Amonth and Hos.Ayear = mem.Ayear
  95.  
  96. /*################################### Cars */
  97.  
  98. left join
  99. (select A.month as Amonth,
  100. A.Ayear as Ayear,
  101. Count(distinct A.lid) as cars_listed,
  102. count(distinct case when (A.listing_type = 'Individual' and A.insurance_package = 'Trevo-Shield') then A.lid end) as Individual_trevo_Sheild_Cars,
  103. count(distinct case when (A.listing_type = 'Individual' and A.insurance_package = 'Normal') then A.lid end) as Individual_no_insurance_Cars,
  104. count(distinct case when A.listing_type = 'Fleet' then A.lid end) as Fleet_Cars,
  105. B.Individual_trevo_Sheild_cars as Active_Trevosheild_Cars,
  106. B.Individual_no_insurance_cars as Active_NO_Trevosheild_Cars,
  107. B.Fleet_cars as Active_Fleet_Cars
  108. from
  109. (select distinct l.id as lid,l.status as status,
  110. cu.id as cuid,
  111. li.insurance_package_id as insurance_id,
  112. CASE WHEN cu.id IS NOT NULL THEN 'Fleet' ELSE 'Individual' END listing_type,
  113. CASE WHEN li.insurance_package_id=2 THEN 'Trevo-Shield' ELSE 'Normal' END insurance_package,
  114. extract('month' from l.created_at) as month,
  115. extract('year' from l.created_at) as Ayear
  116. from listings l
  117. LEFT JOIN company_users cu ON cu.user_id=l.user_id
  118. LEFT join listing_insurance li on li.listing_id = l.id
  119. WHERE l.status='approved' AND l.deleted_at IS NULL) A
  120. left join
  121. (select I.Amonth as Amonth,
  122. I.Ayear as Ayear,
  123. count(I.bid) as Reservations,
  124. count(distinct case when (I.listing_type = 'Individual' and I.insurance_package = 'Trevo-Shield') then I.car_id end) as Individual_trevo_Sheild_cars,
  125. count(distinct case when (I.listing_type = 'Individual' and I.insurance_package = 'Normal') then I.car_id end) as Individual_no_insurance_cars,
  126. count(distinct case when I.listing_type = 'Fleet' then I.car_id end) as Fleet_cars
  127. from
  128. (select
  129. distinct b.id as bid,
  130. extract('month' from b.created_at) as Amonth,
  131. extract('year' from b.created_at) as Ayear,
  132. b.listing_id as car_id,
  133. sum(case when b.status not IN('cancelled', 'payment_failed', 'withdrawn', 'rejected', 'accepted') then b.gross_amount end) as comp_gross,
  134. sum(case when b.status not IN('cancelled', 'payment_failed', 'withdrawn', 'rejected', 'accepted') then b.net_amount end) as comp_net,
  135. sum(case when b.status not IN('cancelled', 'payment_failed', 'withdrawn', 'rejected', 'accepted') then b.gross_amount end) -
  136. sum(case when b.status not IN('cancelled', 'payment_failed', 'withdrawn', 'rejected', 'accepted') then b.net_amount end) as coupon,
  137. ROUND((EXTRACT(EPOCH FROM b.booking_end_at) - EXTRACT(EPOCH FROM b.booking_start_at))/3600) as dur,
  138. l.status as listing_status, cu.id as cuid, li.insurance_package_id as insurance_id,
  139. CASE WHEN cu.id IS NOT NULL THEN 'Fleet' ELSE 'Individual' END listing_type,
  140. CASE WHEN li.insurance_package_id=2 THEN 'Trevo-Shield' ELSE 'Normal' END insurance_package
  141. from bookings b
  142. left join listings l on l.id = b.listing_id
  143. LEFT JOIN company_users cu ON cu.user_id=l.user_id
  144. LEFT join listing_insurance li on li.listing_id = l.id
  145. where b.status not IN('cancelled', 'payment_failed', 'withdrawn', 'rejected', 'accepted')
  146. and b.booking_end_at is not null
  147. group by 1,2,l.status,cuid,li.insurance_package_id) as I
  148. group by 1,2
  149. order by 1 desc)B
  150. on A.month = B.Amonth and A.Ayear = B.Ayear
  151. group by A.month,A.Ayear, B.Individual_trevo_Sheild_cars,B.Individual_no_insurance_cars,B.Fleet_cars) cars
  152. on cars.Amonth = Hos.Amonth and cars.Ayear = Hos.Ayear
  153.  
  154. /*################################### Revenue and Reservations */
  155.  
  156. left join
  157. (select I.Amonth as Amonth,
  158. I.Ayear as Ayear,
  159. count(I.bid) as Reservations,
  160. count(distinct case when (I.listing_type = 'Individual' and I.insurance_package = 'Trevo-Shield') then I.bid end) as Individual_trevo_Sheild_Resv,
  161. count(distinct case when (I.listing_type = 'Individual' and I.insurance_package = 'Normal') then I.bid end) as Individual_no_insurance_resv,
  162. count(distinct case when I.listing_type = 'Fleet' then I.bid end) as Fleet_resv,
  163. sum(case when (I.listing_type = 'Individual' and I.insurance_package = 'Trevo-Shield') then I.dur end) as Individual_trevo_Sheild_dur,
  164. sum(case when (I.listing_type = 'Individual' and I.insurance_package = 'Normal') then I.dur end) as Individual_no_insurance_dur,
  165. sum(case when I.listing_type = 'Fleet' then I.dur end) as Fleet_dur,
  166. sum(I.comp_gross) as comp_gross,
  167. sum(case when (I.listing_type = 'Individual' and I.insurance_package = 'Trevo-Shield') then I.comp_gross end) as Individual_trevo_Sheild_comp_gross,
  168. sum(case when (I.listing_type = 'Individual' and I.insurance_package = 'Normal') then I.comp_gross end) as Individual_no_insurance_comp_gross,
  169. sum(case when I.listing_type = 'Fleet' then I.comp_gross end) as Fleet_comp_gross,
  170. sum(case when (I.listing_type = 'Individual' and I.insurance_package = 'Trevo-Shield') then I.comp_net end) as Individual_trevo_Sheild_comp_net,
  171. sum(case when (I.listing_type = 'Individual' and I.insurance_package = 'Normal') then I.comp_net end) as Individual_no_insurance_comp_net,
  172. sum(case when I.listing_type = 'Fleet' then I.comp_net end) as Fleet_comp_net,
  173. sum(case when (I.listing_type = 'Individual' and I.insurance_package = 'Trevo-Shield') then I.coupon end) as Individual_trevo_Sheild_coupon,
  174. sum(case when (I.listing_type = 'Individual' and I.insurance_package = 'Normal') then I.coupon end) as Individual_no_insurance_coupon,
  175. sum(case when I.listing_type = 'Fleet' then I.coupon end) as Fleet_coupon
  176. from
  177. (select
  178. distinct b.id as bid,
  179. extract('month' from b.created_at) as Amonth,
  180. extract('year' from b.created_at) as Ayear,
  181. b.listing_id as car_id,
  182. sum(case when b.status not IN('cancelled', 'payment_failed', 'withdrawn', 'rejected', 'accepted') then b.gross_amount end) as comp_gross,
  183. sum(case when b.status not IN('cancelled', 'payment_failed', 'withdrawn', 'rejected', 'accepted') then b.net_amount end) as comp_net,
  184. sum(case when b.status not IN('cancelled', 'payment_failed', 'withdrawn', 'rejected', 'accepted') then b.gross_amount end) -
  185. sum(case when b.status not IN('cancelled', 'payment_failed', 'withdrawn', 'rejected', 'accepted') then b.net_amount end) as coupon,
  186. ROUND((EXTRACT(EPOCH FROM b.booking_end_at) - EXTRACT(EPOCH FROM b.booking_start_at))/3600) as dur,
  187. l.status as listing_status, cu.id as cuid, li.insurance_package_id as insurance_id,
  188. CASE WHEN cu.id IS NOT NULL THEN 'Fleet' ELSE 'Individual' END listing_type,
  189. CASE WHEN li.insurance_package_id=2 THEN 'Trevo-Shield' ELSE 'Normal' END insurance_package
  190. from bookings b
  191. left join listings l on l.id = b.listing_id
  192. LEFT JOIN company_users cu ON cu.user_id=l.user_id
  193. LEFT join listing_insurance li on li.listing_id = l.id
  194. where b.status not IN('cancelled', 'payment_failed', 'withdrawn', 'rejected', 'accepted')
  195. group by 1,l.status,cuid,li.insurance_package_id) as I
  196. group by 1,2
  197. order by 1 asc) Rev
  198. on Rev.Amonth = Hos.Amonth and Rev.Ayear = Hos.Ayear
  199.  
  200. order by 1,37 asc
Advertisement
Add Comment
Please, Sign In to add comment
Public Pastes
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!