time measurement between 2 entries in database

Question

I'm trying to write a query to measure the time between two events. Let's assume I have the following entries in a database

userid	event	timestamp
user1	endpoint_called	2022-04-29 22:07:22.342000 UTC
user1	endpoint_200	2022-04-29 22:07:24.342000 UTC
user2	endpoint_called	2022-04-29 22:07:22.342000 UTC
user2	endpoint_400	2022-04-29 22:07:27.342000 UTC
user3	endpoint_called	2022-04-29 22:07:22.342000 UTC

cases:

• user1 has an entry for endpoint_called and endpoint_200
• user2 has an entry for endpoint_called and endpoint_400
• user3 has only endpoint called

Now i need a query where:

• if only endpoint is called for a user i have a timeout (count())
• if we have an endpoint_called and endpoint_200 for a user i need the time difference between these two timestamps
• if we have an endpoint_called and endpoint_400 i need the time difference between these two timestamps

user1, user2 or user3 are only dummies. I have a few Gigabyte of date with userNNNN

Any Idea how to solve this maybe in one query ?

Answer 1

I think something like this would get you what you want, and should work on any platform.

SELECT UserID,
        CASE 
          WHEN endpoint_200 IS NOT NULL THEN endpoint_called-endpoint_200
          WHEN endpoint_400 IS NOT NULL THEN endpoint_called-endpoint_400
          ELSE whatever_your_timeout_count_is_supposed_to_be
        END as ResultField
  FROM (
        SELECT UserID,
               MAX(CASE WHEN Event = 'endpoint_called' THEN timestamp 
                        ELSE NULL END) as endpoint_called,
               MAX(CASE WHEN Event = 'endpoint_200' THEN timestamp 
                        ELSE NULL END) as endpoint_200,
               MAX(CASE WHEN Event = 'endpoint_400' THEN timestamp 
                        ELSE NULL END) as endpoint_400
          FROM YourTable
         GROUP BY UserID
       ) sub