Find stores with inventory imbalance

#
# Table: stores
# +-------------+---------+
# | Column Name | Type    |
# +-------------+---------+
# | store_id    | int     |
# | store_name  | varchar |
# | location    | varchar |
# +-------------+---------+
# store_id is the unique identifier for this table.
# Each row contains information about a store and its location.
# Table: inventory
# +-------------+---------+
# | Column Name | Type    |
# +-------------+---------+
# | inventory_id| int     |
# | store_id    | int     |
# | product_name| varchar |
# | quantity    | int     |
# | price       | decimal |
# +-------------+---------+
# inventory_id is the unique identifier for this table.
# Each row represents the inventory of a specific product at a specific store.
# Write a solution to find stores that have inventory imbalance - stores where the most expensive product has lower stock than the cheapest product.
# For each store, identify the most expensive product (highest price) and its quantity
# For each store, identify the cheapest product (lowest price) and its quantity
# A store has inventory imbalance if the most expensive product's quantity is less than the cheapest product's quantity
# Calculate the imbalance ratio as (cheapest_quantity / most_expensive_quantity)
# Round the imbalance ratio to 2 decimal places
# Only include stores that have at least 3 different products
# Return the result table ordered by imbalance ratio in descending order, then by store name in ascending order.
# The result format is in the following example.
# Example:
# Input:
# stores table:
# +----------+----------------+-------------+
# | store_id | store_name     | location    |
# +----------+----------------+-------------+
# | 1        | Downtown Tech  | New York    |
# | 2        | Suburb Mall    | Chicago     |
# | 3        | City Center    | Los Angeles |
# | 4        | Corner Shop    | Miami       |
# | 5        | Plaza Store    | Seattle     |
# +----------+----------------+-------------+
# inventory table:
# +--------------+----------+--------------+----------+--------+
# | inventory_id | store_id | product_name | quantity | price  |
# +--------------+----------+--------------+----------+--------+
# | 1            | 1        | Laptop       | 5        | 999.99 |
# | 2            | 1        | Mouse        | 50       | 19.99  |
# | 3            | 1        | Keyboard     | 25       | 79.99  |
# | 4            | 1        | Monitor      | 15       | 299.99 |
# | 5            | 2        | Phone        | 3        | 699.99 |
# | 6            | 2        | Charger      | 100      | 25.99  |
# | 7            | 2        | Case         | 75       | 15.99  |
# | 8            | 2        | Headphones   | 20       | 149.99 |
# | 9            | 3        | Tablet       | 2        | 499.99 |
# | 10           | 3        | Stylus       | 80       | 29.99  |
# | 11           | 3        | Cover        | 60       | 39.99  |
# | 12           | 4        | Watch        | 10       | 299.99 |
# | 13           | 4        | Band         | 25       | 49.99  |
# | 14           | 5        | Camera       | 8        | 599.99 |
# | 15           | 5        | Lens         | 12       | 199.99 |
# +--------------+----------+--------------+----------+--------+
# Output:
# +----------+----------------+-------------+------------------+--------------------+------------------+
# | store_id | store_name     | location    | most_exp_product | cheapest_product   | imbalance_ratio  |
# +----------+----------------+-------------+------------------+--------------------+------------------+
# | 3        | City Center    | Los Angeles | Tablet           | Stylus             | 40.00            |
# | 1        | Downtown Tech  | New York    | Laptop           | Mouse              | 10.00            |
# | 2        | Suburb Mall    | Chicago     | Phone            | Case               | 25.00            |
# +----------+----------------+-------------+------------------+--------------------+------------------+
# Explanation:
# Downtown Tech (store_id = 1):
# Most expensive product: Laptop ($999.99) with quantity 5
# Cheapest product: Mouse ($19.99) with quantity 50
# Inventory imbalance: 5 < 50 (expensive product has lower stock)
# Imbalance ratio: 50 / 5 = 10.00
# Has 4 products (&ge; 3), so qualifies
# Suburb Mall (store_id = 2):
# Most expensive product: Phone ($699.99) with quantity 3
# Cheapest product: Case ($15.99) with quantity 75
# Inventory imbalance: 3 < 75 (expensive product has lower stock)
# Imbalance ratio: 75 / 3 = 25.00
# Has 4 products (&ge; 3), so qualifies
# City Center (store_id = 3):
# Most expensive product: Tablet ($499.99) with quantity 2
# Cheapest product: Stylus ($29.99) with quantity 80
# Inventory imbalance: 2 < 80 (expensive product has lower stock)
# Imbalance ratio: 80 / 2 = 40.00
# Has 3 products (&ge; 3), so qualifies
# Stores not included:
# Corner Shop (store_id = 4): Only has 2 products (Watch, Band) - doesn't meet minimum 3 products requirement
# Plaza Store (store_id = 5): Only has 2 products (Camera, Lens) - doesn't meet minimum 3 products requirement
# The Results table is ordered by imbalance ratio in descending order, then by store name in ascending order
#

# Write your MySQL query statement below

WITH store_products AS (
  SELECT store_id, COUNT(*) AS product_count
  FROM inventory
  GROUP BY store_id
  HAVING COUNT(*) >= 3
),
most_expensive AS (
  SELECT i.store_id, i.product_name AS most_exp_product, i.quantity AS exp_quantity
  FROM inventory i
  JOIN (
    SELECT store_id, MAX(price) AS max_price
    FROM inventory
    GROUP BY store_id
  ) m ON i.store_id = m.store_id AND i.price = m.max_price
),
cheapest AS (
  SELECT i.store_id, i.product_name AS cheapest_product, i.quantity AS cheap_quantity
  FROM inventory i
  JOIN (
    SELECT store_id, MIN(price) AS min_price
    FROM inventory
    GROUP BY store_id
  ) m ON i.store_id = m.store_id AND i.price = m.min_price
)
SELECT s.store_id, s.store_name, s.location,
  me.most_exp_product, c.cheapest_product,
  ROUND(c.cheap_quantity / me.exp_quantity, 2) AS imbalance_ratio
FROM stores s
JOIN store_products sp ON s.store_id = sp.store_id
JOIN most_expensive me ON s.store_id = me.store_id
JOIN cheapest c ON s.store_id = c.store_id
WHERE me.exp_quantity < c.cheap_quantity
ORDER BY imbalance_ratio DESC, s.store_name ASC;