Calculate Monthly Conversion Rate and Average Order Value
Company: TikTok
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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.
Overview: This question evaluates the ability to compute monthly conversion rates and average order value from event and transaction data, testing skills in time-based aggregation, joining datasets, distinct user counting, and metric calculation.
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.
Tables
page_views(user_id INTEGER, page_id INTEGER, view_timestamp TIMESTAMP)
purchases(purchase_id INTEGER, user_id INTEGER, amount DECIMAL(10,2), purchase_timestamp TIMESTAMP)
Hints
- Use date_trunc to bucket by month
- LEFT JOIN page_views to purchases on user_id and month