Sum Employee Working Time from Ordered Check-In and Check-Out Events

Compute each employee's total working duration by pairing every check-out event with that employee's immediately preceding check-in event. ### Input Table `attendance_events` | Column | Type | Meaning | | --- | --- | --- | | `event_id` | INTEGER | Primary key | | `employee_id` | INTEGER | Non-null employee identifier | | `event_time` | TIMESTAMP | Non-null timestamp with whole-second precision | | `event_type` | TEXT | Either `in` or `out` | ### Output Contract Write one read-only PostgreSQL query returning `employee_id` and `total_seconds`, one row per employee present in the table, ordered by `employee_id` ascending. Sum elapsed seconds for all that employee's completed check-in/check-out pairs. ### Constraints and Clarifications To make the pairing unambiguous, this exercise guarantees that each employee's events, ordered by `(event_time, event_id)`, alternate `in`, `out`, start with `in`, and finish with `out`. There are no unmatched events. Equal timestamps are allowed and can create a zero-duration pair. A work interval may cross midnight; subtract full timestamps rather than time-of-day values. ### Example | event_id | employee_id | event_time | event_type | | --- | --- | --- | --- | | 1 | 10 | 2026-01-01 09:00:00 | in | | 2 | 10 | 2026-01-01 10:00:00 | out | | 3 | 10 | 2026-01-01 10:30:00 | in | | 4 | 10 | 2026-01-01 11:00:00 | out | | 5 | 20 | 2026-01-01 23:30:00 | in | | 6 | 20 | 2026-01-02 00:30:00 | out | Expected result: | employee_id | total_seconds | | --- | --- | | 10 | 5400 | | 20 | 3600 | ```hint Keep both event types while finding neighbors The preceding check-in must still be present when you calculate the previous timestamp for a check-out. Filtering too early can make the previous row another check-out instead. ```