Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- WITH prints AS (
- SELECT date_trunc('day', offer_events.created_at) "date", COUNT(offer_events.id) "count"
- FROM offer_events
- JOIN app_users
- ON app_users.id = offer_events.user_id
- WHERE app_users.web_app_id = 6 AND event_type = 1
- GROUP BY date
- ),
- opens AS (
- SELECT date_trunc('day', offer_events.created_at) "date", COUNT(offer_events.id) "count"
- FROM offer_events
- JOIN app_users
- ON app_users.id = offer_events.user_id
- WHERE app_users.web_app_id = 6 AND event_type = 2
- GROUP BY date
- ),
- new_users AS (
- SELECT date_trunc('day', app_users.created_at) "date", COUNT(app_users.id) "count"
- FROM app_users
- WHERE app_users.web_app_id = 6
- GROUP BY date
- )
- SELECT
- COALESCE(prints.date, opens.date, new_users.date) date,
- COALESCE(prints.count, 0) prints,
- COALESCE(opens.count, 0) opens,
- COALESCE(new_users.count, 0) new_users
- FROM prints
- LEFT OUTER JOIN opens ON prints.date = opens.date
- LEFT OUTER JOIN new_users ON prints.date = new_users.date
- ORDER BY date ASC
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement