[1084] Sales Analysis III

Sales Analysis III - LeetCode

 

Table: Product

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
| unit_price   | int     |
+--------------+---------+
product_id is the primary key of this table.
Each row of this table indicates the name and the price of each product.

Table: Sales

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| seller_id   | int     |
| product_id  | int     |
| buyer_id    | int     |
| sale_date   | date    |
| quantity    | int     |
| price       | int     |
+-------------+---------+
This table has no primary key, it can have repeated rows.
product_id is a foreign key to the Product table.
Each row of this table contains some information about one sale.

 

Write an SQL query that reports the products that were only sold in the first quarter of 2019. That is, between 2019-01-01 and 2019-03-31 inclusive.

=> 2019년 1분기에만 판매된 제품을 보고하는 SQL 쿼리를 작성합니다.

Return the result table in any order.

The query result format is in the following example.

 

Example 1:

Input: 
Product table:
+------------+--------------+------------+
| product_id | product_name | unit_price |
+------------+--------------+------------+
| 1          | S8           | 1000       |
| 2          | G4           | 800        |
| 3          | iPhone       | 1400       |
+------------+--------------+------------+
Sales table:
+-----------+------------+----------+------------+----------+-------+
| seller_id | product_id | buyer_id | sale_date  | quantity | price |
+-----------+------------+----------+------------+----------+-------+
| 1         | 1          | 1        | 2019-01-21 | 2        | 2000  |
| 1         | 2          | 2        | 2019-02-17 | 1        | 800   |
| 2         | 2          | 3        | 2019-06-02 | 1        | 800   |
| 3         | 3          | 4        | 2019-05-13 | 2        | 2800  |
+-----------+------------+----------+------------+----------+-------+
Output: 
+-------------+--------------+
| product_id  | product_name |
+-------------+--------------+
| 1           | S8           |
+-------------+--------------+
Explanation: 
The product with id 1 was only sold in the spring of 2019.
The product with id 2 was sold in the spring of 2019 but was also sold after the spring of 2019.
The product with id 3 was sold after spring 2019.
We return only product 1 as it is the product that was only sold in the spring of 2019.

 

[문제 풀이 과정] 

 

해당 문제 또한 1083과 동일하게 product와 sales 테이블을 가지고 있습니다.

2019년 1분기(~4월)에만 판매된 제품을 보고하는 쿼리를 작성하는 것이 문제의 조건입니다.

 

판매 날짜 데이터에 조건을 걸어줘야 하기 때문에, sales 와 product 테이블을 left join하여 붙여 주었습니다.

그 다음, product_id와 product_name별로 그루핑을 진행 하였고 

1분기는 4월 전 까지를 뜻하니 having절을 이용해서 2019-01-01 ~ 2019-03-31 데이터로 필터링을 진행합니다. 

select p.product_id, p.product_name
from sales s 
        left join product p on s.product_id = p.product_id
group by 1,2
having min(s.sale_date) >= '2019-01-01'
and max(s.sale_date) <= '2019-03-31'

 

MySQL에는 QUARTER라는 분기를 나타내는 함수가 추가로 존재합니다. 

sub-query를 이용해서 product_id가 2019년 1분기에 들어가있지 않은 데이터가 아니라면 출력하라는 조건을 걸었습니다.

 

 

select product_id, 
       product_name
from Product 
where product_id not in (select  product_id
                         from Sales
                         where not (year(sale_date) = 2019 
                                    and quarter(sale_date)=1))

해당 문제에서 이 쿼리가 돌아가지 않는 이유는 무엇일까요?

select product_id, product_name
from product
where product_id not in (select product_id
                        from sales
                         where not (year(sale_date) = 2019
                        and quarter(sale_date)in (2,3,4)))