Calculate Monthly Conversion Rate and Average Order Value

page_views

+-----------+---------+---------------------+
| user_id   | page_id | view_timestamp      |
+-----------+---------+---------------------+
| 101       | 12      | 2023-05-01 10:05:00 |
| 102       | 15      | 2023-05-02 14:20:00 |
| 101       | 18      | 2023-05-04 09:10:00 |
+-----------+---------+---------------------+

purchases

+-------------+---------+--------+---------------------+
| purchase_id | user_id | amount | purchase_timestamp  |
+-------------+---------+--------+---------------------+
| 1           | 101     | 19.99  | 2023-05-01 10:12:00 |
| 2           | 103     | 29.99  | 2023-05-03 12:45:00 |
| 3           | 101     | 9.99   | 2023-05-04 09:15:00 |
+-------------+---------+--------+---------------------+

Scenario

E-commerce platform wants to track how many monthly site visitors convert into paying customers.

Question

Using the available tables, write a SQL query that returns each month, the number of distinct visitors, the number of purchasers, and the conversion rate (purchasers/visitors). Extend the query to include average order value per month.

Hints

Use date_trunc, LEFT JOIN page_views to purchases, aggregate with COUNT(DISTINCT) and SUM/AVG.

Monthly Conversion Rate and Average Order Value

Using the page_views and purchases tables, write a SQL query that returns, for each month, the number of distinct visitors (users with at least one page view that month), the number of purchasers (those visitors who also made at least one purchase in the same month), the conversion rate (purchasers / distinct visitors), and the average order value (total revenue / total orders) per month.

Table page_views

column	type	constraints
user_id	INTEGER
page_id	INTEGER
view_timestamp	TIMESTAMP

Sample data:

page_id	user_id	view_timestamp
12	101	2023-05-01 10:05:00
15	102	2023-05-02 14:20:00
18	101	2023-05-04 09:10:00

Table purchases

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

Sample data:

amount	purchase_id	purchase_timestamp	user_id
19.99	1	2023-05-01 10:12:00	101
29.99	2	2023-05-03 12:45:00	103
9.99	3	2023-05-04 09:15:00	101

Expected output

One row per month with: month start date, distinct_visitors (unique users with at least one page view that month), purchasers (those visitors who also made at least one purchase in the same month), conversion_rate = purchasers / distinct_visitors, and average_order_value = total revenue divided by total orders among those purchasing visitors.

average_order_value	conversion_rate	distinct_visitors	month	purchasers
14.99	0.5	2	2023-05-01	1

Hints

1. Use date_trunc to bucket by month
2. LEFT JOIN page_views to purchases on user_id and month
3. Aggregate with COUNT(DISTINCT) and SUM/AVG
4. Deduplicate by user and month before joining to avoid double counting