計算移動設備上的總用戶參與度

根據Rob和Nick提供的答案，我參考并結合他們，創建了以下內容：

WITH time_idle AS (
    SELECT
    sessionid,
    SUM(datediff(
        second,
        prev_timestamp,
        collector_tstamp)
    ) as time_idle_duration
    FROM
    (
      SELECT
          sessionid,
          event_name,
          collector_tstamp,
          LAG(event_name) OVER (ORDER BY collector_tstamp) AS prev_event,
          LAG(collector_tstamp) OVER (ORDER BY collector_tstamp) AS prev_timestamp
      FROM EVENTS
      WHERE event_name IN ('application_foreground', 'application_background')
    )
    WHERE event_name = 'application_foreground' and prev_event = 'application_background'
    group by sessionid
)
select
    DISTINCT
    sce.sessionid,
    datediff(
        second,
        MIN(sce.collector_tstamp) OVER (PARTITION BY sce.sessionid),
        MAX(sce.collector_tstamp) OVER (PARTITION BY sce.sessionid)
        )
    - ti.time_idle_duration as total_duration,
    datediff(
        second,
        MIN(sce.collector_tstamp) OVER (PARTITION BY sce.sessionid),
        MAX(sce.collector_tstamp) OVER (PARTITION BY sce.sessionid)
        ) AS absolute_time_duration,
      ti.time_idle_duration
from EVENTS sce
JOIN time_idle ti on sce.sessionid = ti.sessionid

我只是計算用戶在session上的總停機時間，并減去session的總持續時間。