Calculate User Revenue and Session Duration in Python

events

+---------+------------+---------+---------------------+
| user_id | event_type | revenue | timestamp           |
+---------+------------+---------+---------------------+
| 101     | view       | 0.00    | 2023-01-01 10:00:00 |
| 101     | purchase   | 29.99   | 2023-01-01 10:05:12 |
| 202     | view       | 0.00    | 2023-01-01 11:20:33 |
| 202     | purchase   | 15.50   | 2023-01-01 11:22:10 |
| 202     | logout     | 0.00    | 2023-01-01 11:30:45 |
+---------+------------+---------+---------------------+

Scenario

An e-commerce company stores clickstream events in a Python list of JSON objects; analysts need per-user revenue and average session duration for reporting.

Question

Write a Python function that takes a list of dictionaries each with keys ['user_id', 'event_type', 'revenue', 'timestamp'] and returns, for every user, total_revenue and average_session_duration (in seconds). Explain complexity.

Hints

Use one pass with defaultdict. A session starts with the first event and ends with the last for that user; avoid multiple sorts.

Per-user revenue and session duration

For each user in the events table, compute total_revenue (sum of revenue) and average_session_duration in seconds, where a session is defined as the time from the user’s first event to their last event.

Table events

column	type	constraints
user_id	INTEGER
event_type	VARCHAR(20)
revenue	DECIMAL(10,2)
timestamp	TIMESTAMP

Sample data:

event_type	revenue	timestamp	user_id
view	0	2023-01-01 10:00:00	101
purchase	29.99	2023-01-01 10:05:12	101
view	0	2023-01-01 11:20:33	202
purchase	15.5	2023-01-01 11:22:10	202
logout	0	2023-01-01 11:30:45	202

Expected output

One row per user with the sum of revenue (total_revenue) and the session duration in seconds computed as MAX(timestamp) - MIN(timestamp). With the provided data, each user has a single session, so the average_session_duration equals that session’s duration.

average_session_duration	total_revenue	user_id
312	29.99	101
612	15.5	202

Hints

1. Group rows by user_id to aggregate all events per user.
2. Compute session duration as the difference between MAX(timestamp) and MIN(timestamp) per user, then convert that interval to seconds.