Calculate the Time spent per user in a issue in sql

Question

I have a table where I have multiple users working, I need to calculate total time spent per user based on status claimed

 ISSUEID    Time             User       date     status
10101   2019-08-30 19:51:43 peter   2019-08-30  CLAIMED
10101   2019-08-30 19:51:46 peter   2019-08-30  CLAIMED
10101   2019-08-30 20:10:39 miller  2019-08-30  CLAIMED
10101   2019-08-30 21:21:14 miller  2019-08-30  CLAIMED
10101   2019-08-30 21:22:33 Renus   2019-08-30  CLAIMED
10101   2019-08-31 1:31:58  Renus   2019-08-31  CLAIMED
10101   2019-08-31 1:32:08  peter   2019-08-31  CLAIMED
10101   2019-08-31 1:32:12  peter   2019-08-31  CLAIMED
10101   2019-08-31 1:32:58  miller  2019-08-31  CLAIMED
10101   2019-09-04 23:16:24 peter   2019-09-04  CLAIMED
10101   2019-09-04 23:16:26 peter   2019-09-04  CLAIMED
10101   2019-09-04 23:16:50 peter   2019-09-04  CLAIMED
10101   2019-09-04 23:17:01 Renus   2019-09-04  CLAIMED
10101   2019-09-04 23:17:06 Renus   2019-09-04  CLAIMED

Desired result

IssueID user    timespent
10101   Peter   33
10101   Miller  4285
10101   Renus   30

Why do you have both timestamp and date columns? Will the dates ever differ? — jarlh, Sep 24 '19 at 12:12
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/199905/discussion-between-syncdm2012-and-jarlh). — syncdm2012, Sep 24 '19 at 12:14

score 1 · Answer 1 · edited Sep 24 '19 at 12:21

1

use DATETIME_DIFF() and before that convert datetime to date using DATETIME_TRUNC

SELECT
  ISSUEID,
  User,
  DATETIME_DIFF(
    min(DATETIME_TRUNC(Time, DAY)), 
    max(day), 
    day)
FROM table 
GROUP BY ISSUEID, User

edited Sep 24 '19 at 12:21

Martin Weitzmann

4,430
10
19

answered Sep 24 '19 at 12:16

Zaynul Abadin Tuhin

31,407
5
33
63

Zaynul Abadin Tuhin, Min & Max will give difference of first and last entry, which is not desired as I have mentioned. – syncdm2012 Sep 24 '19 at 12:22
@syncdm2012 can you please explain your logic – Zaynul Abadin Tuhin Sep 24 '19 at 12:26

score 1 · Accepted Answer · answered Sep 24 '19 at 12:44

Below is for BigQuery Standard SQL

#standardSQL
SELECT ISSUEID, user, SUM(timespent) timespent
FROM (
  SELECT ISSUEID, user, TIMESTAMP_DIFF(MAX(time), MIN(time), SECOND) timespent
  FROM (
    SELECT *, COUNTIF(start) OVER(ORDER BY time) grp
    FROM (
      SELECT *, IFNULL(LAG(User) OVER(ORDER BY time), '') != User AS start 
      FROM `project.dataset.table`
    )
  )
  GROUP BY ISSUEID, user, grp
)
GROUP BY ISSUEID, user

Calculate the Time spent per user in a issue in sql

2 Answers2