The problem requires calculating the average selling price for each product, where the average selling price equals the total revenue divided by the quantity sold.

Solution approach:

We need to compute the total revenue and total quantity for each product. The total quantity can be obtained using the SUM function on the UnitsSold table, which can be achieved through GROUP BY and SUM.

SELECT product_id, SUM(units) FROM UnitsSold GROUP BY product_id

Since prices vary across different time periods for the same product, we must first calculate the revenue for each price period. The revenue for a given period is computed as price multiplied by the quantity sold during that period. As price and date information reside in the Prices table and quantity data is in the UnitsSold table, we can join these tables using product_id. A LEFT JOIN connects both tables, and a WHERE clause filters records based on purchase dates falling with in valid price periods. This allows us to determine the price and quantity for each transaction.

SELECT
    Prices.product_id AS product_id,
    Prices.price * UnitsSold.units AS sales,
    UnitsSold.units AS units
FROM Prices
LEFT JOIN UnitsSold ON Prices.product_id = UnitsSold.product_id
AND (UnitsSold.purchase_date BETWEEN Prices.start_date AND Prices.end_date)

After computing the revenue per price period, we apply the SUM function again to get the overall revenue for each product. Dividing this by the total quantity and rounding to two decimal places gives the desired result. Here's the complete solution:

SELECT
    product_id,
    IFNULL(Round(SUM(sales) / SUM(units), 2), 0) AS average_price
FROM (
    SELECT
        Prices.product_id AS product_id,
        Prices.price * UnitsSold.units AS sales,
        UnitsSold.units AS units
    FROM Prices
    LEFT JOIN UnitsSold ON Prices.product_id = UnitsSold.product_id
        AND (UnitsSold.purchase_date BETWEEN Prices.start_date AND Prices.end_date)
) T
GROUP BY product_id;

Summary:

1. The IFNULL function handles cases where certain products have no sales records, ensuring calculations do not fail due to NULL values.
2. The Round function rounds results to two decimal places.
3. Usage of BETWEEN operator checks if a value lies inclusively between two specified values.