GROUP BY uninterrupted sequence of logs for same location

Question

I'm having a hard time describing my problem or finding it via Google.

I want to be able to group rows based on location and employee, but the group should be split if a row with the same location occurs.

Basically a way to determine shifts. If another employee logs a row at the same location then the last created_at should be the end date of the previous employee.

Starting from this (employee_logs):

location | employee | created_at
---------+----------+--------------------
A        | Bart     | 2015-09-01 09:00:00
B        | Homer    | 2015-09-01 09:05:00
A        | Bart     | 2015-09-01 09:30:00
A        | Bart     | 2015-09-01 10:00:00
A        | Homer    | 2015-09-01 10:05:00
A        | Homer    | 2015-09-01 11:05:00
A        | Bart     | 2015-09-01 12:00:00
A        | Bart     | 2015-09-01 14:00:00

The desired result is (shifts):

location | employee | start               | end
---------+----------+---------------------+---------------------
A        | Bart     | 2015-09-01 09:00:00 | 2015-09-01 10:00:00
B        | Homer    | 2015-09-01 09:05:00 | 2015-09-01 09:05:00 
A        | Homer    | 2015-09-01 10:05:00 | 2015-09-01 11:05:00
A        | Bart     | 2015-09-01 12:00:00 | 2015-09-01 14:00:00

The hard part is that the sequence should be interrupted when another person creates a log at the same location. I can't seem to solve this with the usual techniques.

Answer 1

SELECT location, employee, min(created_at) AS start_ts, max(created_at) AS end_ts
FROM  (
   SELECT location, employee, created_at
        , row_number() OVER (PARTITION BY location ORDER BY created_at)
        - row_number() OVER (PARTITION BY location, employee ORDER BY created_at) AS grp
   FROM   employee_logs
   ) sub
GROUP  BY location, employee, grp
ORDER  BY min(created_at), max(created_at);

This interrupts a sequence when a different employee logs at the same location. It does not interrupt when the same employee logs at a different location (which might also make sense, but you didn't mention that).

In the subquery, have an running number over all rows for the same location and subtract the running number over rows for the same location with the same employee. The result grp is a number without meaning, that denotes each group of rows forming a sequence together.

Aggregate in the outer SELECT. It is important to remember that grp is only unique per (location, employee), so you have to group by all three columns.

SQL Fiddle.

Related, with ample explanation:

• Select longest continuous sequence