SQL
Calculate Time Differences Between Consecutive Events
Learn to calculate the duration between successive events for each user or item in your SQL database using the LAG() window function, perfect for sequence analysis.
SELECT
event_id,
user_id,
event_timestamp,
event_timestamp - LAG(event_timestamp) OVER (PARTITION BY user_id ORDER BY event_timestamp) AS time_since_previous_event
FROM user_events
ORDER BY user_id, event_timestamp;
How it works: This query calculates the time difference between consecutive events for each user. The `LAG(event_timestamp)` window function retrieves the `event_timestamp` from the previous row within the current partition. `PARTITION BY user_id` ensures that the 'previous row' is relative to the current `user_id`, and `ORDER BY event_timestamp` defines the sequence. This is useful for analyzing user behavior sequences or processing times.