Untitled

mail@pastecode.io avatar
unknown
plain_text
a year ago
794 B
7
Indexable
Never
WITH users as (SELECT user_id, 
EXTRACT( month from CAST(created_at as date)) as month,               
count(user_id) OVER () as user_registered
FROM tools_shop.users
WHERE CAST(DATE_TRUNC('month', created_at) as date) = '2020-01-01'),

events AS (SELECT *
               FROM tools_shop.events as android
               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'
                   ),
                   
tab1 as (SELECT *
FROM users
INNER JOIN events
ON users.user_id = events.user_id)


SELECT 
month,  user_registered , 
count(DISTINCT events.user_id)
FROM tab1, events
GROUP BY month,  user_registered
ORDER BY month,  user_registered