WITH profile AS (SELECT user_id,
COUNT (user_id) OVER (PARTITION BY DATE_TRUNC ('month' , created_at)='2020-01-01') AS total_users
FROM tools_shop.users
WHERE DATE_TRUNC ('month' , created_at)='2020-01-01'),
cohorts AS (SELECT *
FROM tools_
shop.events WHERE platform = 'android' AND (event_time BETWEEN '2020-01-01' AND '2020-04-01'))
SELECT DATE_TRUNC('month',c.event_time::timestamp) AS month_of_event,
p.total _users,
COUNT (DISTINCT c.user_id)
FROM cohorts AS c INNER JOIN profile AS p ON c.user_id=p.user_id
GROUP BY DATE_TRUNC('month',c.event_time::timestamp),
p.total _users
таблицы:
Мое решение:
SELECT *
FROM tools_ shop.events t1
INNER join (select user_id,
count (*) over () as total
from tools_shop.users
where date_trunc ('month', created_at) = '2020-01-01') t2 on t1.user_id = t2.user_id
AND platform = 'android'
AND DATE_TRUNC('month',event_time::timestamp) BETWEEN '2020-01-01' AND '2020-03-01'
ORDER BY DATE_TRUNC('month',event_time::timestamp), total