Average Selling Price - Practice Coding Problems

Average Selling Price - Problem

Database Easy

You're tasked with calculating the average selling price for each product in a company's inventory system. The challenge is that product prices change over time, and you need to match each sale with the correct price based on when it occurred.

You have two tables:

Prices: Contains price information for products during specific date ranges
UnitsSold: Contains sales data with purchase dates and quantities

Goal: Calculate the weighted average selling price for each product, where the weight is the number of units sold at each price point.

Key Rules:

Round results to 2 decimal places
If a product has no sales, its average price is 0
Price periods never overlap for the same product

Input & Output

example_1.sql — Basic Case

$ Input: Prices: | 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: | 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.67 | | 2 | 16.96 |

💡 Note: Product 1: (100 units × $5) + (15 units × $20) = $800 total revenue, 115 total units → $800/115 = $6.96 ≈ $6.67. Product 2: (200 units × $15) + (30 units × $30) = $3900 total revenue, 230 total units → $3900/230 = $16.96.

example_2.sql — Product with No Sales

$ Input: Prices: | product_id | start_date | end_date | price | |------------|------------|------------|-------| | 1 | 2019-02-17 | 2019-02-28 | 5 | | 2 | 2019-02-01 | 2019-02-20 | 15 | UnitsSold: | product_id | purchase_date | units | |------------|---------------|-------| | 1 | 2019-02-25 | 100 |

› Output: | product_id | average_price | |------------|---------------| | 1 | 5.00 | | 2 | 0.00 |

💡 Note: Product 1 has sales: 100 units × $5 = $5.00 average. Product 2 has no sales, so average price is $0.00 by default.

example_3.sql — Single Product Multiple Periods

$ Input: Prices: | product_id | start_date | end_date | price | |------------|------------|------------|-------| | 1 | 2019-01-01 | 2019-01-15 | 10 | | 1 | 2019-01-16 | 2019-01-31 | 20 | | 1 | 2019-02-01 | 2019-02-28 | 30 | UnitsSold: | product_id | purchase_date | units | |------------|---------------|-------| | 1 | 2019-01-10 | 50 | | 1 | 2019-01-20 | 30 | | 1 | 2019-02-15 | 20 |

› Output: | product_id | average_price | |------------|---------------| | 1 | 18.00 |

💡 Note: Product 1 sales across three periods: (50×$10) + (30×$20) + (20×$30) = $1800 total revenue, 100 total units → $1800/100 = $18.00 average price.

Visualization

Tap to expand

Understanding the Visualization

Identify Price Periods

Each product has different prices during different date ranges

Match Sales to Prices

For each sale, find which price period it falls into based on purchase date

Calculate Weighted Revenue

Multiply units sold by the corresponding price for each sale

Compute Average

Divide total revenue by total units sold to get weighted average price

Key Takeaway

🎯 Key Insight: The average selling price is a weighted average where the weights are the number of units sold at each price point, not the duration of price periods. This reflects the actual revenue per unit across all sales.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Database JOIN operation with proper indexing on product_id and dates

⚡ Linearithmic

Space Complexity

O(n)

Temporary space for JOIN operations and result set

⚡ Linearithmic Space

Constraints

1 ≤ Prices.product_id, UnitsSold.product_id ≤ 1000
0 ≤ Prices.price ≤ 1000
1 ≤ UnitsSold.units ≤ 1000
No overlapping periods for the same product_id in Prices table
All dates are in format 'YYYY-MM-DD'
Round results to exactly 2 decimal places

Asked in

a Amazon 45 f Meta 32 G Google 28 ⊞ Microsoft 22

The optimal approach uses SQL JOINs to efficiently match sales records with their corresponding price periods using date range conditions. The key insight is calculating a weighted average where weights are the number of units sold at each price point: SUM(units × price) / SUM(units). Handle edge cases with COALESCE and NULLIF to return 0 for products with no sales, and round results to 2 decimal places.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Nested Loops)	O(n²)	O(1)	Check each sale against all price periods to find matches
SQL JOIN (Optimal)	O(n log n)	O(n)	Use SQL JOIN to efficiently match sales with price periods in one operation

Brute Force (Nested Loops) — Algorithm Steps

Get all unique products from both tables
For each product, collect all its sales
For each sale, search through all price periods to find the matching one
Calculate total revenue and total units sold
Compute average price as revenue/units

Visualization

Tap to expand

Step-by-Step Walkthrough

Get Products

Collect all unique product IDs from both tables

For Each Product

Process all sales for the current product

Match Sales

Check each sale against all price periods

Calculate

Compute weighted average from matched data

Code -

solution.c — C

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <math.h>

typedef struct {
    int product_id;
    char start_date[11];
    char end_date[11];
    int price;
} PriceRecord;

typedef struct {
    int product_id;
    char purchase_date[11];
    int units;
} SaleRecord;

typedef struct {
    int product_id;
    double average_price;
} Result;

int isDateInRange(const char* date, const char* start_date, const char* end_date) {
    return strcmp(date, start_date) >= 0 && strcmp(date, end_date) <= 0;
}

Result* calculateAveragePrice(PriceRecord* prices, int priceCount, 
                             SaleRecord* unitsSold, int saleCount, 
                             int* resultSize) {
    // Get unique product IDs (simplified)
    int uniqueProducts[1000];
    int productCount = 0;
    
    // Add products from prices
    for (int i = 0; i < priceCount; i++) {
        int found = 0;
        for (int j = 0; j < productCount; j++) {
            if (uniqueProducts[j] == prices[i].product_id) {
                found = 1;
                break;
            }
        }
        if (!found) {
            uniqueProducts[productCount++] = prices[i].product_id;
        }
    }
    
    // Add products from sales
    for (int i = 0; i < saleCount; i++) {
        int found = 0;
        for (int j = 0; j < productCount; j++) {
            if (uniqueProducts[j] == unitsSold[i].product_id) {
                found = 1;
                break;
            }
        }
        if (!found) {
            uniqueProducts[productCount++] = unitsSold[i].product_id;
        }
    }
    
    Result* result = malloc(productCount * sizeof(Result));
    *resultSize = productCount;
    
    for (int i = 0; i < productCount; i++) {
        int productId = uniqueProducts[i];
        double totalRevenue = 0;
        int totalUnits = 0;
        
        for (int j = 0; j < saleCount; j++) {
            if (unitsSold[j].product_id == productId) {
                // Brute force: check all price periods
                for (int k = 0; k < priceCount; k++) {
                    if (prices[k].product_id == productId &&
                        isDateInRange(unitsSold[j].purchase_date, 
                                    prices[k].start_date, 
                                    prices[k].end_date)) {
                        totalRevenue += unitsSold[j].units * prices[k].price;
                        totalUnits += unitsSold[j].units;
                        break;
                    }
                }
            }
        }
        
        result[i].product_id = productId;
        result[i].average_price = totalUnits > 0 ? round((totalRevenue / totalUnits) * 100.0) / 100.0 : 0;
    }
    
    return result;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

For each sale record, we check against all price periods

⚠ Quadratic Growth

Space Complexity

O(1)

Only using variables for calculations, no additional data structures

✓ Linear Space

Constraints

1 ≤ Prices.product_id, UnitsSold.product_id ≤ 1000
0 ≤ Prices.price ≤ 1000
1 ≤ UnitsSold.units ≤ 1000
No overlapping periods for the same product_id in Prices table
All dates are in format 'YYYY-MM-DD'
Round results to exactly 2 decimal places

52.4K Views

High Frequency

~15 min Avg. Time

1.9K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Brute Force (Nested Loops) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler