CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
an_analyst
an_analyst

Reputation: 39

Get difference in timestamps based on a condition

I have one table.

user_id status timestamp
null null 2022-05-05 01:01:00
null null 2022-05-05 01:02:00
7461 null 2022-05-05 01:03:00
null open 2022-05-05 01:04:00
null null 2022-05-05 01:05:00
null on hold 2022-05-05 01:06:00
8474 null 2022-05-05 01:07:00
null null 2022-05-05 01:08:00
null solved 2022-05-05 01:09:00

I need to find the time difference between from when a user_id is assigned and the first status change. Also, if there is no status change until the next user is assigned, then I need to find the time difference from when the first user_id is assigned to when the next user_id is assigned.

The output I'm looking for is

user_id timestamp
7461 00:01:00
8474 00:02:00

I've been working on this for hours and I can't seem to get the right result. I know that a CTE would work but I don't know how to put in the conditions correctly. Any help would be appreciated.

Upvotes: 0

Views: 186

Answers (1)

lemon
lemon

Reputation: 15482

If you're working with MySQL, you can do it this way:

   SELECT t1.user_id,
          MIN(SEC_TO_TIME(TIMESTAMPDIFF(SECOND, t1.timestamp, t2.timestamp))) AS timestamp
   FROM       tab t1
   INNER JOIN tab t2
           ON t1.timestamp < t2.timestamp
          AND t1.user_id IS NOT NULL
          AND t2.status IS NOT NULL
   GROUP BY t1.user_id

What it does is it applies a self join on the two timestamps with user_id and status different from null, then just get the smallest difference in time grouped by user_id.

You'll find a fiddle here.

Upvotes: 1

Related Questions