코테 준비 | Friend Requests II: Who Has the Most Friends / Investments in 2016 (union all / 중복값만 or 중복값이 아닌 행만 필터링 하기)

☑️  602. Friend Requests II: Who Has the Most Friends

[문제]

• Write a solution to find the people who have the most friends and the most friends number.
• The test cases are generated so that only one person has the most friends.
• 가장 많은 친구를 가진 사람과 그 수 조회하기

---

[문제 풀이]

내 코드

WITH total AS (
    SELECT requester_id as id
      ,count(distinct accepter_id) as n
    FROM REQUESTACCEPTED
    GROUP BY requester_id
    UNION ALL -- UNION 만 쓰면 연결 될때 중복값 자동 제거 됨. 연결될때 손실없이 연결하려면 UNION ALL
    SELECT accepter_id as id
        ,count(distinct requester_id) as n
    FROM REQUESTACCEPTED
    GROUP BY accepter_id
)
SELECT id
        ,sum(n) as num
FROM total
GROUP BY id
ORDER BY sum(n) DESC
LIMIT 1

 

수정 코드 

WITH AllIds AS (
    SELECT requester_id AS id FROM RequestAccepted
    UNION ALL
    SELECT accepter_id FROM RequestAccepted
  )
SELECT id,
 	 COUNT(*) AS num
FROM AllIds
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1;

 

이슈 및 해결 과정

• 행 병합 시 union vs union all 
  
  • 각 서브쿼리의 컬럼타입/ 컬럼 갯수 가 동일해서 union 으로 연결해줬는데, union 사용시 중복값 자동 제거 됨
  • union all로 중복값 출력하여 해결 
• 수정 코드
  • 나의 경우, 그룹바이 후 union all 해줬는데, 문제풀이를 보니 union 으로 병합 먼저 해주고, 필터링 해줄수 있었음. 
  •  휠씬 짧아 진듯 , 성능은 유사 

☑️  585. Investments in 2016

[문제]

• Write a solution to report the sum of all total investment values in 2016 tiv_2016, for all policyholders who:
  • have the same tiv_2015 value as one or more other policyholders, and
  • are not located in the same city as any other policyholder (i.e., the (lat, lon) attribute pairs must be unique).
  • Round tiv_2016 to two decimal places.
• 2016년 토탈 보험료 조회 하기 
  • 2015년 값이 한명이상의 다른 policyholders와 같고 
  • 같은 지역(lat,lon 페어가 중복이 아닌) 이 아닌 조건

---

[문제 풀이]

내 코드

SELECT ROUND(SUM(tiv_2016),2) AS tiv_2016
FROM (select *
            ,count(pid) over (partition by concat(lat,lon)) as cnts_p
            ,count(tiv_2015) over (partition by tiv_2015) as cnts_t 
       from insurance) AS total
WHERE cnts_p=1
  AND cnts_t>1

 

이슈 및 해결 과정  

• `문제풀이 핵심` : 특정 컬럼의 행이 중복 일때 & 중복이 아닐 때 각각 필터링 하기 
  • (실패) 아이디어1 : `dense_rank` 순번매기기로 접근하기 → tiv_2015이 중복값일때 같은 순번 매겨지지만, 몇 번째에 중복값이 있는지 필터링 해줄 수 없음 → 다른 아이디어로 접근
  • (실패) 아이디어2 : where 절 서브쿼리로 접근 →  where 절 서브쿼리로 tiv_2015 가 중복값일때만 필터링 하고, 아우터 쿼리에서 lat&lon 값이 중복이 아닌것만 필터링 하려고 했는데, 서브쿼리로 이미 필터링 된 후라 lat&lon 중복인게 필터링이 안됨  → 다른 아이디어로 접근
• (해결) count 윈도우 함수 & from 절 서브쿼리로 접근
  • 인라인 서브쿼리에 count 윈도우 함수 집계하여 tiv_2015컬럼의 중복값 , concat(lat,lon) 의 중복값 집계하는 컬럼 추가 
  • 아우터 쿼리에서 집계값이 중복값일때 ( >1), 중복값이 아닐때(>1) 필터링 해줘서 해결 

한줄 포인트

• union 병합시 , 중복 값 필요한 경우 `union all` 사용
• 중복행 or 중복행이 아닌행 필터링 시 count 윈도우 함수로 접근