585. Investments in 2016.md

585. Investments in 2016

Question Link

Intuition

The result of the below code:

select a.pid, b.pid from Insurance a inner join Insurance b on (a.lat,a.lon) = (b.lat,b.lon)
where a.pid<>b.pid

is:

| pid | pid |
| --- | --- |
| 1   | 1   |
| 3   | 2   |
| 2   | 2   |
| 3   | 3   |
| 2   | 3   |
| 4   | 4   |

So when filtering the where a.pid<>b.pid, we get the policyholder who has the unique location.

Code

# Write your MySQL query statement below
select round(sum(tiv_2016), 2) tiv_2016 from Insurance
where pid not in (
  select a.pid from Insurance a inner join Insurance b on (a.lat,a.lon) = (b.lat,b.lon)
  where a.pid<>b.pid
)
and
tiv_2015 in (
  select tiv_2015 from Insurance group by tiv_2015 having count(pid)>1
)