CODEWITHSUNDEEP
snowflake-cloud-data-platform
user3369545
user3369545

Reputation: 421

Snowflake Sql Query to find the time taken between specific task flow for completing the coding task for an encounter

enter image description hereHope you are doing well!.. An encounter is a combination of ctextid and vbillid.. There are multiple events that place before an encounter is coding completed.. Sometimes the tasks don't get coding completed at all... Following represents the tasks flow (eventtype) for which the difference in the event time should be calculated in minutes...Please find below the input and the output tables...can you please help me here.. Distinctnumberofcoders is calculated as distinct count of user_id)..

170109-image.png

Input table

create table ##input
(event_time datetime2,
event_type varchar(1000),
user_id varchar(100),
ctextid int,
vbillid int)

insert into ##input values
('1/11/2022 1:21:02 PM','Start Work Clicked','am21','10021','21'),
('1/11/2022 1:46:02 PM','Coding Complete Clicked','am21','10021','21'),
('1/12/2022 1:46:02 PM','Start Work Clicked','am31','78321','561'),
('1/12/2022 1:51:02 PM','Home Button Clicked','am31','78321','561'),
('1/12/2022 2:04:02 PM','VisitBill Button Clicked','am12','78321','561'),
('1/12/2022 2:24:02 PM','Coding Complete Clicked','am12','78321','561'),
('1/12/2022 1:55:02 PM','Start Work Clicked','am78','5561','78'),
('1/12/2022 2:10:02 PM',' Icon Clicked','am78','5561','78'),
('1/12/2022 2:15:02 PM','VisitBill Button Clicked','am89','5561','78'),
('1/12/2022 2:44:02 PM','Coding Complete Clicked','am89','5561','78'),
('1/22/2022 9:55:02 AM','Start Work Clicked','am115','789','89'),
('1/22/2022 10:25:02 AM','Home Button Clicked','am115','789','89'),
('1/22/2022 10:45:02 AM','VisitBill Button Clicked','am115','789','89'),
('1/22/2022 11:10:02 AM',' Icon Clicked','am115','789','89'),
('1/22/2022 11:25:02 AM','VisitBill Button Clicked','am115','789','89'),
('1/22/2022 11:40:02 AM','Coding Complete Clicked','am115','789','89'),
('1/23/2022 9:55:02 AM','Start Work Clicked','am115','9918','890'),
('1/23/2022 10:25:02 AM','Home Button Clicked','am115','9918','890'),
('1/23/2022 10:45:02 AM','VisitBill Button Clicked','am115','9918','890'),
('1/23/2022 11:10:02 AM',' Icon Clicked','am115','9918','890'),
('1/23/2022 11:25:02 AM','VisitBill Button Clicked','am115','9918','890'),
('1/23/2022 11:30:02 AM','VisitBill Button Clicked','am115','9918','890'),
('1/23/2022 11:40:02 AM','Coding Complete Clicked','am115','9918','890'),
('1/25/2022 10:55:02 AM','Start Work Clicked','am115','10021','981'),
('1/25/2022 11:15:02 AM','Home Button Clicked','am115','10021','981'),
('1/25/2022 11:23:02 AM','VisitBill Button Clicked','am115','10021','981'),
('1/25/2022 9:45:02 AM','Start Work Clicked','am118','9021','904'),
('1/25/2022 10:05:02 AM','Home Button Clicked','am118','9021','904'),
('1/25/2022 10:23:02 AM','VisitBill Button Clicked','am120','9021','904'),
('1/25/2022 10:43:02 AM','Icon Clicked','am120','9021','904')


Output table

create table ##output
(ctextid int,
vbillid int,
eventdate date,
comment varchar(1000),
timetakeninminutes float,
distinctnumberofcoders int)

insert into ##output values

('10021','21','44572.5562731481','coding completed','25','1'),
('78321','561','44573.5771064815','coding completed','25','2'),
('5561','78','44573.5937731481','coding completed','44','2'),
('789','89','44583.4479398148','coding completed','70','1'),
('9918','890','44584.4479398148','coding completed','65','1'),
('10021','981','44586.4687731481','coding incomplete','20','1'),
('9021','904','44586.4465509259','coding incomplete','40','2')

Upvotes: 1

Views: 334

Answers (1)

Michael Golos
Michael Golos

Reputation: 2069

First, you need to build a map between which events you count time. Then you arrange the data in the order event_time and then you just count all these occurrences. Please try this code:

WITH map AS (
  SELECT *
    FROM VALUES('Start Work Clicked', 'Coding Complete Clicked')
              ,('Start Work Clicked', 'Home Button Clicked')
              ,('Start Work Clicked', 'Icon Clicked')
              ,('VisitBill Button Clicked', 'Coding Complete Clicked')
              ,('VisitBill Button Clicked', 'Home Button Clicked')
              ,('VisitBill Button Clicked', 'Icon Clicked') AS t(first_event_type, second_event_type)
), ip AS (
    SELECT *, CAST(event_time AS DATE) AS eventdate, ROW_NUMBER() OVER(PARTITION BY ctextid, vbillid, eventdate, user_id ORDER BY event_time) AS rn
      FROM input
), gaps AS (
  SELECT fi.ctextid
       , fi.vbillid
       , fi.user_id 
       , fi.event_time
       , fi.eventdate
       , m.second_event_type AS comment
       , SUM(TIMEDIFF(minute, fi.event_time, si.event_time)) AS timetakeninminutes
    FROM map AS m
    JOIN ip AS fi ON TRIM(fi.event_type) = TRIM(m.first_event_type)
    JOIN ip AS si ON TRIM(si.event_type) = TRIM(m.second_event_type)
                    AND fi.ctextid = si.ctextid 
                    AND fi.vbillid = si.vbillid 
                    AND fi.user_id = si.user_id 
                    AND CAST(fi.event_time AS DATE) = CAST(si.event_time AS DATE)
                    AND fi.rn + 1 = si.rn
   GROUP BY fi.ctextid, fi.vbillid, fi.user_id, fi.eventdate, comment, fi.event_time
)
SELECT DISTINCT 
       ctextid
     , vbillid
     , eventdate
     , LAST_VALUE(comment) OVER(PARTITION BY ctextid, vbillid, eventdate ORDER BY event_time) AS comment
     , SUM(timetakeninminutes) OVER(PARTITION BY ctextid, vbillid, eventdate) AS timetakeninminutes
     , COUNT(DISTINCT user_id) OVER(PARTITION BY ctextid, vbillid, eventdate) AS distinctnumberofcoders
  FROM gaps
 ORDER BY eventdate, ctextid DESC, vbillid;

Query result:

CTEXTID VBILLID EVENTDATE COMMENT TIMETAKENINMINUTES DISTINCTNUMBEROFCODERS
10021 21 2022-01-11 Coding Complete Clicked 25 1
78321 561 2022-01-12 Coding Complete Clicked 25 2
5561 78 2022-01-12 Coding Complete Clicked 44 2
789 89 2022-01-22 Coding Complete Clicked 70 1
9918 890 2022-01-23 Coding Complete Clicked 65 1
10021 981 2022-01-25 Home Button Clicked 20 1
9021 904 2022-01-25 Icon Clicked 40 2

Upvotes: 1

Related Questions