Untitled

From Ungracious Bat, 10 Months ago, written in Plain Text, viewed 353 times.
URL http://codebin.org/view/f7cc6287 Embed
Download Paste or View Raw
  1. with profile as
  2. (select u.user_id, date_trunc('month', min(event_time))::date as dt
  3. from tools_shop.users u
  4. join tools_shop.orders o ON o.user_id=u.user_id
  5. join tools_shop.events e ON e.user_id=u.user_id
  6. group by 1),
  7. sessions as
  8. (select date_trunc('month', event_time)::date as dt, e.user_id
  9. from tools_shop.events e
  10. join profile p ON e.user_id=p.user_id
  11. group by 1, 2),
  12. cohort_users_cnt as
  13. (select dt as dt,
  14. count(user_id) as cohort_users_cnt
  15. from profile
  16. group by 1),
  17. profiles as
  18. (select p.user_id, s.dt as cohort_start
  19. from profile p
  20. join sessions s ON p.user_id=s.user_id)
  21. select pr.cohort_start, cuc.dt, count(pr.user_id) as users_cnt, cuc.cohort_users_cnt,
  22. ROUND(count(pr.user_id) * 100.0 / cohort_users_cnt, 2) as retention_rate
  23. from profiles pr
  24. join cohort_users_cnt cuc ON pr.cohort_start=cuc.dt
  25. group by 1, 2, 4
  26. order by 1, 2

Reply to "Untitled"

Here you can reply to the paste above