SQLite/solvesql 지역별 주문의 특징

Posted on | In

[SQLite/solvesql] 지역별 주문의 특징

📌문제링크

SQLite의 경우, pivot 함수가 없다.
Case When Then End 구절으로 데이터를 조작해야한다.

컬럼명 오타없이 입력하자.

solution

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
select
  region as Region,
  max(Furniture) as 'Furniture',
  max(Office) as 'Office Supplies',
  max(Technology) as 'Technology'
from
  (
    select
      Result.region,
      case
        when category = 'Furniture' then sum(cnt)
        else 0
      end as Furniture,
      case
        when category = 'Office Supplies' then sum(cnt)
        else 0
      end as Office,
      case
        when category = 'Technology' then sum(cnt)
        else 0
      end as Technology
    from
      (
        select
          region,
          category,
          count(distinct order_id) as cnt
        from
          records
        group by
          region,
          category
      ) Result
    group by
      Result.region,
      category
  )
group by
  region