Leetcode 1070

Product Sales Analysis III

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

TABLE Definition

Solution

# product id에 대한 첫번째 연도 계산
WITH min_year AS (
    SELECT product_id, min(year) AS year
    FROM sales
    group by product_id
)

SELECT s.product_id, m.year AS first_year, s.quantity, s.price
FROM sales s
JOIN min_year m
on s.product_id = m.product_id 
and s.year = m.year

동일한 product_id에 대해 첫 번째 연도(first_year)의 판매 데이터를 정확하게 선택하기 위해서 조건문을 2개 주었다.

Other Approaches

where문에 서브쿼리

select product_id, year as first_year, quantity, price 
from sales 
where (product_id,year) in
(
select product_id ,min(year) as year 
from sales 
group by product_id
)

Leetcode 1045

Customers Who Bought All Products

Write a solution to report the customer ids from the Customer table that bought all the products in the Product table.

TABLE Definition

Solution

# 각 고객이 구매한 서로 다른 제품의 수
WITH buy_log as
(
   SELECT  customer_id
   , COUNT(DISTINCT product_key) AS dis_pcon 
   FROM customer
   GROUP BY customer_id
),

# 전체 제품의 수
total_products AS(
    SELECT COUNT(*) AS product_ccon
    FROM product
)

SELECT customer_id 
FROM buy_log, total_products 
WHERE product_ccon = dis_pcon

각 고객이 구매한 단일 제품의 개수(dis_pcon)가 전체 제품의 개수(product_ccon)와 같은 고객
즉, 모든 제품을 구매한 고객을 찾는 쿼리. DISTINCT를 적절히 사용해 고객이 구매한 제품 중 중복을 제거하고 구매한 품목만 가져오는 것이 핵심.

Other Approaches

having 서브쿼리

select customer_id 
from Customer   
group by customer_id  
having count(distinct product_key)  = (select count(*) from Product);