O desafio pedia que fosse SOMADO o investimento de 2016 APENAS das apólices que em 2015 possuem mais de 1 cliente e que não compartilham a mesma região.

example 1:

Input:
Insurance table:
+-----+----------+----------+-----+-----+
| pid | tiv_2015 | tiv_2016 | lat | lon |
+-----+----------+----------+-----+-----+
| 1   | 10       | 5        | 10  | 10  |
| 2   | 20       | 20       | 20  | 20  |
| 3   | 10       | 30       | 20  | 20  |
| 4   | 10       | 40       | 40  | 40  |
+-----+----------+----------+-----+-----+
Output:
+----------+
| tiv_2016 |
+----------+
| 45.00    |
+----------+

A solução implementada foi a seguinte:

-- Write your PostgreSQL query statement below
SELECT TRUNC(SUM(tiv_2016)::numeric, 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(*) = 1
);

Esse desafio foi um pouco complexo no início, pois achei que conseguiria resolver tudo em apenas 1 query. Fiquei tentando encontrar o jeito “mais fácil” e acabei gastando tempo à toa com essa ideia. O que aprendi é que a solução pode ser simplificada utilizando subqueries em conjunto com condições no WHERE e AND, deixando o raciocínio bem mais claro.

A consulta funciona como uma filtragem em duas etapas, cada uma resolvida por uma subquery:

1. Filtrar pelos tiv_2015 duplicados
   • O primeiro IN (...) busca apenas os valores de tiv_2015 que aparecem mais de uma vez na tabela.
   • Isso garante que só consideramos apólices cujo investimento em 2015 também foi feito por outros clientes.
2. Filtrar pelas localizações únicas
   • O segundo IN (...) seleciona os pares (lat, lon) que aparecem apenas uma vez.
   • Isso elimina regiões com duplicidade, garantindo que a apólice esteja em uma localização exclusiva.
3. Somar o tiv_2016 dos registros resultantes
   • Após a filtragem, o SUM(tiv_2016) agrega os valores de 2016 que atendem às duas condições.
   • A função TRUNC(..., 2) é usada para truncar o resultado final em duas casas decimais, conforme o enunciado.

Assim, cada subquery é responsável por aplicar uma regra do problema, e a query principal combina essas condições de forma clara e direta.