Better than 100% of the solutions
Intuition\nThe given SQL query is designed to calculate the average price of products over a specific period, defined by start_date and end_date. The query uses
# Intuition\nThe given SQL query is designed to calculate the average price of products over a specific period, defined by start_date and end_date. The query uses a Common Table Expression (CTE) to first create a temporary result set that joins the Prices table with the UnitsSold table based on the product_id. The join condition also includes a filter to consider only those sales that occurred within the given date range. The main query then calculates the weighted average price for each product by summing the product of price and units and dividing it by the sum of units. If there are no units sold for a product, the COALESCE function ensures that the average price defaults to 0 instead of NULL.\n\n# Approach\nDefine a CTE to create a combined dataset of prices and units sold within the specified date range.\nPerform a LEFT JOIN between the Prices and UnitsSold tables on product_id to ensure all products are included, even if there are no corresponding sales records.\nFilter the sales records to include only those within the specified date range using the purchase_date column.\nIn the main query, group the results by product_id.\nCalculate the weighted average price for each product by dividing the sum of the product of price and units by the sum of units.\nUse the COALESCE function to handle cases where there are no units sold, defaulting the average price to 0.\nUse the ROUND function to format the average price to two decimal places.\nUse the CAST function to ensure that the division is performed as a floating-point operation to maintain decimal precision.\nSelect the product_id and the calculated average_price from the CTE result set.\n\n# Complexity\nThe complexity of this query is primarily determined by the size of the Prices and UnitsSold tables and the efficiency of the join operation. Assuming that both tables are indexed on product_id, the join operation should be relatively efficient. However, the complexity can be considered as O(N) where N is the number of rows that satisfy the join condition and date range filter. The aggregation functions (SUM and GROUP BY) also add to the complexity, but modern SQL servers are optimized to handle such operations efficiently.\n\n# Code\n```\n/* Write your T-SQL query statement below */\nWITH CTE AS(\n    SELECT Prices.product_id, price, units\n    FROM Prices LEFT JOIN UnitsSold\n    ON Prices.product_id = UnitsSold.product_id\n    AND purchase_date >= start_date and purchase_date <= end_date\n)\nSELECT product_id, COALESCE(ROUND(CAST(SUM(price * units) AS FLOAT) / SUM(units),2),0) AS average_price\nFROM CTE\nGROUP BY product_id;\n```
['MS SQL Server']