Как правильно сделать сравнение 2-х таблиц с дилеем даты в date + 24 часа

Question

Есть 2 таблицы: installs (поля: event_id, user_id, event_date), где строка = установка приложения, и actions (поля такие же), где строка = какое-то ключевое действие.

Нужно посчитать конверсию от installs к actions по дням, где сконвертированным считается юзер, который совершил событие из actions в течение 24-х часов после того, как трекнулся в installs. Т.е. если user_id из таблицы installs есть в таблице actions не позднее чем installs.event_date + 24 часа.

Postgre 9.6, http://sqlfiddle.com/#!17/99e06/17

Я накидал совсем просто, и даже результаты походят на правду, но в этом решении, насколько я понимаю, учитывается только совершение обоих событий в один день, но если инсталл был 2020-01-01 23:59:00 а экшн 2020-01-02 00:05:00 то не считается.

Как правильно сделать вот это окошко в 24 часа?

Answer 1

Что-то типа

select i.event_date::date, 
       count(distinct i.user_id),
       count(distinct a.user_id),
       100*count(distinct a.user_id)/count(distinct i.user_id)
from installs i
left join actions a on i.user_id = a.user_id
                   and i.event_date + interval '1 day' >= a.event_date
group by 1

БЛОГ НА HUSL

Как правильно сделать сравнение 2-х таблиц с дилеем даты в date + 24 часа

Ответы (1 шт):