LeetCode Problem Workspace
Find Stores with Inventory Imbalance
Solve Find Stores with Inventory Imbalance by comparing cheapest and most expensive product stock within each store.
0
Topics
2
Code langs
0
Related
Practice Focus
Medium · Find Stores with Inventory Imbalance core interview pattern
Answer-first summary
Solve Find Stores with Inventory Imbalance by comparing cheapest and most expensive product stock within each store.
Ace coding interviews with Interview AiBoxInterview AiBox guidance for Find Stores with Inventory Imbalance core interview pattern
In Find Stores with Inventory Imbalance, each store must be checked against two boundary products: the cheapest item and the most expensive item in that store. The key is to isolate those rows correctly, then compare their quantities and return only stores where the expensive product has less stock than the cheap one. Most mistakes come from collapsing prices too early or mishandling ties at the minimum or maximum price.
Problem Statement
You are given a stores table with basic store details and an inventory table with product quantity and price for each store. A store is considered imbalanced when, inside that same store, the product with the highest price has a smaller quantity than the product with the lowest price.
The task is to return the stores that satisfy that imbalance rule. The core challenge is not joining tables, but correctly identifying the cheapest and most expensive inventory rows per store, especially when multiple products can share the same boundary price.
Examples
Example 1
Input: See original problem statement.
Output: See original problem statement.
+-------------+---------+ | 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.
Example 2
Input: See original problem statement.
Output: See original problem statement.
+-------------+---------+ | 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.
Example 3
Input: See original problem statement.
Output: See original problem statement.
+----------+----------------+-------------+ | 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 | +----------+----------------+-------------+
Constraints
Solution Approach
Find per-store minimum and maximum prices
Start by grouping inventory by store_id and compute the minimum price and maximum price for each store. This defines the two price boundaries you need to inspect before any quantity comparison happens.
Join boundary prices back to inventory rows
Join the per-store min and max prices back to the inventory table so you can recover the quantities attached to those boundary products. This step is where this problem usually breaks: if a store has multiple products tied at the cheapest or most expensive price, a loose join can duplicate rows or compare the wrong quantities.
Filter imbalanced stores and return store details
After isolating the cheapest-side quantity and the most-expensive-side quantity, keep only stores where expensive_quantity is smaller than cheap_quantity. Then join to stores to output the requested store information in the required order.
Complexity Analysis
| Metric | Value |
|---|---|
| Time | Depends on the final approach |
| Space | Depends on the final approach |
The usual SQL plan scans inventory to compute each store's minimum and maximum price, then scans matching boundary rows again for the final comparison. That makes the runtime linear to the number of inventory rows plus join cost, which is typically handled efficiently with indexing on store_id and price. Extra space comes from the grouped per-store boundary result or any intermediate CTEs used to hold min and max prices.
What Interviewers Usually Probe
- They want you to recognize a per-group boundary comparison, not a global cheapest-versus-expensive check.
- They are testing whether you can preserve row-level quantity after aggregating price by store.
- They expect you to notice tie handling at the minimum or maximum price before writing the final filter.
Common Pitfalls or Variants
Common pitfalls
- Comparing the minimum quantity and maximum quantity instead of the quantity attached to the minimum and maximum price.
- Joining min and max price summaries back to inventory without controlling duplicates when several products share the same boundary price.
- Forgetting that the comparison must stay inside each store, which can accidentally mix products across stores.
Follow-up variants
- Return the exact cheapest and most expensive product names alongside each imbalanced store.
- Treat a store as imbalanced only when every highest-priced tied product has lower stock than every lowest-priced tied product.
- Rank stores by the quantity gap between the cheapest and most expensive boundary products.
FAQ
What is the main SQL pattern in Find Stores with Inventory Imbalance?
The main pattern is per-group boundary selection. You first find each store's minimum and maximum price, then reconnect those boundary prices to their inventory rows so you can compare the correct quantities.
Why is this problem harder than a simple GROUP BY query?
A basic GROUP BY can give you min and max price per store, but it cannot directly tell you the quantity of the products that own those prices. You need an extra join, window function, or equivalent step to recover the row-level quantities.
What tie case should I watch for most carefully?
If multiple products share the cheapest price or the most expensive price, a naive join can create multiple matches and inflate results. Your query must define how to handle those boundary ties so the quantity comparison stays valid.
Can window functions solve this problem cleanly?
Yes. You can rank or filter rows by price within each store to isolate cheapest and most expensive inventory rows, then compare their quantities. This often makes the row-selection logic clearer than stacked aggregates and joins.
What exactly causes a store to be returned?
A store is returned only when the quantity of its most expensive product is lower than the quantity of its cheapest product. The comparison is local to one store and depends on price boundaries, not overall stock extremes.
Solution
Solution 1: Window Functions + Joins
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:
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 result