8/27 每日一題(MYSQL 計算商品的平均價格)

 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 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     |
+---------------+---------+
There is no primary key for this table, it may contain duplicates.
Each row of this table indicates the date, units, and product_id of each product sold. 

 

Write an SQL query to find the average selling price for each product. average_price should be rounded to 2 decimal places.

Return the result table in any order.

The query result format is in the following example.

# Write your MySQL query statement below

SELECT P.product_id, ROUND(SUM((P.price * U.units ))/SUM(U.units),2) AS average_price

FROM Prices AS P

join UnitsSold AS U

on P.product_id =U.product_id

WHERE U.purchase_date Between P.start_date and P.end_date  

GROUP BY P.product_id

-----------------------------

# Write your MySQL query statement below


SELECT P.product_id, ROUND(SUM(u.units*p.price)/SUM(u.units),2) AS average_price
FROM Prices P
INNER JOIN UnitsSold U ON P.product_id = U.product_id
 where U.purchase_date >= P.start_date and U.purchase_date <= P.end_date
group by 1;