Advertisement
Guest User

Untitled

a guest
Sep 26th, 2016
66
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.72 KB | None | 0 0
  1. WITH new_users AS (
  2. SELECT DISTINCT ON (user_id) user_id, created_at
  3. FROM purchases
  4. ORDER BY user_id ASC, created_at ASC
  5. ), old_users AS (
  6. SELECT DISTINCT ON (user_id) user_id, created_at
  7. FROM purchases AS pc
  8. WHERE exists (
  9. SELECT 1 FROM purchases
  10. WHERE date_trunc('month', created_at) < date_trunc('month', pc.created_at)
  11. AND user_id = pc.user_id
  12. )
  13. )
  14. SELECT
  15. COALESCE(old_users.created_at, new_users.created_at),
  16. COUNT(DISTINCT new_users.user_id) AS new_user,
  17. COUNT(DISTINCT old_users.user_id) AS old_user
  18. FROM old_users full outer JOIN new_users
  19. ON old_users.created_at = new_users.created_at
  20. GROUP BY COALESCE(old_users.created_at, new_users.created_at)
  21. ORDER BY COALESCE(old_users.created_at, new_users.created_at)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement