Stored Procedure Challenges — 3 Problems

DELIMITER //

CREATE PROCEDURE GetCustomerSummary(IN p_customer_id INT)
BEGIN
  SELECT
    c.customer_id,
    CONCAT(c.first_name, ' ', c.last_name) AS full_name,
    c.email,
    c.city,
    c.signup_date,
    COUNT(o.order_id) AS total_orders,
    COALESCE(SUM(CASE WHEN o.status = 'completed'
      THEN o.total_amount ELSE 0 END), 0) AS total_spent,
    COALESCE(ROUND(AVG(CASE WHEN o.status = 'completed'
      THEN o.total_amount END), 2), 0) AS avg_order_value,
    MAX(o.order_date) AS last_order_date,
    DATEDIFF(CURDATE(), MAX(o.order_date)) AS days_since_last_order
  FROM customers c
  LEFT JOIN orders o ON c.customer_id = o.customer_id
  WHERE c.customer_id = p_customer_id
  GROUP BY c.customer_id, c.first_name, c.last_name,
           c.email, c.city, c.signup_date;
END //

DELIMITER ;

-- Test it
CALL GetCustomerSummary(1);
CALL GetCustomerSummary(15);

DELIMITER //

CREATE PROCEDURE CalculateDiscount(
  IN p_order_amount DECIMAL(10,2),
  OUT p_discount_pct DECIMAL(5,2),
  OUT p_discount_amount DECIMAL(10,2),
  OUT p_final_amount DECIMAL(10,2)
)
BEGIN
  IF p_order_amount > 50000 THEN
    SET p_discount_pct = 10.00;
  ELSEIF p_order_amount > 10000 THEN
    SET p_discount_pct = 5.00;
  ELSE
    SET p_discount_pct = 0.00;
  END IF;

  SET p_discount_amount = ROUND(p_order_amount * p_discount_pct / 100, 2);
  SET p_final_amount = p_order_amount - p_discount_amount;
END //

DELIMITER ;

-- TEST CASES: Run each and verify the output

-- TC1: Above 50000 — should get 10%
CALL CalculateDiscount(75000.00, @pct, @disc, @final);
SELECT @pct AS discount_pct, @disc AS discount_amt, @final AS final_amt;
-- Expected: 10.00, 7500.00, 67500.00

-- TC2: Exactly 50000 — boundary test (should get 5%, NOT 10%)
CALL CalculateDiscount(50000.00, @pct, @disc, @final);
SELECT @pct AS discount_pct, @disc AS discount_amt, @final AS final_amt;
-- Expected: 5.00, 2500.00, 47500.00

-- TC3: Between 10001 and 49999 — should get 5%
CALL CalculateDiscount(25000.00, @pct, @disc, @final);
SELECT @pct AS discount_pct, @disc AS discount_amt, @final AS final_amt;
-- Expected: 5.00, 1250.00, 23750.00

-- TC4: Exactly 10000 — boundary (should get 0%)
CALL CalculateDiscount(10000.00, @pct, @disc, @final);
SELECT @pct AS discount_pct, @disc AS discount_amt, @final AS final_amt;
-- Expected: 0.00, 0.00, 10000.00

-- TC5: Small amount
CALL CalculateDiscount(500.00, @pct, @disc, @final);
SELECT @pct AS discount_pct, @disc AS discount_amt, @final AS final_amt;
-- Expected: 0.00, 0.00, 500.00

-- TC6: Zero amount
CALL CalculateDiscount(0, @pct, @disc, @final);
SELECT @pct AS discount_pct, @disc AS discount_amt, @final AS final_amt;
-- Expected: 0.00, 0.00, 0.00

-- TC7: Negative amount — BUG if no validation
CALL CalculateDiscount(-5000.00, @pct, @disc, @final);
SELECT @pct AS discount_pct, @disc AS discount_amt, @final AS final_amt;
-- Expected: Should error or return 0. What actually happens?

-- BUGGY VERSION — Find 3 bugs
DELIMITER //

CREATE PROCEDURE MonthlySalesReport(
  IN p_year INT,
  IN p_month INT
)
BEGIN
  SELECT
    DATE_FORMAT(o.order_date, '%Y-%m') AS report_month,
    COUNT(o.order_id) AS total_orders,
    SUM(o.total_amount) AS total_revenue,
    AVG(o.total_amount) AS avg_order_value,
    COUNT(DISTINCT o.customer_id) AS unique_customers
  FROM orders o
  WHERE YEAR(o.order_date) = p_year
    AND MONTH(o.order_date) = p_month;
    -- Bug 1: Missing GROUP BY
    -- Bug 2: No status filter — includes cancelled/returned orders in revenue
    -- Bug 3: No rounding on AVG — returns 15+ decimal places
END //

DELIMITER ;

-- FIXED VERSION
DELIMITER //

CREATE PROCEDURE MonthlySalesReport(
  IN p_year INT,
  IN p_month INT
)
BEGIN
  SELECT
    DATE_FORMAT(o.order_date, '%Y-%m') AS report_month,
    COUNT(o.order_id) AS total_orders,
    SUM(o.total_amount) AS total_revenue,
    ROUND(AVG(o.total_amount), 2) AS avg_order_value,   -- Fix 3: Round to 2 decimals
    COUNT(DISTINCT o.customer_id) AS unique_customers
  FROM orders o
  WHERE YEAR(o.order_date) = p_year
    AND MONTH(o.order_date) = p_month
    AND o.status = 'completed'                          -- Fix 2: Only completed orders
  GROUP BY DATE_FORMAT(o.order_date, '%Y-%m');           -- Fix 1: Add GROUP BY
END //

DELIMITER ;

-- Test the fixed version
CALL MonthlySalesReport(2024, 6);