сложный запрос с датами 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');

мне нужно получить из нее таблицу сгрупированную по датам регистрации, только тех элементов у которых на второй день после регистрации пользователя происходило действе "showSomeThing" у ого же пользователя. в первом столбце дата регистрации, во втором количество всех регистраций в этот день деленное на количество "showSomeThing" на следующий день после регистрации сверять по "user id" и под названиями другими. пример того что из этого примера должно выдать:

+-----------------+---------+
|    regDate      |  ret1d  |
+-----------------+---------+
|    2014-12-12   |    3    |
+-----------------+---------+
|    2014-12-15   |    1    |
+-----------------+---------+

вот) т.е. выдало 2014-12-12 потому что из трех пользователей зарегистрировавшихся в тот день один хоть на следующий день сделал действие "showSomeThing" а его начение во втором столбце количество всех пользователей зареганных в тот день(3) поделенное на количество пользователей из этих трех которые на следующий день сделали действие "showSomeThing". А зареганых 2014-12-15 только один и он же сделал действие на следующий день.

пишу вот и пока мало что выходит. SELECT * FROM retention;

SELECT dates, count(dates) as dat FROM retention
GROUP BY dates

Answer 1

SELECT t1.dates, COUNT(*) ret1d
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'
WHERE t1.eventes = 'registration'
GROUP BY t1.dates
HAVING COUNT(t2.eventes)

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=35265869f20e5311b49bb95ba702bab6

БЛОГ НА HUSL

сложный запрос с датами SQL

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