118. Investments in 2016

[문제]

https://leetcode.com/problems/investments-in-2016/description/

 

 

[풀이]

1. tiv_2015가 중복인 행들의 pid을 추출해서 dup 리스트 생성

2. lat과 lon이 중복인 행들을 제거

3. 제거된 데이터프레임에서 pid이 dup 리스트에 포함된 행들만 필터링

4. tiv_2016의 합계를 구해서 제출

 

Pandas

import pandas as pd

def find_investments(insurance: pd.DataFrame) -> pd.DataFrame:
    dup = insurance[insurance.duplicated('tiv_2015', keep=False)].pid
    pos = insurance.drop_duplicates(subset=['lat', 'lon'], keep=False)
    
    return pd.DataFrame({
        'tiv_2016': [round(pos[pos.pid.isin(dup)]['tiv_2016'].sum(), 2)]
    })

 

SQL

# Write your MySQL query statement below
SELECT 
    ROUND(SUM(tiv_2016), 2) AS tiv_2016
FROM Insurance
WHERE 
    tiv_2015 IN (SELECT tiv_2015 
                 FROM Insurance 
                 GROUP BY tiv_2015
                 Having COUNT(*) > 1)
AND
    (lat, lon) in (SELECT lat, lon
                    FROM Insurance
                    GROUP BY lat, lon
                    HAVING COUNT(*) < 2)