[TIL] 25.03.10 Postgre SQL과 프로젝트 시작 땡땡땡

Prologue

오늘한 것: Postgre SQL Easy 4, Medium 1문제, 프로젝트 기획서 제출 및 정류장 간의 거리 측정

Postgre SQL 코드 테스트

---

1. Average Post Hiatus, Facebook

목표: 2021년에 가장 마지막 게시일과 가장 처음 게시일 간의 날짜 차이를 구하기

SELECT
  user_id
  ,EXTRACT('DAY' FROM MAX(post_date) - MIN(post_date)) as days_between
FROM posts
WHERE EXTRACT('YEAR' FROM post_date) = '2021' 
GROUP BY user_id
HAVING count(user_id) > 1

• user 중 2021년에 포스팅이 2개 이상 없다면 날짜의 차이가 0이 나옴
  : Having 절로 포스팅이 2개 이상인 user만 구해주기

2. Teams Power Users, MS

목표: 2022년 8월에 가장 많이 메시지한 2명의 파워 유저와 메시지 수 구하기

-- 2022년 8월에 가장 많이 메시지한 2명의 파워 유저, message_cnt

SELECT
  sender_id
  ,COUNT(message_id) as message_count
FROM messages
WHERE EXTRACT(YEAR FROM sent_date) = '2022'
  AND EXTRACT(MONTH FROM sent_date) = '8'
GROUP BY sender_id
ORDER BY 2 DESC
LIMIT 2;

• 2022년 8월 조건을 설정하기 위해 EXTRACT를 2번 써야 함
• 상위 2명을 뽑기 위해 LIMIT을 사용

3. Duplicate Job Listings, Linked In

목표: 제목과 설명이 같은 job list를 갖고 있는 회사의 수를 세기

-- 제목과 설명이 같은 job list를 갖고 있는 회사의 수를 세기
SELECT
  COUNT(DISTINCT j.company_id) AS duplicate_companies
FROM job_listings j
INNER JOIN job_listings j2
ON j.company_id = j2.company_id AND j.title = j2.title AND j.description = j2.description AND j.job_id <> j2.job_id

• Inner Join을 사용해서 회사명, 제목, 설명이 같은데 직업(리스트의 구별자)이 다른 행을 구함
• 회사의 수를 세는 것이기 때문에 회사명의 중복을 없앤 회사의 수를 구함

4. Cities With Completed Trades, Robinhood

Robinhood: 수수료 무료 주식 거래 플랫폼
목표: 거래가 완료된 수를 도시별로 구하고 Top 3 구하기

-- 3개 도시, 총 오더 수(거래 완료된)
-- 내림차순
SELECT
  city
  ,COUNT(*) total_orders
FROM trades t
INNER JOIN users u
ON t.user_id = u.user_id
WHERE status = 'Completed'
GROUP BY city
ORDER BY total_orders DESC
LIMIT 3;

• FROM trades 하기 전에 서브쿼리로 먼저 Trades 테이블에서 필요한 정보를 선택하면 좋을 듯..?

  FROM (
  SELECT order_id, user_id, status
  FROM trades
  WHERE status = 'Completed') t

• 이 구문을 넣고 실행해도 결과는 같다. 먼저 필요한 정보를 추출해서 사용했으므로 메모리 효율과 연산 속도를 기대할 수 있을 것 같음

  SELECT
  city
  ,COUNT(*) total_orders
  FROM (
  SELECT order_id, user_id, status
  FROM trades
  WHERE status = 'Completed') t
  INNER JOIN users u
  ON t.user_id = u.user_id
  GROUP BY city
  ORDER BY total_orders DESC
  LIMIT 3;

5. Top 5 Artists, Spotify

목표: 상위 5명의 가수 이름을 그들의 랭킹 오름차순으로 표현하기

WITH ranking AS(
SELECT 
  artist_name
  ,DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS artist_rank
FROM global_song_rank g
INNER JOIN songs s
ON g.song_id = s.song_id
INNER JOIN artists a
ON s.artist_id = a.artist_id
WHERE rank <= 10
GROUP BY artist_name)
SELECT *
FROM ranking
WHERE artist_rank <= 5

• Global_Song_rank부터 시작: 이 테이블에서 10등안에 든 것이 중요, 곡 이름과 가수 이름은 붙여지면 되는 정보일 뿐임
• 가수 이름으로 그룹화하면 DENSE_RANK() 에서도 가수 이름으로 그룹화한 집계함수를 사용 가능함(SELECT 절의 일반적인 컬럼에 집계 함수를 사용하는 것과 같게 생각하면 됨)
• WITH 절(CTE 구문: 공통 테이블 표현식, WITH와 함께 여러 개의 서브쿼리를 정의)을 이용. WITH절이든 일반 서브쿼리든 FROM 절의 서브쿼리(인라인 뷰)에는 항상 Alias를 써주기

프로젝트 기획서 제출

버스, 정류장, 버스 이동 3개의 엔터티로 나눠서 데이터를 정리
Git Project를 적극적으로 활용해서 5분 대시보드와 일정을 만들기
-> 나 혼자..! 나를 적극적으로 관리하기!!
자문을 적극적으로 구하기!!: 구글폼, 정류장 간의 거리 측정에서 자문을 구했을 때 훨씬 좋은 결과가 있었음. 매니저님께서 구글폼 피드백을 넘 잘해주셨고 같은 캠프 내 동료 분이 TMAP API를 소개해주셔서 정류장 간의 차량 이동 거리를 측정할 수 있을 듯!!