Advertisement
Guest User

Untitled

a guest
Jan 17th, 2017
61
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.95 KB | None | 0 0
  1. WITH prints AS (
  2. SELECT date_trunc('day', offer_events.created_at) "date", COUNT(offer_events.id) "count"
  3. FROM offer_events
  4. JOIN app_users
  5. ON app_users.id = offer_events.user_id
  6. WHERE app_users.web_app_id = 6 AND event_type = 1
  7. GROUP BY date
  8. ),
  9. opens AS (
  10. SELECT date_trunc('day', offer_events.created_at) "date", COUNT(offer_events.id) "count"
  11. FROM offer_events
  12. JOIN app_users
  13. ON app_users.id = offer_events.user_id
  14. WHERE app_users.web_app_id = 6 AND event_type = 2
  15. GROUP BY date
  16. ),
  17. new_users AS (
  18. SELECT date_trunc('day', app_users.created_at) "date", COUNT(app_users.id) "count"
  19. FROM app_users
  20. WHERE app_users.web_app_id = 6
  21. GROUP BY date
  22. )
  23. SELECT
  24. COALESCE(prints.date, opens.date, new_users.date) date,
  25. COALESCE(prints.count, 0) prints,
  26. COALESCE(opens.count, 0) opens,
  27. COALESCE(new_users.count, 0) new_users
  28. FROM prints
  29. LEFT OUTER JOIN opens ON prints.date = opens.date
  30. LEFT OUTER JOIN new_users ON prints.date = new_users.date
  31. ORDER BY date ASC
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement