[StrataScratch - SQL] (Medium) Number of Streets Per Zip Code

문제

각 우편번호별로 고유한 거리 이름의 수를 계산하시오. 거리 이름의 첫 번째 단어만 사용하며 대소문자는 구분하지 않는다(예: "FOLSOM"과 "Folsom"은 동일하게 간주). 구조가 반대인 경우(예: "Pier 39"과 "39 Pier")에도 동일한 거리로 간주한다. 결과는 우편번호와 함께 출력하며, 거리 수를 기준으로 내림차순 정렬하고, 거리 수가 같을 경우 우편번호를 오름차순으로 정렬한다.

 

테이블설명

 

예상결과

 

✏️ 풀이

SELECT business_postal_code AS postal_code,
    COUNT(DISTINCT LOWER(CASE
          WHEN REGEXP_LIKE(business_address, "^[0-9]") = 1 THEN substring_index(substring_index(business_address, ' ', 2), ' ', -1)
          ELSE substring_index(business_address, ' ', 1)
      END)) AS street_name
FROM sf_restaurant_health_violations
GROUP BY 1
HAVING postal_code IS NOT NULL
ORDER BY 2 DESC, 1 ASC;

1. 숫자가 있을 경우 그 다음 첫번째 단어 추출

2. 숫자가 없을 경우 바로 첫번째 단어 추출

3. postal_code는 is not null 필터

4. postal_code로 group by 

 

📚 개념정리

SUBSTRING_INDEX() ? 문자열을 구분자(delimiter)를 기준으로 잘라서, 특정 부분을 추출할 때 사용하는 함수

SUBSTRING_INDEX(str, delim, count)

- str : 입력 문자열 (예: '123 Main Street')

- delim : 구분자 (보통 공백 ' ', 쉼표 ',', 등)

- count : 정수 값. 몇 번째까지 추출할지 지정 (양수 → 왼쪽부터 추출, 음수 → 오른쪽부터 추출)