Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation – Advanced Level

Answer: Common Table Expressions

Explore how to use common table expressions with CASE expressions to classify suppliers by risk level and filter results using SQL. This lesson helps you understand building reusable and readable queries to handle complex data classifications and custom sorting in MySQL.

We'll cover the following...

Solution

The solution is given below:

MySQL 8.0
WITH SupplierPerformance AS (
    SELECT SupplierID, SupplierName, OnTimeDeliveryRate, AvgLeadTime,
           CASE
                WHEN OnTimeDeliveryRate < 96 OR AvgLeadTime > 5 THEN 'High'
                WHEN OnTimeDeliveryRate BETWEEN 96 AND 97.99
                     AND AvgLeadTime BETWEEN 4 AND 5 THEN 'Medium'
                ELSE 'Low'
           END AS RiskLevel
    FROM   Suppliers
)
SELECT    SupplierID, SupplierName, OnTimeDeliveryRate, AvgLeadTime, RiskLevel
FROM      SupplierPerformance
WHERE     RiskLevel IN ('High', 'Medium')
ORDER BY  CASE RiskLevel
            WHEN 'High' THEN 1
            WHEN 'Medium' THEN 2
            ELSE 3
          END,
          SupplierName;

Explanation

The explanation of the solution code is given below:

  • Line 1: This starts a common table expression with WITH SupplierPerformance AS (...). This defines a named temporary result set called SupplierPerformance that is only visible to the main SELECT that follows.

  • Line 2: This uses SELECT to list all required columns from the base table in one line: SupplierID, SupplierName, OnTimeDeliveryRate, and AvgLeadTime. These are the core supplier identifiers and delivery metrics that the CTE will work with.

  • Lines 3–7: These introduce a CASE expression that classifies each supplier into a risk category:

    • Line 4: If OnTimeDeliveryRate is less than 96 or AvgLeadTime is greater than 5, the supplier is labeled 'High' risk.

    • Lines 5–6: If OnTimeDeliveryRate is between 96 and 97.99 and AvgLeadTime is between 4 and 5, the supplier is labeled 'Medium' risk.

    • Line 7: All remaining suppliers are classified as 'Low' risk by the ELSE branch. ...