Time difference between different stages in data set-CodePudding

I was wondering if you could help me out. Im not sure if this is possible but given the table of data below I was wondering if it is possible to write a query that could easily show the time taken for each car between the carViewed and carBought stage. Ideally I would like to see the carID along with the time. For example the results should be something like this:

CarID	TimeDifference
1	00:17:83
2	00:04:21
3	01:57:83

Data

CarID	Stage	Timestamp
1	carArrived	2022-01-20 13:00:00
1	carViewed	2022-01-20 14:00:00
1	carBought	2022-01-20 14:17:83
1	carLeft	2022-01-20 15:17:83
2	carArrived	2022-01-21 15:00:00
2	carViewed	2022-01-21 16:00:00
2	carBought	2022-01-21 16:04:21
2	carLeft	2022-01-21 16:27:83
3	carArrived	2022-01-22 13:00:00
3	carViewed	2022-01-22 14:00:00
3	carBought	2022-01-22 15:57:83
3	carLeft	2022-01-22 16:17:83

Any help with this would be greatly appreciated. Thank you.

CodePudding user response：

Use conditional aggregation:

SELECT carid,
       MAX(CASE stage WHEN 'carBought' THEN timestamp END)
         - MIN(CASE stage WHEN 'carViewed' THEN timestamp END) AS timeDifference
FROM   table_name
GROUP BY carid

Which, for the sample data:

CREATE TABLE table_name (CarID, Stage, Timestamp) AS
SELECT 1, 'carArrived', TIMESTAMP '2022-01-20 13:00:00' FROM DUAL UNION ALL
SELECT 1, 'carViewed',  TIMESTAMP '2022-01-20 14:00:00' FROM DUAL UNION ALL
SELECT 1, 'carBought',  TIMESTAMP '2022-01-20 14:17:53' FROM DUAL UNION ALL
SELECT 1, 'carLeft',    TIMESTAMP '2022-01-20 15:17:53' FROM DUAL UNION ALL
SELECT 2, 'carArrived', TIMESTAMP '2022-01-21 15:00:00' FROM DUAL UNION ALL
SELECT 2, 'carViewed',  TIMESTAMP '2022-01-21 16:00:00' FROM DUAL UNION ALL
SELECT 2, 'carBought',  TIMESTAMP '2022-01-21 16:04:21' FROM DUAL UNION ALL
SELECT 2, 'carLeft',    TIMESTAMP '2022-01-21 16:27:53' FROM DUAL UNION ALL
SELECT 3, 'carArrived', TIMESTAMP '2022-01-22 13:00:00' FROM DUAL UNION ALL
SELECT 3, 'carViewed',  TIMESTAMP '2022-01-22 14:00:00' FROM DUAL UNION ALL
SELECT 3, 'carBought',  TIMESTAMP '2022-01-22 15:57:53' FROM DUAL UNION ALL
SELECT 3, 'carLeft',    TIMESTAMP '2022-01-22 16:17:53' FROM DUAL;

Outputs:

CARID TIMEDIFFERENCE

1 000000000 00:17:53.000000000

2 000000000 00:04:21.000000000

3 000000000 01:57:53.000000000

CARID	TIMEDIFFERENCE
1	000000000 00:17:53.000000000
2	000000000 00:04:21.000000000
3	000000000 01:57:53.000000000

db<>fiddle here