[SQL문제풀기] 지역별 주문의 특징

문제

solvesql.com

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

SQLite

내가 작성한 오답

with a as (select region, category, count(distinct order_id) count
          from records
          group by region, category),
     b as (select region, 
                  case category when 'Furniture' then count else 0 end "Furniture",
                  case category when 'Office Supplies' then count else 0 end "Office Supplies",
                  case category when 'Technology' then count else 0 end "Technology"
          from a)
select region, 
        sum(Furniture) Furniture, 
        sum("Office Supplies") "Office Supplies",
        sum(Technology) Technology
from b
group by region
order by 1 asc

내가 작성한 정답1

with a as (select region, category, count(distinct order_id) count
          from records
          group by region, category),
     b as (select region, 
                  case category when 'Furniture' then count else 0 end "Furniture",
                  case category when 'Office Supplies' then count else 0 end "Office Supplies",
                  case category when 'Technology' then count else 0 end "Technology"
          from a)
select region "Region", 
        sum(Furniture) Furniture, 
        sum("Office Supplies") "Office Supplies",
        sum(Technology) Technology
from b
group by region
order by 1 asc

내가 작성한 정답2

select region "Region",
       sum(case category when 'Furniture' then count end) "Furniture",
       sum(case category when 'Office Supplies' then count end) "Office Supplies",
       sum(case category when 'Technology' then count end) "Technology"
from (select region, category, count(distinct order_id) count
      from records
      group by region, category)
group by 1
order by 1