Find Stores with Inventory Imbalance solution and explanation
The problem
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
3different 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 | | 2 | Suburb Mall | Chicago | Phone | Case | 25.00 | | 1 | Downtown Tech | New York | Laptop | Mouse | 10.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 (≥ 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 (≥ 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 (≥ 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
Approach overview
Problem Overview: You get store and inventory tables. For each store, you compare stock levels of its most expensive and least expensive products, and return the stores that meet the imbalance condition. The core work is ranking products by price inside each store, then comparing the extremes.
Approach 1: Self-Join on Min and Max Price Subqueries (O(n^2) worst case, O(n) space)
Compute the minimum and maximum price per store in one subquery. Then join the inventory table back to it twice, once to pick the row matching the max price and once for the min price. The join condition is store_id plus price equality. This works, but each store requires several scans and the joins can multiply rows when prices tie. Use it only to understand the logic before you move to window functions.
Approach 2: Window Functions with ROW_NUMBER() and Joins (O(n log n) time, O(n) space)
Partition the inventory table by store_id and rank products by price using ROW_NUMBER() in ascending order and again in descending order. Filter to rank 1 in each direction. You now have exactly one min-price row and one max-price row per store. Join those two derived sets on store_id, then apply the imbalance condition to the quantities. The database sorts within each partition, so the cost is O(n log n). This is the approach to write in an interview.
Recommended for interviews: Interviewers expect the window function version. The self-join shows you understand how the extremes relate to each store, which proves the logic. The ROW_NUMBER() version shows you can write set-based SQL without repeated scans, which proves skill. Mention tie handling early. If two products share the max price, pick a deterministic tiebreaker such as product_id, or the result changes between runs. For more practice, see SQL, window functions, and joins.
Solution
Thinking
Imbalance means a store has at least three products and the most expensive item's stock is strictly below the cheapest item's. A manual per-store scan easily mishandles ties on price.
Keep stores with enough distinct products. Sort inventory by
(store_id,price,quantity)and take the first row per store in each direction; quantity descending breaks price ties uniquely.Join the two frames, keep rows whose expensive quantity is smaller, round the ratio to two decimals, join store metadata, and sort by ratio then name.
We can use window functions to calculate the most expensive and cheapest products for each store, and use joins to filter out stores with inventory imbalance. The specific steps are as follows:
- Calculate the most expensive product for each store: Use the
RANK()window function to sort by price in descending order, and in case of the same price, sort by quantity in descending order, selecting the product ranked first. - Calculate the cheapest product for each store: Use the
RANK()window function to sort by price in ascending order, and in case of the same price, sort by quantity in descending order, selecting the product ranked first. - Filter stores with at least 3 different products: Use the
COUNT()window function to count the number of products for each store, and filter out stores with a count greater than or equal to 3. - Join most expensive and cheapest products: Join the results of the most expensive and cheapest products, ensuring that the quantity of the most expensive product is less than the quantity of the cheapest product.
- Calculate imbalance ratio: Calculate the ratio of the cheapest product quantity to the most expensive product quantity, and round it to two decimal places.
- Join store information: Join the results with the store information table to get store names and locations.
- Sort results: Sort by imbalance ratio in descending order, then by store name in ascending order.
# Write your MySQL query statement below
WITH
T AS (
SELECT
store_id,
product_name,
quantity,
RANK() OVER (
PARTITION BY store_id
ORDER BY price DESC, quantity DESC
) rk1,
RANK() OVER (
PARTITION BY store_id
ORDER BY price, quantity DESC
) rk2,
COUNT(1) OVER (PARTITION BY store_id) cnt
FROM inventory
),
P1 AS (
SELECT *
FROM T
WHERE rk1 = 1 AND cnt >= 3
),
P2 AS (
SELECT *
FROM T
WHERE rk2 = 1
)
SELECT
s.store_id store_id,
store_name,
location,
p1.product_name most_exp_product,
p2.product_name cheapest_product,
ROUND(p2.quantity / p1.quantity, 2) imbalance_ratio
FROM
P1 p1
JOIN P2 p2 ON p1.store_id = p2.store_id AND p1.quantity < p2.quantity
JOIN stores s ON p1.store_id = s.store_id
ORDER BY imbalance_ratio DESC, store_name;import pandas as pd
def find_inventory_imbalance(
stores: pd.DataFrame, inventory: pd.DataFrame
) -> pd.DataFrame:
# Step 1: Identify stores with at least 3 products
store_counts = inventory["store_id"].value_counts()
valid_stores = store_counts[store_counts >= 3].index
# Step 2: Find most expensive product for each valid store
# Sort by price (descending) then quantity (descending) and take first record per store
most_expensive = (
inventory[inventory["store_id"].isin(valid_stores)]
.sort_values(["store_id", "price", "quantity"], ascending=[True, False, False])
.groupby("store_id")
.first()
.reset_index()
)
# Step 3: Find cheapest product for each store
# Sort by price (ascending) then quantity (descending) and take first record per store
cheapest = (
inventory.sort_values(
["store_id", "price", "quantity"], ascending=[True, True, False]
)
.groupby("store_id")
.first()
.reset_index()
)
# Step 4: Merge the two datasets on store_id
merged = pd.merge(
most_expensive, cheapest, on="store_id", suffixes=("_most", "_cheap")
)
# Step 5: Filter for cases where cheapest product has higher quantity than most expensive
result = merged[merged["quantity_most"] < merged["quantity_cheap"]].copy()
# Step 6: Calculate imbalance ratio (cheapest quantity / most expensive quantity)
result["imbalance_ratio"] = (
result["quantity_cheap"] / result["quantity_most"]
).round(2)
# Step 7: Merge with store information to get store names and locations
result = pd.merge(result, stores, on="store_id")
# Step 8: Select and rename columns for final output
result = result[
[
"store_id",
"store_name",
"location",
"product_name_most",
"product_name_cheap",
"imbalance_ratio",
]
].rename(
columns={
"product_name_most": "most_exp_product",
"product_name_cheap": "cheapest_product",
}
)
# Step 9: Sort by imbalance ratio (descending) then store name (ascending)
result = result.sort_values(
["imbalance_ratio", "store_name"], ascending=[False, True]
).reset_index(drop=True)
return resultComplexity analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Self-Join on Min/Max Subqueries | O(n^2) worst case | O(n) | Small tables, or to understand the logic first |
| Window Functions + Joins | O(n log n) | O(n) | General case and interviews |
Frequently asked questions
Is Find Stores with Inventory Imbalance easy or hard?
It is a medium-difficulty problem with a 51.3% acceptance rate. The difficulty comes from correctly isolating the min and max price rows per store and handling ties. Once you know window functions, the query is short.
Find Stores with Inventory Imbalance Python or Java solution?
This is a SQL problem, so the solution is a query, not Python or Java code. For Pandas, use groupby on store_id, then idxmin and idxmax on price to pick the extreme rows, and merge the results. The SQL version with ROW_NUMBER() and joins is the one interviewers expect.
How to solve Find Stores with Inventory Imbalance in O(n log n)?
Partition the inventory table by store_id and assign ROW_NUMBER() by price in both directions. Keep rank 1 for each direction, which gives one min-price and one max-price row per store. Join these two sets on store_id, compare their quantities, and filter stores that meet the imbalance condition. Add a tiebreaker like product_id so ties return consistent results.
What is the best approach for Find Stores with Inventory Imbalance?
The best approach uses window functions with joins. Apply ROW_NUMBER() partitioned by store_id, ordered by price ascending and descending, to isolate the cheapest and most expensive product per store. Then join those two result sets on store_id and apply the imbalance condition.
What data structure is used in Find Stores with Inventory Imbalance?
The problem uses relational tables, not a classic data structure. The store and inventory tables are the input, and the solution relies on partitioned window functions and joins. Internally, the database sorts each partition by price to find the extremes.
What is the time complexity of Find Stores with Inventory Imbalance?
The optimal window function solution runs in O(n log n) time, where n is the number of inventory rows. The cost comes from sorting rows within each store partition. Space complexity is O(n) for the intermediate ranked results.
Is Find Stores with Inventory Imbalance asked at Google, Amazon, or Meta?
This problem is tagged General on LeetCode, so it has no confirmed company tag in our data. Window function questions on store and inventory schemas do show up in SQL rounds at many large tech and e-commerce companies. Check recent interview reports on your target company before you plan around it.