[solveSql] 전국 카페 주소 데이터 정제하기

*solveSql은 문제를 게시하는 걸 금하기 때문에 답만 첨부

https://solvesql.com/problems/refine-cafe-address/

문제유형 : String/Date
난이도3

 

WITH parsed_cafes AS (
  SELECT
    SUBSTR(address, 1, INSTR(address, ' ') - 1) AS sido,
    SUBSTR(
      address,
      INSTR(address, ' ') + 1,
      INSTR(SUBSTR(address, INSTR(address, ' ') + 1), ' ') - 1
    ) AS sigungu
  FROM
    cafes
)
SELECT
  sido,
  sigungu,
  COUNT(*) AS cnt
FROM
  parsed_cafes
WHERE sido IS NOT NULL AND sigungu IS NOT NULL AND sigungu != ''
GROUP BY
  sido,
  sigungu
ORDER BY
  cnt DESC,
  sido,
  sigungu;