finding time difference between rows

Question

I need to calculate the claimed time of IssueID by user Total claim time is time from status claim till recent waiting. Looks a bit complex Kindly help out.

 IssueID    TransTime   User    Status
101 2019-08-23 0:25:41  Peter   CLAIMED
101 2019-08-23 0:25:44  Peter   CLAIMED
101 2019-08-23 0:26:12  Peter   WAITING
101 2019-08-23 20:14:13 Peter   CLAIMED
101 2019-08-23 20:14:16 Peter   CLAIMED
101 2019-08-23 20:14:52 Peter   WAITING
102 2019-08-24 8:59:19  Miller  CLAIMED
102 2019-08-24 8:59:56  Miller  CLAIMED
102 2019-08-24 9:00:09  Miller  WAITING
102 2019-08-24 9:00:17  Miller  CLAIMED
102 2019-08-24 9:00:20  Miller  CLAIMED
102 2019-08-25 21:56:52 Miller  WAITING`

For example, For peter total claim time start from '2019-08-23 0:25:41' till the first waiting time '2019-08-23 0:26:12' and next from '2019-08-23 20:14:13' till '2019-08-23 20:14:52'. All this time difference add up to the total time claimed by peter, which is around 31 seconds first and 39 seconds in the second time. Comes around 70 seconds.

Thanks in Advance

`

Answer 1

You can identify each group by counting the number of "waiting" after each row. Then use this information to get each claim period. So:

select issueId,
       min(transTime) as min_time,
       max(transTime) as max_time),
       datetime_diff(min(transTime), max(transTime), second) as time_in_seconds
from (select t.*,
             countif(status = 'WAITING') over (partition by issueId order by transTime desc) as grp
      from t
      where status in ('WAITING', 'CLAIM')
     ) t
group by issueId, grp;

I'm not sure if is exactly what you want -- you might want an additional level of aggregation. But this is the idea for calculating each period.

Answer 2

Below is for BigQuery Standard SQL

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 101 IssueID, TIMESTAMP '2019-08-23 0:25:41' TransTime, 'Peter' User, 'CLAIMED' Status UNION ALL
  SELECT 101, '2019-08-23 0:25:44', 'Peter', 'CLAIMED' UNION ALL
  SELECT 101, '2019-08-23 0:26:12', 'Peter', 'WAITING' UNION ALL
  SELECT 101, '2019-08-23 20:14:13', 'Peter', 'CLAIMED' UNION ALL
  SELECT 101, '2019-08-23 20:14:16', 'Peter', 'CLAIMED' UNION ALL
  SELECT 101, '2019-08-23 20:14:52', 'Peter', 'WAITING' UNION ALL
  SELECT 102, '2019-08-24 8:59:19', 'Miller', 'CLAIMED' UNION ALL
  SELECT 102, '2019-08-24 8:59:56', 'Miller', 'CLAIMED' UNION ALL
  SELECT 102, '2019-08-24 9:00:09', 'Miller', 'WAITING' UNION ALL
  SELECT 102, '2019-08-24 9:00:17', 'Miller', 'CLAIMED' UNION ALL
  SELECT 102, '2019-08-24 9:00:20', 'Miller', 'CLAIMED' UNION ALL
  SELECT 102, '2019-08-25 21:56:52', 'Miller', 'WAITING' 
)
SELECT IssueID, SUM(waiting_time) total_waiting_time 
FROM (
  SELECT IssueID, TIMESTAMP_DIFF(MAX(TransTime), MIN(TransTime), SECOND) waiting_time
  FROM (
    SELECT *, COUNTIF(start) OVER(PARTITION BY IssueID ORDER BY TransTime) waiting
    FROM (
      SELECT *, ('CLAIMED' = status AND IFNULL(LAG(status) OVER(PARTITION BY IssueID ORDER BY TransTime), 'WAITING') = 'WAITING') start
      FROM `project.dataset.table`
      WHERE status IN ('CLAIMED', 'WAITING')
    )
  )
  GROUP BY IssueID, waiting
)
GROUP BY IssueID
ORDER BY IssueID

with result

Row IssueID total_waiting_time   
1   101     70   
2   102     133045