Write SQL for seller and category metrics
MetaData Scientist · Technical Screen
Last updated
This question tests the ability to perform complex aggregations, handle date filtering, and calculate performance ratios while managing potential division-by-zero errors in a multi-table SQL environment.
Problem Statement
You are working with an e-commerce platform's database containing two tables: sales and categories.
Tables
sales
| Column | Type | Description |
|---|---|---|
| sale_id | int | Unique identifier for the sale |
| category_id | int | Foreign key to categories table |
| seller_id | int | Unique identifier for the seller |
| sale_date | date | Date of transaction |
| revenue | decimal | Revenue generated in USD |
categories
| Column | Type | Description |
|---|---|---|
| category_id | int | Unique identifier |
| category_name | varchar | Name of the category (e.g., 'Electronics', 'Home') |
Task
Write a SQL query to identify the top 3 categories by 'conversion efficiency' for the month of October 2023.
Define 'conversion efficiency' as the average revenue per sale for a category. However, exclude any categories that had fewer than 10 total sales in that month, as they are not statistically significant.
Return the category_name, the total_revenue, and the average_revenue_per_sale (rounded to 2 decimal places). Sort the final results by average_revenue_per_sale in descending order.
Example
Input:
sales table has 15 entries for 'Electronics' in Oct 2023 totaling $1500, and 5 entries for 'Books' totaling $100.
Output:
| category_name | total_revenue | average_revenue_per_sale |
|---|---|---|
| Electronics | 1500.00 | 100.00 |
Note: 'Books' is excluded because the count (5) is less than 10.
Hints
Hint 1
Filter the sales data to include only dates between '2023-10-01' and '2023-10-31' before performing your aggregations.
Hint 2
Use a `GROUP BY` clause on the `category_id` and `category_name` to calculate both `SUM(revenue)` and `COUNT(*)` for each category.
Hint 3
Apply the `HAVING` clause to filter out categories with fewer than 10 sales after the aggregation is performed.
Follow-up questions
- How would you modify this query if you needed to compare the 'conversion efficiency' in October 2023 vs September 2023 in a single result set?
- If the revenue column contained NULL values, how would that impact your `SUM` and `COUNT` functions, and how would you adjust the query to handle them?
- What steps would you take to optimize this query if the `sales` table contained billions of rows?
Solution walkthrough is for members
Get the full walkthrough with reference code and complexity analysis, plus Kitty Coach at 300 messages a day. Hints, the code runner and community answers stay free.