[SQL테스트]지역별 주문의 특징

문제

https://solvesql.com/problems/characteristics-of-orders/

풀이

1번째 풀이

select region as Region,
count(DISTINCT(case when category = 'Furniture' then order_id end )) as 'Furniture',
count(DISTINCT(case when category = 'Office Supplies'  then order_id end)) as 'Office Supplies',
count(DISTINCT(case when category = 'Technology'  then order_id end)) as 'Technology'
from records
group by 1
order by 1 asc

2번째 풀이

WITH cte AS(
  SELECT region, category, COUNT(DISTINCT order_id) as dis_order_id
  FROM records
  GROUP BY 1,2
)

SELECT
  region AS Region,
  SUM(CASE WHEN category = 'Furniture' THEN dis_order_id ELSE 0 END) AS Furniture,
  SUM(CASE WHEN category = 'Office Supplies' THEN dis_order_id ELSE 0 END) AS "Office Supplies",
  SUM(CASE WHEN category = 'Technology' THEN dis_order_id ELSE 0 END) AS Technology
FROM cte
GROUP BY region
ORDER BY region;

후기

id별 유니크한 구매내역을 정답으로 원하는 듯 하다.
하지만 문제에는 그런 내용이 별도로 없었어서 좀 아쉬웠다.