Спасибо, @SaraDev, это нам пригодится. Поскольку мы — небольшое сообщество, где сотрудники гораздо активнее обычных пользователей, я (вернее, мы с ChatGPT) доработал запрос, чтобы можно было запускать отчёт отдельно для обычных пользователей, отдельно для сотрудников или объединённо. Мне также нравится видеть результаты в виде графика, поэтому я добавил соответствующую возможность.
Недавно я узнал, как скачать схему базы данных из этого совета от @tyler.lamparter. Я загружаю её в ChatGPT в начале каждой сессии — так проще попросить его отделить staff.
-- [params]
-- int :months_ago = 3
-- int :include_staff = 1
WITH staff_users AS (
SELECT user_id FROM group_users WHERE group_id = 3
),
daily_users AS (
SELECT
date_trunc('day', visited_at)::DATE AS day,
COUNT(DISTINCT user_id) AS dau
FROM user_visits
WHERE visited_at >= CURRENT_DATE - INTERVAL ':months_ago months'
AND (
(:include_staff = 1) -- Все пользователи
OR (:include_staff = 0 AND user_id NOT IN (SELECT user_id FROM staff_users)) -- Только не-сотрудники
OR (:include_staff = 2 AND user_id IN (SELECT user_id FROM staff_users)) -- Только сотрудники
)
GROUP BY day
),
monthly_users AS (
SELECT
date_trunc('month', visited_at)::DATE AS month,
COUNT(DISTINCT user_id) AS mau
FROM user_visits
WHERE visited_at >= CURRENT_DATE - INTERVAL ':months_ago months'
AND (
(:include_staff = 1) -- Все пользователи
OR (:include_staff = 0 AND user_id NOT IN (SELECT user_id FROM staff_users)) -- Только не-сотрудники
OR (:include_staff = 2 AND user_id IN (SELECT user_id FROM staff_users)) -- Только сотрудники
)
GROUP BY month
)
SELECT
d.day::DATE AS date, -- ✅ Ось X для построения графика
ROUND((d.dau::numeric / NULLIF(m.mau, 0)::numeric) * 100, 1)::FLOAT AS dau_mau_ratio -- ✅ Ось Y для построения графика
FROM daily_users d
JOIN monthly_users m ON date_trunc('month', d.day) = m.month
ORDER BY date
-- 0 = Только не-сотрудники, 1 = Все пользователи, 2 = Только сотрудники