Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- WITH new_users AS (
- SELECT DISTINCT ON (user_id) user_id, created_at
- FROM purchases
- ORDER BY user_id ASC, created_at ASC
- ), old_users AS (
- SELECT DISTINCT ON (user_id) user_id, created_at
- FROM purchases AS pc
- WHERE exists (
- SELECT 1 FROM purchases
- WHERE date_trunc('month', created_at) < date_trunc('month', pc.created_at)
- AND user_id = pc.user_id
- )
- )
- SELECT
- COALESCE(old_users.created_at, new_users.created_at),
- COUNT(DISTINCT new_users.user_id) AS new_user,
- COUNT(DISTINCT old_users.user_id) AS old_user
- FROM old_users full outer JOIN new_users
- ON old_users.created_at = new_users.created_at
- GROUP BY COALESCE(old_users.created_at, new_users.created_at)
- ORDER BY COALESCE(old_users.created_at, new_users.created_at)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement