solvesql 정답 공유) 폐쇄할 따릉이 정류소 찾기 1

https://solvesql.com/problems/find-unnecessary-station-1/

solvesql

난이도 표시 : 어려움 (solvesql 기준)

체감 : 보통 (수학 function을 찾아보는 게 번거로웠지만, 차례대로 넣기만 하면 됨)

 

 

코드 

WITH
  TBL AS (
    select
      s.station_id,
      s.name,
      COUNT(s2.station_id) as count
    FROM
      station s
      LEFT JOIN station s2 ON s2.station_id != s.station_id
      AND s2.updated_at > s.updated_at
    WHERE
      2 * 6356 * asin(
        sqrt(
          cos(radians(s.lat)) * cos(radians(s2.lat)) * sin((radians(s.lng) - radians(s2.lng)) / 2) * sin((radians(s.lng) - radians(s2.lng)) / 2) + sin((radians(s.lat) - radians(s2.lat)) / 2) * sin((radians(s.lat) - radians(s2.lat)) / 2)
        )
      ) < 0.3
    GROUP BY
      1
    HAVING
      count >= 5
  )

SELECT
  station_id,
  name
FROM
  TBL