SQL 코테 준비(3월 2주차)(해커랭크)

3.6

1) Print Prime Numbers

WITH RECURSIVE numbers AS (
  SELECT 1 AS num
  UNION ALL
  SELECT num+1 FROM numbers WHERE num<1000
)
SELECT GROUP_CONCAT(num SEPARATOR '&')
FROM numbers as n
WHERE (SELECT SUM(MOD(n.num,num)=0) FROM numbers) = 2

• 데이터셋이 없는데 어떻게 데이터를 불러와서 쿼리 작성을 하지?
  => with recursive 문 사용

  • with recursive 문 기본 문법

    WITH RECURSIVE cte_name AS (
      -- 기저 조건 (Base Case)
      SELECT 초기값
    
      UNION ALL
    
      -- 재귀 조건 (Recursive Case)
      SELECT 재귀적으로 증가할 값 FROM cte_name WHERE 종료 조건
    )
    SELECT * FROM cte_name;

• 소수를 어떻게 구할까?
  => 소수는 자기자신과 1만을 약수로 가지는 수, 나눴을 때 나머지가 0인 값이 2개인 수
  

3.7

1) The Challenges

WITH data AS (
    SELECT h.hacker_id, h.name, COUNT(c.challenge_id) AS cnt
    FROM hackers h
    JOIN challenges c ON h.hacker_id = c.hacker_id
    GROUP BY h.hacker_id, h.name
),
counts AS (
    SELECT cnt, COUNT(*) as cnt_count
    FROM data
    GROUP BY cnt
)
SELECT d.hacker_id, d.name, d.cnt
FROM data d
JOIN counts c ON d.cnt = c.cnt
WHERE d.cnt = (SELECT MAX(cnt) FROM data) 
   OR c.cnt_count = 1 
ORDER BY d.cnt DESC, d.hacker_id ASC;

• '챌린지가 중복되었을 때, 최대치가 아니라면 1개의 경우만 최대치의 경우라면 모든 값 살려놓기' 조건 미적용

3.8

1) Draw The Triangle 1

WITH RECURSIVE my_cte AS
(
  SELECT 1 AS n
  UNION ALL
  SELECT 1+n FROM my_cte WHERE n<20 
)
SELECT repeat('* ', 21-n) FROM my_cte;

2) Draw The Triangle 2

WITH RECURSIVE my_cte AS
(
  SELECT 1 AS n
  UNION ALL
  SELECT 1+n FROM my_cte WHERE n<20 
)
SELECT repeat('* ', n) FROM my_cte;

3) Japanese Cities' Attributes

SELECT *
FROM CITY
WHERE COUNTRYCODE = 'JPN'

3.9

1) Binary Tree Node

SELECT DISTINCT NOW.N,
	CASE 
		WHEN NOW.P IS NULL THEN 'Root'
		WHEN CHILD.P IS NULL THEN 'Leaf'
		ELSE 'Inner'
	END
FROM BST NOW
	LEFT JOIN BST CHILD ON NOW.N = CHILD.P
	LEFT JOIN BST PARENT ON NOW.P = PARENT.N
ORDER BY NOW.N

3.10

1) The pad

SELECT CONCAT(NAME, '(', LEFT(OCCUPATION, 1), ')')
FROM OCCUPATIONS
ORDER BY NAME;

SELECT CONCAT('There are a total of ', COUNT(OCCUPATION), ' ', LOWER(OCCUPATION), 's.')
FROM OCCUPATIONS
GROUP BY OCCUPATION
ORDER BY COUNT(OCCUPATION), OCCUPATION;

3.11

1) Symmetric Pairs

# x! = y
SELECT f1.X, f1.Y
FROM Functions f1
    INNER JOIN Functions f2
        ON f1.X = f2.Y AND f1.Y = f2.X
WHERE f1.X < f1.Y 


UNION

# x = y
SELECT X,Y
FROM Functions
WHERE X=Y
GROUP BY X,Y
HAVING count(*) > 1
)

ORDER BY X

• 한 번에 모든 조건을 적용시키보다는 케이스를 나눠서 union하는 방식도 하나의 방법.