Average Post Hiatus (Part 1)

[문제]

https://datalemur.com/questions/sql-average-post-hiatus-1

Average Post Hiatus | Facebook SQL Interview Question

 

[풀이]

PostgreSQL 에서 날짜 차이를 구하려면 AGE() 함수 또는 날자 뺄셈(-) 연산자를 사용하면 된다.

1. 21년에 작성한 user_id만 필터링해서

2. user_id 별로 최근 작성날짜와 첫 작성날짜를 빼준다

 

[코드]

SELECT 
  user_id
  , MAX(post_date)::DATE - MIN(post_date)::DATE AS days_between
FROM posts
WHERE user_id in (SELECT user_id
                  FROM posts
                  WHERE EXTRACT(YEAR FROM post_date) = 2021
                  GROUP BY user_id
                  HAVING COUNT(post_id) >= 2)
GROUP BY user_id