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

silver

Mar 07, 2025

Contents

문제 SQLite

문제

solvesql.com

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

SQLite

내가 작성한 오답

: 결과에서 region이 아니라 Region로 출력되어야했다.


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

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

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

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