241213_TIL

아무리 사소하더라도 배움이 없는 날은 없다.

SQL 문제풀이 복습

Leetcode

문제 링크
substring_index와 concat을 이용해 문자열을 제어하는 문제.
문장 중간에 해시태그가 들어간 경우가 있기 때문에
substring_index를 두 번 써 주고 + concat을 써서 #를 붙여줘야 한다.

그 외의 나머지는 어려울 거 없음.

SELECT CONCAT('#', SUBSTRING_INDEX(SUBSTRING_INDEX(tweet, '#', -1), ' ', 1)) AS
       "HASHTAG",
       COUNT(*)                                                              AS
       "HASHTAG_COUNT"
FROM   Tweets
WHERE  YEAR(tweet_date) = '2024'
       AND MONTH(tweet_date) = '02'
GROUP  BY 1
ORDER  BY 2 DESC,
          1 DESC
LIMIT  3;

문제 링크
난이도는 medium인데 왠만한 hard만큼 어려웠던 문제.

주간 평균 포스팅 갯수를 구해야 하기 때문에
그냥 count over도 써야 하고,
7일 연속으로 올린 포스팅 갯수도 구해야 하기 때문에
count over 안에 range between interval n-day preceding and current row도 써야 한다.
써야 하는 함수는 count로 동일했는데 다양한 활용법을 알아야 풀 수 있었던 문제.

덧. post_date의 조건을 year와 month로 썼더니만
귀신같이 2024-02-29를 넣어놔서 오답을 만드네^^

SELECT DISTINCT user_id,
                MAX(max_7day_posts) AS "max_7day_posts",
                avg_weekly_posts
FROM            (
                         SELECT   post_id,
                                  user_id,
                                  post_date,
                                  avg_weekly_posts,
                                  COUNT(post_id) over(partition BY user_id ORDER BY post_date range BETWEEN INTERVAL 6 DAY preceding AND      CURRENT row) AS "max_7day_posts"
                         FROM     (
                                         SELECT post_id,
                                                user_id,
                                                post_date,
                                                COUNT(*) over(partition BY user_id)/4 AS "avg_weekly_posts"
                                         FROM   Posts
                                         WHERE  post_date BETWEEN '2024-02-01' AND    '2024-02-28') a) b
WHERE           max_7day_posts >= 2*avg_weekly_posts
GROUP BY        1
ORDER BY        1;

문제 링크
한 tweet 안에 해시태그가 여러 개 들어가 있을 경우
#이 붙은 것들을 다 해시태그로 인식하고 떼어내야 하기 때문에
recursive cte를 써야 풀 수 있는 문제였다.

거기에 더해 regexp_substr, regexp_replace도 써야 하고
#[^\s]+라는 표현을 써서 해시태그를 식별할 줄도 알아야 하기 때문에
여러모로 정규표현식에 관련된 개념들이 한꺼번에 쏟아졌던 문제.
해시태그가 여러 개 들어가 있는 텍스트를 분석할 때 쓸 수 있을법한 문제였다..!

WITH recursive tags
AS
  (
         SELECT regexp_substr (tweet, "#[^\\s]+")           AS hashtag,
                regexp_replace(tweet, "#[^\\s]+", "", 1, 1) AS tweet
         FROM   Tweets
         UNION ALL
         SELECT regexp_substr (tweet, "#[^\\s]+")           AS hashtag,
                regexp_replace(tweet, "#[^\\s]+", "", 1, 1) AS tweet
         FROM   tags
         WHERE  hashtag IS NOT NULL )
  SELECT   hashtag,
           COUNT(*) AS COUNT
  FROM     tags
  WHERE    hashtag IS NOT NULL
  GROUP BY hashtag
  ORDER BY COUNT DESC,
           hashtag DESC
  LIMIT    3;