сложный запрос с датами SQL с выводом пользователей повторно посетивших сайт

Question

Не могу отдельно посчитать ретеншн. Имеется такая таблица

CREATE TABLE retention (
    id INT,
    dates date, 
    dateTimes datetime,
    userId INT,
    eventes VARCHAR(50)
);

INSERT INTO retention (id, dates, dateTimes, userId, eventes)
VALUES (1, '2014-12-12', '2014-12-12 12:34:54', 5, 'registration'), (2, '2014-12-12', '2014-12-12 14:34:54', 2, 'registration'), (3, '2014-12-12', '2014-12-12 05:34:54', 10, 'registration'),  (4, '2014-07-12', '2014-07-12 05:34:54', 17, 'registration'),  (5, '2014-12-15', '2014-12-15 05:34:54', 15, 'registration'), (6, '2014-12-13', '2014-12-13 11:08:04', 5, 'auth'), (7, '2014-12-13', '2014-12-13 11:08:34', 5, 'showSomeThing'), (7, '2014-12-13', '2014-12-13 11:08:04', 2, 'auth'), (8, '2014-12-13', '2014-12-13 11:08:34', 10, 'auth'), (9, '2014-12-13', '2014-12-14 11:08:34', 10, 'showSomeThing'), (10, '2014-12-16', '2014-12-16 05:34:54', 15, 'auth'), (11, '2014-12-16', '2014-12-16 07:42:11', 15, 'showSomeThing'), (12, '2014-12-15', '2014-12-15 11:08:34', 5, 'showSomeThing'), (13, '2014-12-15', '2014-12-15 11:08:34', 10, 'showSomeThing');
SELECT * FROM retention;

Мне нужно вывести таблицу где в "regDate" будет выводится дата в которой зарегистрировались пользователи и при этом они совершили "showSomeThing" в течении 3ех дней. во втором столбце "ret1d" вывести количество пользователей зарешистрировавшихся в тот день деленное на количество пользователей совершивших в течении дня 'showSomeThing'. а в третей колонке "ret2d" вывести количество пользователей зарешистрировавшихся в тот день деленное на количество пользователей совершивших в течении трех дней 'showSomeThing'

Вот что пока разобрал, ну не без вашей помощи)))

SELECT t1.dates regDate, COUNT(t2.eventes)/COUNT(t1.dates) ret1d, COUNT(t3.eventes)/COUNT(t1.dates) ret2d
FROM retention t1 
LEFT JOIN retention t2 ON t1.userId = t2.userId 
    AND t2.dates BETWEEN t1.dates AND t1.dates + INTERVAL 1 DAY 
    AND t2.eventes = 'showSomeThing' 
LEFT JOIN retention t3 ON t1.userId = t3.userId 
    AND t3.dates BETWEEN t1.dates AND t1.dates + INTERVAL 3 DAY 
    AND t3.eventes = 'showSomeThing' 
WHERE t1.eventes = 'registration' 
GROUP BY t1.dates 
HAVING COUNT(t2.eventes)

получается у меня

а должно

+--------------+-------+-------+
|    regDate   | ret1d | ret2d |
+--------------+-------+-------+
|  2014-12-12  | 0,66  | 0,33  |
+--------------+-------+-------+
|  2014-12-15  |   1   |   0   |
+--------------+-------+-------+

т.е. из трех тех кто 2014-12-12 зарегался совершили в течении дня"showSomeThing" двое и получили 2/3 равное 0,66, а в течении трех дней один и получили 1/3 = 0,33. а из тех кто 2014-12-15 зарегался совершил в течении дня"showSomeThing" только один и получили 1/1, а в течении трех дней никто не вернулся и получили 0. Ну почти у меня полуичлось)) Может кто то помочь? и желательно отсчитывать дни от даты с временем совершения эвента т.е. отсчитывать от dataTimes

БЛОГ НА HUSL

сложный запрос с датами SQL с выводом пользователей повторно посетивших сайт

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