Home » SQL & PL/SQL » SQL & PL/SQL » find the next occurrence of an event based on another event ( SQL Developer 19.2.1 )
find the next occurrence of an event based on another event [message #682016] Sat, 26 September 2020 00:16 Go to next message
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 #682017 is a reply to message #682016] Sat, 26 September 2020 01:41 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read the OraFAQ Forum Guide and How to use code tags and make your code easier to read

Is this a college homework assignment?

You need to provide the CREATE TABLE and INSERT statements needed to set up the problem, and also show what SQL you have tried so far, and the result you would like from your sample data.
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 Go to previous message
Solomon Yakobson
Messages: 3269
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.
Previous Topic: How to get a substring from a character on
Next Topic: update column for the set of records
Goto Forum:
  


Current Time: Thu Mar 28 09:45:38 CDT 2024