Я пытаюсь выяснить некоторые скользящие статистики окна для моих пользователей. У меня есть таблица с пользователем и столбцы, такие как created_at и verified_at. В течение каждого месяца я хотел бы узнать, сколько пользователей зарегистрировано (простая группа по дате_trunc созданного_at), а затем этих людей, сколько проверок в моем скользящем окне (назовите это 60 дней).
Я хотел бы сделать SQL-запрос, который дает мне что-то вроде:
Month | Registered | Verified in 60 days
Jan 2009 | 1543 | 107
Feb 2009 | 2000 | 250
Я использую postgresql. Я начинаю смотреть на сумму (случай...), но я не знаю, могу ли я заставить мое дело быть зависимым от date_trunc каким-то образом.
Это не работает, конечно, но вот идея:
SELECT DATE_TRUNC('month', created_at) as month,
COUNT(*) as registered,
SUM(CASE WHEN verified_at < month+60 THEN 1 ELSE 0 END) as verified
FROM users
GROUP BY DATE_TRUNC('month', created_at)