Categorizing Product Pricing Tiers
This challenge focuses on using SQL's CASE statement to implement conditional logic for categorizing data. You will be tasked with assigning descriptive price tiers to a list of products based on their price. This is a common task in data analysis and business intelligence, allowing for easier segmentation and reporting.
Problem Description
You are given a table named Products with the following columns:
product_id: A unique identifier for each product (integer).product_name: The name of the product (string).price: The selling price of the product (decimal/numeric).
Your task is to write an SQL query that retrieves all products and adds a new column called price_tier. This price_tier column should categorize each product into one of three tiers based on its price:
- 'Low': If the
priceis less than or equal to 50.00. - 'Medium': If the
priceis greater than 50.00 and less than or equal to 200.00. - 'High': If the
priceis greater than 200.00.
The query should return the product_id, product_name, and the newly created price_tier for all products.
Examples
Example 1:
Input Table: Products
| product_id | product_name | price |
|---|---|---|
| 1 | Gadget A | 25.50 |
| 2 | Widget B | 150.00 |
| 3 | Doodad C | 300.75 |
Output:
| product_id | product_name | price_tier |
|---|---|---|
| 1 | Gadget A | Low |
| 2 | Widget B | Medium |
| 3 | Doodad C | High |
Explanation:
- Gadget A has a price of 25.50, which is <= 50.00, so its tier is 'Low'.
- Widget B has a price of 150.00, which is > 50.00 and <= 200.00, so its tier is 'Medium'.
- Doodad C has a price of 300.75, which is > 200.00, so its tier is 'High'.
Example 2:
Input Table: Products
| product_id | product_name | price |
|---|---|---|
| 4 | Gizmo D | 50.00 |
| 5 | Thingamajig E | 50.01 |
| 6 | Contraption F | 200.00 |
| 7 | Apparatus G | 200.01 |
Output:
| product_id | product_name | price_tier |
|---|---|---|
| 4 | Gizmo D | Low |
| 5 | Thingamajig E | Medium |
| 6 | Contraption F | Medium |
| 7 | Apparatus G | High |
Explanation:
- Gizmo D has a price of exactly 50.00, falling into the 'Low' tier.
- Thingamajig E has a price of 50.01, which is just above 50.00, placing it in the 'Medium' tier.
- Contraption F has a price of exactly 200.00, falling into the 'Medium' tier.
- Apparatus G has a price of 200.01, placing it in the 'High' tier.
Constraints
- The
Productstable will always contain at least one row. - The
pricecolumn will always contain a non-negative numeric value. - The SQL dialect used should support standard
CASEstatement syntax.
Notes
Think about the order of conditions in your CASE statement. Ensure that each product falls into exactly one tier. You should select product_id, product_name, and the calculated price_tier.