Find Lowest Prices for Highly Rated Categories
Company: Capital One
Role: Data Analyst
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Take-home Project
You are given two tables containing product catalog information and purchase records.
### Tables
`products`
| Column | Type | Description |
|---|---:|---|
| `id` | INT | Primary key for the product. |
| `category` | VARCHAR | Product category, such as `apple`, `orange`, or `banana`. |
| `product_name` | VARCHAR | Name of the product. |
`purchases`
| Column | Type | Description |
|---|---:|---|
| `id` | INT | Primary key for the purchase record. |
| `product_id` | INT | Foreign key referencing `products.id`. |
| `price` | DECIMAL(10,2) | Purchase price of the product. |
| `stars` | DECIMAL(2,1) | Customer rating for the purchase. |
### Task
For each product category in `products`, return the lowest purchase price in that category **only if the category has at least one purchase with `stars > 4`**.
If a category does not have any purchase with `stars > 4`, return `0` as the price for that category.
### Assumptions
- Include every category that appears in `products`.
- A category may contain multiple products and each product may have multiple purchase records.
- If a category has at least one purchase with `stars > 4`, compute the minimum `price` across purchases in that category.
- If a category has no purchases, return `0`.
- There is no time filter for this question, so timezone handling is not applicable.
### Required Output
Return one row per category.
| Column | Type | Description |
|---|---:|---|
| `category` | VARCHAR | Product category. |
| `price` | DECIMAL(10,2) or INT | Lowest price for qualifying categories; otherwise `0`. |
Example output format:
| category | price |
|---|---:|
| apple | 0 |
Quick Answer: This question evaluates competency in data manipulation and aggregation using SQL or Python, focusing on grouping, conditional aggregation, and handling related product and purchase records. It is commonly asked for Data Manipulation (SQL/Python) roles to assess practical application of filtering and aggregation logic for per-category summaries rather than purely conceptual understanding.