Analyze User Purchase Behavior in Online Marketplace Data

user_events

+----------+------------+---------------------+-------------+
| user_id  | event_type | event_timestamp     | product_id  |
+----------+------------+---------------------+-------------+
| 101      | view       | 2024-01-02 10:00:00 | 55          |
| 101      | purchase   | 2024-01-02 10:05:00 | 55          |
| 102      | purchase   | 2024-01-03 09:30:00 | 77          |
| 101      | purchase   | 2024-02-01 12:00:00 | 88          |
| 103      | view       | 2024-02-02 08:00:00 | 23          |

Scenario

Online marketplace wants to understand user purchase behavior stored in user_events table.

Question

SQL: For each user, return the first product_id they purchased and the purchase timestamp. SQL: Count the number of distinct users who made at least two purchases on the same day. SQL: Find the top 3 products by total number of purchases. SQL: Calculate the 7-day rolling average of daily purchases overall. Pandas: Given the same data in DataFrame df, compute daily active users (unique user_id per date).

Hints

Use window functions, GROUP BY, DISTINCT, rolling(), and groupby().

First Purchase per User

For each user who made at least one purchase, return the first product they purchased.

Use the user_events table and consider only rows where event_type = 'purchase'. Return these columns:

• user_id
• first_product_id: the product_id from the user's earliest purchase event
• first_purchase_timestamp: the earliest purchase timestamp formatted as YYYY-MM-DD HH24:MI:SS

Order the result by user_id.

Table user_events

column	type	constraints
user_id	INTEGER
event_type	VARCHAR(20)
event_timestamp	TIMESTAMP
product_id	INTEGER

Sample data:

event_timestamp	event_type	product_id	user_id
2024-01-02 10:00:00	view	55	101
2024-01-02 10:05:00	purchase	55	101
2024-01-03 09:30:00	purchase	77	102
2024-02-01 12:00:00	purchase	88	101
2024-02-02 08:00:00	view	23	103

Expected output

Each purchasing user with their earliest purchased product and formatted purchase timestamp.

first_product_id	first_purchase_timestamp	user_id
55	2024-01-02 10:05:00	101
77	2024-01-03 09:30:00	102

Hints

1. Filter to purchase events before ranking.
2. Use ROW_NUMBER() partitioned by user_id and ordered by event_timestamp.
3. Use TO_CHAR(timestamp, ...) for PostgreSQL timestamp formatting.

Two purchases on the same day per user

Count the number of distinct users who made at least two purchases on the same calendar date.

Table user_events

column	type	constraints
user_id	INTEGER
event_type	VARCHAR(20)
event_timestamp	TIMESTAMP
product_id	INTEGER

Sample data:

event_timestamp	event_type	product_id	user_id
2024-01-02 10:00:00	view	55	101
2024-01-02 10:05:00	purchase	55	101
2024-01-03 09:30:00	purchase	77	102
2024-02-01 12:00:00	purchase	88	101
2024-02-02 08:00:00	view	23	103

Expected output

Number of users with at least two purchases on the same calendar date in the sample data.

users_with_2_plus_purchases_same_day
0

Hints

1. Group by user_id and purchase date.
2. Use HAVING COUNT(*) >= 2 to keep dates with at least two purchases.

Top products by purchases

Find the top 3 products by total number of purchases, breaking ties by product_id in ascending order.

Table user_events

column	type	constraints
user_id	INTEGER
event_type	VARCHAR(20)
event_timestamp	TIMESTAMP
product_id	INTEGER

Sample data:

event_timestamp	event_type	product_id	user_id
2024-01-02 10:00:00	view	55	101
2024-01-02 10:05:00	purchase	55	101
2024-01-03 09:30:00	purchase	77	102
2024-02-01 12:00:00	purchase	88	101
2024-02-02 08:00:00	view	23	103

Expected output

Top three products by purchase count from the sample data (ties broken by product_id ascending).

product_id	purchase_count
55	1
77	1
88	1

Hints

1. Filter to rows where event_type = 'purchase'.
2. Group by product_id and order by the purchase count descending, then product_id ascending.

7-day rolling average of daily purchases

Calculate the 7-day rolling average of daily purchases overall, using a window of the current date and the previous 6 calendar days.

Table user_events

column	type	constraints
user_id	INTEGER
event_type	VARCHAR(20)
event_timestamp	TIMESTAMP
product_id	INTEGER

Sample data:

event_timestamp	event_type	product_id	user_id
2024-01-02 10:00:00	view	55	101
2024-01-02 10:05:00	purchase	55	101
2024-01-03 09:30:00	purchase	77	102
2024-02-01 12:00:00	purchase	88	101
2024-02-02 08:00:00	view	23	103

Expected output

Daily rolling average of purchase counts over the previous 7 calendar days (including the current day) for dates with purchases in the sample.

purchase_date	rolling_avg_7d
2024-01-02	1
2024-01-03	1
2024-02-01	1

Hints

1. First aggregate purchases to get a daily count per purchase_date.
2. Then apply a window function with ROWS BETWEEN 6 PRECEDING AND CURRENT ROW to compute the 7-row rolling average.

Daily active users (DAU)

Compute daily active users (count of unique user_id per calendar date) across all event types.

Table user_events

column	type	constraints
user_id	INTEGER
event_type	VARCHAR(20)
event_timestamp	TIMESTAMP
product_id	INTEGER

Sample data:

event_timestamp	event_type	product_id	user_id
2024-01-02 10:00:00	view	55	101
2024-01-02 10:05:00	purchase	55	101
2024-01-03 09:30:00	purchase	77	102
2024-02-01 12:00:00	purchase	88	101
2024-02-02 08:00:00	view	23	103

Expected output

Daily count of unique users with any event in the sample data.

daily_active_users	event_date
1	2024-01-02
1	2024-01-03
1	2024-02-01
1	2024-02-02

Hints

1. Convert event_timestamp to a DATE to group by calendar day.
2. Use COUNT(DISTINCT user_id) to get daily active users.