find the next occurrence of an event based on another event [message #682016] |
Sat, 26 September 2020 00:16 |
|
ac24
Messages: 1 Registered: September 2020
|
Junior Member |
|
|
Hi,
I'm hoping someone can help me figure out how to get this done. I have an oracle DB table that contains some information on users of a gaming website. Each gamer has a unique gamer_id and gets a new session_id assigned for each gaming session. A gaming session spans multiple weeks and the table has an entry per day for the gaming session per gamer. A gamer may or may not collect bonus points daily and this information is recorded in the bonus_points column.
I want to identify gamers who have been collecting bonus points every 7 days, starting from the session_start_dt_tm and ending with the session_end_dt_tm. So for example if the session_start_dt_tm for a gamer was JUL-02-2020 I would first like to determine if they had any day between JUL-02-2020 to JUL-08-2020 inclusive where bonus points were collected. Let's say this gamer had bonus points on JUL-06-2020, in that case their next 7 days bucket will restart from JUL-06-2020 and end on JUL-12-2020 and so on until either the session_end_dt_tm is reached or if the session is still active then session_end_dt_tm would be NULL.
Lastly I would like to sum up the number of 7 day buckets grouped per player where they had bonus points.
I have attached sample data.
Thank you for your time and assistance.
Ac
|
|
|
|
Re: find the next occurrence of an event based on another event [message #682018 is a reply to message #682016] |
Sat, 26 September 2020 08:52 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Just use analytic COUNT.
WITH T AS (
SELECT GAMER_ID,
SESSION_CALENDAR_DAY START_DATE,
SUM(BONUS_POINTS) OVER(
PARTITION BY GAMER_ID,
SESSION_ID
ORDER BY SESSION_CALENDAR_DAY
RANGE BETWEEN CURRENT ROW AND 6 FOLLOWING
) SEVEN_DAY_BONUS_POINT,
COUNT(NULLIF(BONUS_POINTS,0)) OVER(
PARTITION BY GAMER_ID,
SESSION_ID
ORDER BY SESSION_CALENDAR_DAY
RANGE BETWEEN CURRENT ROW AND 6 FOLLOWING
) CNT
FROM YOUR_TABLE
)
SELECT GAMER_ID,
START_DATE,
START_DATE + 6 END_DATE,
SEVEN_DAY_BONUS_POINT
FROM T
WHERE CNT = 7
/
SY.
|
|
|