Clean and Analyze User Transactions with Python Functions

transactions

+---------+---------------------+---------+
| user_id | trans_ts            | amount  |
+---------+---------------------+---------+
| 11      |2024-06-03 10:00:00  | 25.80   |
| 11      |2024-06-03 10:05:00  | 10.50   |
| 12      |2024-06-03 12:00:00  | 40.00   |
| 11      |2024-06-04 09:00:00  | 15.00   |
| 12      |2024-06-05 13:20:00  | 33.30   |
+---------+---------------------+---------+

Scenario

Analyst must clean monthly transaction logs and derive user-level features for downstream modeling.

Question

Implement a Python function that removes users with fewer than 100 transactions per calendar month.

Implement another function that returns each user's average time between consecutive transactions in seconds.

Hints

Use pandas groupby with size()/filter and shift() on sorted timestamps; convert Timedelta to .dt.total_seconds().

Filter user-months by volume

Return all transactions that occur in user-months where the user has at least 100 transactions in that calendar month.

Table transactions

column	type	constraints
user_id	INTEGER
trans_ts	TIMESTAMP
amount	DECIMAL(10,2)

Sample data:

amount	trans_ts	user_id
25.8	2024-06-03 10:00:00	11
10.5	2024-06-03 10:05:00	11
40	2024-06-03 12:00:00	12
15	2024-06-04 09:00:00	11
33.3	2024-06-05 13:20:00	12

Expected output

All transactions from user-months that have at least 100 transactions. With the sample data, no user-month reaches 100 transactions, so the result is empty.

Hints

1. COUNT per user and calendar month using date_trunc('month', trans_ts).
2. Join the monthly counts back to the base table and filter by count >= 100.

Average inter-transaction seconds

Compute each user's average time between consecutive transactions in seconds.

Table transactions

column	type	constraints
user_id	INTEGER
trans_ts	TIMESTAMP
amount	DECIMAL(10,2)

Sample data:

amount	trans_ts	user_id
25.8	2024-06-03 10:00:00	11
10.5	2024-06-03 10:05:00	11
40	2024-06-03 12:00:00	12
15	2024-06-04 09:00:00	11
33.3	2024-06-05 13:20:00	12

Expected output

Per-user average seconds between consecutive transactions. For the sample data, user 11 has two gaps (300s and 82500s) averaging 41400.0; user 12 has one gap of 177600.0 seconds.

avg_inter_txn_seconds	user_id
41400	11
177600	12

Hints

1. Use LAG to get the previous timestamp per user.
2. Convert intervals to seconds with EXTRACT(EPOCH FROM ...), then average.