Off-by-one on range boundaries
Wrong move: Loop endpoints miss first/last candidate.
Usually fails on: Fails on minimal arrays and exact-boundary answers.
Fix: Re-derive loops from inclusive/exclusive ranges before coding.
Build confidence with an intuition-first walkthrough focused on core interview patterns fundamentals.
Table: Prices
+---------------+---------+ | Column Name | Type | +---------------+---------+ | product_id | int | | start_date | date | | end_date | date | | price | int | +---------------+---------+ (product_id, start_date, end_date) is the primary key (combination of columns with unique values) for this table. Each row of this table indicates the price of the product_id in the period from start_date to end_date. For each product_id there will be no two overlapping periods. That means there will be no two intersecting periods for the same product_id.
Table: UnitsSold
+---------------+---------+ | Column Name | Type | +---------------+---------+ | product_id | int | | purchase_date | date | | units | int | +---------------+---------+ This table may contain duplicate rows. Each row of this table indicates the date, units, and product_id of each product sold.
Write a solution to find the average selling price for each product. average_price should be rounded to 2 decimal places. If a product does not have any sold units, its average selling price is assumed to be 0.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: Prices table: +------------+------------+------------+--------+ | product_id | start_date | end_date | price | +------------+------------+------------+--------+ | 1 | 2019-02-17 | 2019-02-28 | 5 | | 1 | 2019-03-01 | 2019-03-22 | 20 | | 2 | 2019-02-01 | 2019-02-20 | 15 | | 2 | 2019-02-21 | 2019-03-31 | 30 | +------------+------------+------------+--------+ UnitsSold table: +------------+---------------+-------+ | product_id | purchase_date | units | +------------+---------------+-------+ | 1 | 2019-02-25 | 100 | | 1 | 2019-03-01 | 15 | | 2 | 2019-02-10 | 200 | | 2 | 2019-03-22 | 30 | +------------+---------------+-------+ Output: +------------+---------------+ | product_id | average_price | +------------+---------------+ | 1 | 6.96 | | 2 | 16.96 | +------------+---------------+ Explanation: Average selling price = Total Price of Product / Number of products sold. Average selling price for product 1 = ((100 * 5) + (15 * 20)) / 115 = 6.96 Average selling price for product 2 = ((200 * 15) + (30 * 30)) / 230 = 16.96
Problem summary: Table: Prices +---------------+---------+ | Column Name | Type | +---------------+---------+ | product_id | int | | start_date | date | | end_date | date | | price | int | +---------------+---------+ (product_id, start_date, end_date) is the primary key (combination of columns with unique values) for this table. Each row of this table indicates the price of the product_id in the period from start_date to end_date. For each product_id there will be no two overlapping periods. That means there will be no two intersecting periods for the same product_id. Table: UnitsSold +---------------+---------+ | Column Name | Type | +---------------+---------+ | product_id | int | | purchase_date | date | | units | int | +---------------+---------+ This table may contain duplicate rows. Each row of this table indicates the date, units, and product_id of each product sold. Write a solution to find the
Start with the most direct exhaustive search. That gives a correctness anchor before optimizing.
Pattern signal: General problem-solving
{"headers":{"Prices":["product_id","start_date","end_date","price"],"UnitsSold":["product_id","purchase_date","units"]},"rows":{"Prices":[[1,"2019-02-17","2019-02-28",5],[1,"2019-03-01","2019-03-22",20],[2,"2019-02-01","2019-02-20",15],[2,"2019-02-21","2019-03-31",30]],"UnitsSold":[[1,"2019-02-25",100],[1,"2019-03-01",15],[2,"2019-02-10",200],[2,"2019-03-22",30]]}}Source-backed implementations are provided below for direct study and interview prep.
// Accepted solution for LeetCode #1251: Average Selling Price
// Auto-generated Java example from rust.
class Solution {
public void exampleSolution() {
}
}
// Reference (rust):
// // Accepted solution for LeetCode #1251: Average Selling Price
// pub fn sql_example() -> &'static str {
// r#"
// -- Accepted solution for LeetCode #1251: Average Selling Price
// # Write your MySQL query statement below
// SELECT
// p.product_id,
// IFNULL(ROUND(SUM(price * units) / SUM(units), 2), 0) AS average_price
// FROM
// Prices AS p
// LEFT JOIN UnitsSold AS u
// ON p.product_id = u.product_id AND purchase_date BETWEEN start_date AND end_date
// GROUP BY 1;
// "#
// }
// Accepted solution for LeetCode #1251: Average Selling Price
// Auto-generated Go example from rust.
func exampleSolution() {
}
// Reference (rust):
// // Accepted solution for LeetCode #1251: Average Selling Price
// pub fn sql_example() -> &'static str {
// r#"
// -- Accepted solution for LeetCode #1251: Average Selling Price
// # Write your MySQL query statement below
// SELECT
// p.product_id,
// IFNULL(ROUND(SUM(price * units) / SUM(units), 2), 0) AS average_price
// FROM
// Prices AS p
// LEFT JOIN UnitsSold AS u
// ON p.product_id = u.product_id AND purchase_date BETWEEN start_date AND end_date
// GROUP BY 1;
// "#
// }
# Accepted solution for LeetCode #1251: Average Selling Price
# Auto-generated Python example from rust.
def example_solution() -> None:
return
# Reference (rust):
# // Accepted solution for LeetCode #1251: Average Selling Price
# pub fn sql_example() -> &'static str {
# r#"
# -- Accepted solution for LeetCode #1251: Average Selling Price
# # Write your MySQL query statement below
# SELECT
# p.product_id,
# IFNULL(ROUND(SUM(price * units) / SUM(units), 2), 0) AS average_price
# FROM
# Prices AS p
# LEFT JOIN UnitsSold AS u
# ON p.product_id = u.product_id AND purchase_date BETWEEN start_date AND end_date
# GROUP BY 1;
# "#
# }
// Accepted solution for LeetCode #1251: Average Selling Price
pub fn sql_example() -> &'static str {
r#"
-- Accepted solution for LeetCode #1251: Average Selling Price
# Write your MySQL query statement below
SELECT
p.product_id,
IFNULL(ROUND(SUM(price * units) / SUM(units), 2), 0) AS average_price
FROM
Prices AS p
LEFT JOIN UnitsSold AS u
ON p.product_id = u.product_id AND purchase_date BETWEEN start_date AND end_date
GROUP BY 1;
"#
}
// Accepted solution for LeetCode #1251: Average Selling Price
// Auto-generated TypeScript example from rust.
function exampleSolution(): void {
}
// Reference (rust):
// // Accepted solution for LeetCode #1251: Average Selling Price
// pub fn sql_example() -> &'static str {
// r#"
// -- Accepted solution for LeetCode #1251: Average Selling Price
// # Write your MySQL query statement below
// SELECT
// p.product_id,
// IFNULL(ROUND(SUM(price * units) / SUM(units), 2), 0) AS average_price
// FROM
// Prices AS p
// LEFT JOIN UnitsSold AS u
// ON p.product_id = u.product_id AND purchase_date BETWEEN start_date AND end_date
// GROUP BY 1;
// "#
// }
Use this to step through a reusable interview workflow for this problem.
Two nested loops check every pair or subarray. The outer loop fixes a starting point, the inner loop extends or searches. For n elements this gives up to n²/2 operations. No extra space, but the quadratic time is prohibitive for large inputs.
Most array problems have an O(n²) brute force (nested loops) and an O(n) optimal (single pass with clever state tracking). The key is identifying what information to maintain as you scan: a running max, a prefix sum, a hash map of seen values, or two pointers.
Review these before coding to avoid predictable interview regressions.
Wrong move: Loop endpoints miss first/last candidate.
Usually fails on: Fails on minimal arrays and exact-boundary answers.
Fix: Re-derive loops from inclusive/exclusive ranges before coding.