Calculate Monthly Conversion Rate and Average Order Value

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a Medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Onsite rounds at TikTok.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at TikTok during technical interviews.

Question

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.

PracHub · Accepted Answer

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.

Quick Overview

Quick Overview