Количество пользователей, которые вернулись в течение 3 дней после игры не менее трех сеансов?

Question

У меня есть данные, которые содержат user, eventdate и session. Я хочу отделить пользователей, которые провели как минимум 3 сессии и вернулись на новую сессию в течение 3 дней.

    user  eventdate   session
    A      2018-02-05   1
    A      2018-02-05   2
    A      2018-02-06   3 
    A      2018-02-10   4

Вывод пользователей, которые сделали 3 сеанса, а затем вернулись на четвертый сеанс в течение 3 дней.

Я попытался следующий запрос, но он не дает мне ответа, который необходим.

 SELECT distinct user, MIN(eventdate) startdate, MAX(eventdate) enddate
FROM (SELECT user, eventdate
      FROM (SELECT user, eventdate
              FROM tablename
             where datediff(startdate,enddate)<=3
             ORDER BY user, eventdate) where sessions>=3) t
 GROUP BY user
 ORDER BY user, startdate;

Я знаю, что у запроса много проблем, но я просто не могу понять, как двигаться дальше. Какие-либо предложения?

Answer 1

Ниже для BigQuery Standard SQL

#standardSQL
SELECT *
FROM (
  SELECT 
    user, eventdate, sessions_in_a_day, 
    SUM(sessions_in_a_day) OVER(PARTITION BY user ORDER BY eventdate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) total_sessions_before, 
    DATE_DIFF(eventdate, LAG(eventdate) OVER(PARTITION BY user ORDER BY eventdate), DAY) delay
  FROM (
    SELECT user, eventdate, COUNT(1) sessions_in_a_day 
    FROM t
    GROUP BY user, eventdate
  )
)
WHERE total_sessions_before >= 3
AND delay <= 3
-- ORDER BY user, eventdate

Вы можете проверить / поиграть с выше, используя фиктивные данные

#standardSQL
WITH t AS (
  SELECT 'A' user, DATE '2018-02-05' eventdate, 1 session UNION ALL
  SELECT 'A', DATE '2018-02-05', 2 UNION ALL
  SELECT 'A', DATE '2018-02-06', 3 UNION ALL
  SELECT 'A', DATE '2018-02-06', 4 UNION ALL
  SELECT 'A', DATE '2018-02-09', 5 UNION ALL
  SELECT 'A', DATE '2018-02-09', 6 UNION ALL
  SELECT 'A', DATE '2018-02-10', 7 UNION ALL 
  SELECT 'A', DATE '2018-02-13', 8 
)
SELECT *
FROM (
  SELECT 
    user, eventdate, sessions_in_a_day, 
    SUM(sessions_in_a_day) OVER(PARTITION BY user ORDER BY eventdate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) total_sessions_before, 
    DATE_DIFF(eventdate, LAG(eventdate) OVER(PARTITION BY user ORDER BY eventdate), DAY) delay
  FROM (
    SELECT user, eventdate, COUNT(1) sessions_in_a_day 
    FROM t
    GROUP BY user, eventdate
  )
)
WHERE total_sessions_before >= 3
AND delay <= 3
ORDER BY user, eventdate  

результат

Row user    eventdate   sessions_in_a_day   total_sessions_before   delay    

1   A       2018-02-09  2                   4                       3    
2   A       2018-02-10  1                   6                       1    
3   A       2018-02-13  1                   7                       3    

Играя с предложением WHERE, вы можете "настроиться" на любой случай, который вам нужен
В приведенном выше примере отображаются только пользователи, у которых было по крайней мере 3 сеанса до того, как они достигли следующего сеанса в течение следующих 3 дней. Если вас интересуют только те, у кого было ровно 3 сеанса и которые достигли своего четвертого сеанса - вы можете добавить соответствующий фильтр

Answer 2

WITH Sess AS
(
select user, session
from tablename
group by  user
HAVING count(session) >= 3
)

select user
from tablename join Sess on tablename.session = Sess.session
group by user
having (datediff(day, min(eventdate), Max(eventdate)) <=3) 
and (min(eventdate) <> Max(eventDate))