Untitled

mail@pastecode.io avatar
unknown
plain_text
a year ago
640 B
5
Indexable
Never
WITH users as (SELECT user_id, 
count(user_id) OVER ()
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'
                   ),
                   
tab as (SELECT *
FROM users
INNER JOIN events
ON users.user_id = events.user_id) 

SELECT 
EXTRACT( month from CAST(event_time as date)),
COUNT(DISTINCT tab.user_id)
FROM tab