sqlbeginner10 minutes

Refactor SQL Query to Optimize Simple SELECT with CASE

Improve the readability and efficiency of a beginner-level SQL query that uses multiple CASE statements to classify product sales data without changing its behavior.

Challenge prompt

You are given a SQL query that retrieves product sales data and categorizes the sales amount using multiple CASE statements, but the query has redundant conditions and can be simplified. Refactor the query to eliminate unnecessary repetition and improve clarity while keeping the exact same output results.

Guidance

• Look for repeated CASE conditions that can be simplified or combined.
• Ensure that the refactored query returns exactly the same results as the original.
• Focus on improving readability by reducing nested or duplicated conditions.

Hints

• Try using one CASE statement with multiple WHEN clauses instead of multiple CASE statements.
• Look for common conditions inside CASE expressions that can be merged.

Starter code

SELECT product_id, sales_amount,
  CASE WHEN sales_amount > 1000 THEN 'High'
       ELSE 'Low'
  END AS sales_category,
  CASE WHEN sales_amount > 1000 THEN 'High'
       WHEN sales_amount BETWEEN 500 AND 1000 THEN 'Medium'
       ELSE 'Low'
  END AS sales_class
FROM product_sales;

Expected output

product_id | sales_amount | sales_category | sales_class -----------+--------------+----------------+------------ 1 | 1200 | High | High 2 | 700 | Low | Medium 3 | 300 | Low | Low

Core concepts

CASE statementsSQL query refactoringquery readability

Challenge a Friend

Send this duel to someone else and see if they can solve it.