I am trying to roll up/aggregate the records but cannot find the solution from oracle aggregation functions.
I've been playing with LAG, COLLECT, RANK, but couldn't find a solution yet.
I have the following table and would need to pull the last 5 user comments for the last two user actions.
From below sample, last two actions are Action2 and Action3.
date | user_id | user_action | user_comment
---------+---------+-------------+-------------
5/1/2019 | USER3 | OPEN | COMMENT0
5/1/2019 | USER1 | ACTION1 | COMMENT1
5/2/2019 | USER2 | OPEN | COMMENT2
5/3/2019 | USER2 | ACTION2 | COMMENT3
5/4/2019 | USER3 | OPEN | COMMENT4
5/5/2019 | USER3 | OPEN | COMMENT5
5/5/2019 | USER4 | OPEN | COMMENT6
5/6/2019 | USER3 | OPEN | COMMENT7
5/7/2019 | USER3 | OPEN | COMMENT8
5/8/2019 | USER3 | OPEN | COMMENT9
5/9/2019 | USER3 | ACTION3 | COMMENT10
The point is that User4's action should not be included.
Comment0 should not be included as it's not between Action2 and action3 even though it's the same user as Action3.
user_id | action | comments
--------+---------+--------------------------------------------------
USER3 | ACTION3 | COMMENT10//COMMENT9//COMMENT8//COMMENT7//COMMENT5
USER2 | ACTION2 | COMMENT3//COMMENT2
Can anyone provide any tips?
The following query may need some tweaking. However, it can give you a starting point for finding a solution that fits your requirements. Ideas/steps: {1} Number all entries for a particular user via ROW_NUMBER(). {2} Find the last 2 actions. {3} join the 2 result sets, pick the last 5 entries, and get the comments into one row by using GROUP BY and LISTAGG().
select
UA.userid, UA.useraction
, listagg( R.usercomment, '//' ) within group ( order by R.date_ desc ) --{3}
as comments
from (
select -- {1}
date_, userid, useraction, usercomment
, row_number() over ( partition by userid order by date_ desc ) rownum_
from useractions
) R join ( -- {3}
select userid, useraction -- {2}
from (
select date_, userid, useraction
, row_number() over ( order by date_ desc ) rownum2_
from useractions
where useraction like 'ACTION%'
)
where rownum2_ <= 2
) UA on R.userid = UA.userid -- {3} join
where R.rownum_ between 1 and 5 -- {3} we only the last 5 entries
group by UA.userid, UA.useraction -- {3}
order by userid desc
;
-- result
USERID USERACTION COMMENTS
USER3 ACTION3 COMMENT10//COMMENT9//COMMENT8//COMMENT7//COMMENT5
USER2 ACTION2 COMMENT3//COMMENT2