Product Sales Analysis III Problem


Description

LeetCode Problem 1070.

Table: Sales

1
2
3
4
5
6
7
8
9
10
11
12
+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| sale_id     | int   |
| product_id  | int   |
| year        | int   |
| quantity    | int   |
| price       | int   |
+-------------+-------+
sale_id is the primary key of this table.
product_id is a foreign key to Product table.
Note that the price is per unit.

Table: Product

1
2
3
4
5
6
7
+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
+--------------+---------+
product_id is the primary key of this table.

Write an SQL query that selects the product id, year, quantity, and price for the first year of every product sold.

The query result format is in the following example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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      |
+------------+--------------+

Result table:
+------------+------------+----------+-------+
| product_id | first_year | quantity | price |
+------------+------------+----------+-------+ 
| 100        | 2008       | 10       | 5000  |
| 200        | 2011       | 15       | 9000  |
+------------+------------+----------+-------+


MySQL Solution

1
2
3
4
5
6
7
8
9
10
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 1)




Related Posts

Product Sales Analysis III Problem

LeetCode 1070. Write an SQL query that selects the product...

Project Employees III Problem

LeetCode 1077. Write an SQL query that reports the most...

Project Employees II Problem

LeetCode 1076. Write an SQL query that reports all the...

Project Employees I Problem

LeetCode 1075. Write an SQL query that reports the average...

Product Sales Analysis II Problem

LeetCode 1069. Write an SQL query that reports the total...

Product Sales Analysis I Problem

LeetCode 1068. Write an SQL query that reports all product...