1070. Product Sales Analysis III


Posted by ikl258794613 on 2024-02-26

Table: Sales

Column Name Type
sale_id int
product_id int
year int
quantity int
price int

(sale_id, year) is the primary key (combination of columns with unique values) of this table.
product_id is a foreign key (reference column) to Product table.
Each row of this table shows a sale on the product product_id in a certain year.
Note that the price is per unit.

Table: Product

Column Name Type
product_id int
product_name varchar

product_id is the primary key (column with unique values) of this table.
Each row of this table indicates the product name of each product.

Write a solution to select the product id, year, quantity, and price for the first year of every product sold.

Return the resulting table in any order.

The result format is in the following example.

Example 1:

Input:
Sales table:

sale_id product_id year quantity price
1 100 2008 10 5000
2 100 2009 12 5000
7 200 2011 15 9000

Product table:

product_id product_name
100 Nokia
200 Apple
300 Samsung

Output:

product_id first_year quantity price
100 2008 10 5000
200 2011 15 9000

這題描述不好,單純要達到範例要求只需要。

SELECT product_id , MIN(year) AS first_year ,quantity , price 
FROM Sales 
GROUP BY product_id

而且多給Product table也會造成誤導。

但要通過submit的測試要用子查詢


SELECT product_id, year AS first_year, quantity, price
FROM Sales
WHERE (product_id, year) in (
    SELECT product_id, MIN(year) 
    FROM Sales
    GROUP BY product_id
)

#SQL







Related Posts

資訊安全:湊雜與加密

資訊安全:湊雜與加密

MTR04_0628

MTR04_0628

[ 筆記 ] Fetch 與 Promise

[ 筆記 ] Fetch 與 Promise


Comments