Clean and Summarize User Purchase Data Efficiently

transactions

+-----------+---------------------+-----------+--------+
| user_id   | txn_timestamp       | txn_value | txn_id |
+-----------+---------------------+-----------+--------+
| 201       | 2023-09-01 09:00:00 | 15.50     | 1      |
| 201       | 2023-09-01 09:05:00 | 17.00     | 2      |
| 202       | 2023-09-01 10:00:00 | 20.00     | 3      |
| 201       | 2023-09-02 11:00:00 | 5.00      | 4      |
| 203       | 2023-09-02 11:05:00 | 22.00     | 5      |
+-----------+---------------------+-----------+--------+

Scenario

You have a pandas DataFrame with user purchase history and need to clean and summarize it for analysts.

Question

a) Implement a function that removes every user who has fewer than 100 transactions in any calendar month. b) Implement a second function that returns the average time between consecutive transactions, in seconds, for each remaining user.

Hints

Group by user & month; use shift to compute timedeltas; convert Timedelta to seconds via .dt.total_seconds().

Filter users by monthly transaction count and compute average time between transactions

You are given a transactions table with user purchase history. Write a single SQL query that:

a) Removes every user who has fewer than 100 transactions in any calendar month.

b) For the remaining users, returns the average time between consecutive transactions, in seconds, for each user.

Return one row per remaining user, with their user_id and the average number of seconds between their consecutive transactions.

Table transactions

column	type	constraints
user_id	INTEGER	NOT NULL
txn_timestamp	TIMESTAMP	NOT NULL
txn_value	DECIMAL(10,2)	NOT NULL
txn_id	INTEGER	PRIMARY KEY, NOT NULL

Sample data:

txn_id	txn_timestamp	txn_value	user_id
1	2023-09-01 09:00:00	15.5	201
2	2023-09-01 09:05:00	17	201
3	2023-09-01 10:00:00	20	202
4	2023-09-02 11:00:00	5	201
5	2023-09-02 11:05:00	22	203

Expected output

One row per remaining user with the average number of seconds between their consecutive transactions. Given the sample data, every user has a month with fewer than 100 transactions, so all users are disqualified and the result set is empty.

Hints

1. First, aggregate by user_id and calendar month (e.g., using date_trunc('month', txn_timestamp)) to count monthly transactions.
2. Disqualify any user who has at least one month with a transaction count less than 100.
3. Use LAG over (PARTITION BY user_id ORDER BY txn_timestamp) to access each user's previous transaction timestamp.
4. Compute the time difference in seconds with EXTRACT(EPOCH FROM (current_ts - previous_ts)) and then average these per user.