Forum

Posted by gurpreet, Nov. 19, 2021, 8:02 p.m.

SQL#147

I was testing the solution, and found that USER_ID = '8000024' doesn't seem to have a streak, but, it is showing up in the result.
*TEST QUERY*
SELECT distinct user_id,
date
FROM session_mobile
WHERE date >= '2021-08-01' and date < '2021-09-01'
and USER_ID = '8000024'
order by date

*RESULT*
user_id date
8000024 2021-08-03
8000024 2021-08-06
8000024 2021-08-10
8000024 2021-08-12
8000024 2021-08-14
8000024 2021-08-17
8000024 2021-08-21
8000024 2021-08-29
Essentially, this user should not be showing up in valid users who have >=2 day streak
*My current solution*
With valid_users as (
SELECT distinct user_id,
date
FROM session_mobile
WHERE date >= '2021-08-01' and date < '2021-09-01'
AND event not in ('enter', 'exit')
),
streaks as (
SELECT user_id,
date - LAG(date) over (partition by user_id order by date) as streak
FROM valid_users
)

SELECT distinct user_id
from streaks
where streak =1

Answers

Thanks @Mike, @Gurpreet, there were some bugs in the solution which was fixed.
SQLPad user avatar

Leon (949)

Nov. 20, 2021, 6:48 a.m.