Untitled

mail@pastecode.io avatar
unknown
plain_text
a year ago
853 B
9
Indexable
Never

WITH registered as (
    SELECT 
        EXTRACT( month from CAST(u.created_at as date)) as month,
        COUNT(DISTINCT u.user_id) as registerd_user_id
    FROM tools_shop.users as u
     WHERE CAST(DATE_TRUNC('month', u.created_at) as date) = '2020-01-01'
    GROUP BY EXTRACT( month from CAST(u.created_at as date))
   
),

active as (
    SELECT 
        EXTRACT( month from CAST(ev.event_time as date)) as month,
        COUNT(DISTINCT ev.user_id) as active_user_id
    FROM tools_shop.events as ev
    WHERE platform = 'android' AND CAST(DATE_TRUNC('month', event_time) as date)  >='2020-01-01' AND 
               CAST(DATE_TRUNC('month', event_time) as date) <= '2020-03-01'
    GROUP BY EXTRACT( month from CAST(ev.event_time as date))
)

SELECT *
FROM registered
INNER JOIN active
ON registered.month = active.month