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.

Total Working Duration from Check-In/Check-Out Events

Table attendance_events

column	type	constraints
event_id	INTEGER	PRIMARY KEY
employee_id	INTEGER	NOT NULL
event_time	TIMESTAMP	NOT NULL
event_type	TEXT

Sample data:

employee_id	event_id	event_time	event_type
10	1	2026-03-02 08:55:00	in
30	2	2026-03-02 09:00:00	in
20	3	2026-03-03 06:30:00	out
10	4	2026-03-02 12:05:30	out
20	5	2026-03-02 14:00:00	in
20	6	2026-03-02 18:00:00	out
10	7	2026-03-02 13:00:00	in
30	8	2026-03-02 09:00:00	out
10	9	2026-03-02 17:45:15	out
30	10	2026-03-02 09:00:00	in
30	11	2026-03-02 11:00:00	out
20	12	2026-03-02 22:00:00	in

Expected output

employee_id	total_seconds
10	28545
20	45000
30	7200

Hints

1. 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.