Think Time after Viewing an APEX Interactive Report (IR)

I had someone ask me how to get the think time after someone views an Interactive Report. I think the query below does it.

with page_views_with_previous_view_id as

(select application_id, page_id, apex_user, apex_session_id, page_view_type, view_timestamp,

      lead(view\_timestamp, 1) OVER (ORDER BY view\_timestamp desc) AS prev\_view\_ts

from apex\_workspace\_activity\_log al

where application\_id = :APP\_ID

)

select pv.*,

     pv.view\_timestamp - ppv.view\_timestamp think\_time,

     ppv.interactive\_report\_id ppv\_ir\_id

from page_views_with_previous_view_id pv -- page_view

inner join apex_workspace_activity_log ppv -- previous_page_view

on ppv.view\_timestamp = pv.prev\_view\_ts

and ppv.application\_id = :APP\_ID

  and ppv.apex\_session\_id = pv.apex\_session\_id

  and ppv.page\_id = 2

  and ((ppv.interactive\_report\_id = 38006896117622159377) -- if you hard code the ID

       or   -- use the region static id

            (ppv.interactive\_report\_id = (select pir.interactive\_report\_id 

                                            from apex\_application\_page\_ir pir 

                                            inner join apex\_application\_page\_regions pr on pr.region\_id = pir.region\_id

                                            where pir.application\_id = :APP\_ID 

                                              and pir.page\_id = 2

                                              and pr.static\_id ='ANTON\_IR\_STATIC\_ID')

            )

       or

       (ppv.page\_view\_type = 'Rendering')

  )

order by pv.view_timestamp desc

Maybe this will help another person as well.