Skip to main content

LeetCode 585. Investments in 2016 SQL Solution

Problem​

LeetCode SQL Problem

  1. Investments in 2016

insurance table

PIDTIV_2015TIV_2016LATLON
11051010
220202020
310302020
410404040

Solution - 2 CTEs + Left Join​

Investments in 2016
-- Find policy holders who have the same TIV_2015 value as one or more other policyholders.
WITH TIV2015Check
AS (
	SELECT DISTINCT L.PID
		,L.TIV_2016
	FROM insurance AS L
	INNER JOIN insurance AS R ON L.PID <> R.PID
		AND L.TIV_2015 = R.TIV_2015
	)
	,SameCityCheck
-- Find policy holders who are located in the same city as any other policyholder
AS (
	SELECT DISTINCT L.PID
	FROM insurance AS L
	INNER JOIN insurance AS R ON L.PID <> R.PID
		AND L.LAT = R.LAT
		AND L.LON = R.LON
	)
SELECT SUM(TIV.TIV_2016) AS TIV_2016
FROM TIV2015Check AS TIV
-- Use LEFT JOIN to include policy holders who are NOT located in the same city as any other policyholder
LEFT JOIN SameCityCheck AS SC ON TIV.PID = SC.PID
WHERE SC.PID IS NULL

Query Output

TIV_2016
45

Solution - Subqueries in WHERE Clause​

Investments in 2016
SELECT sum(insurance.TIV_2016) AS TIV_2016
FROM insurance
-- Have the same TIV_2015 value as one or more other policyholders.
WHERE insurance.TIV_2015 IN (
		SELECT TIV_2015
		FROM insurance
		GROUP BY TIV_2015
		HAVING count(*) > 1
		)
    -- Are not located in the same city as any other policyholder
    -- (i.e.: the (latitude, longitude) attribute pairs must be unique).
	AND CONCAT (
		LAT
		,LON
		) IN (
		SELECT CONCAT (
				LAT
				,LON
				)
		FROM insurance
		GROUP BY LAT
			,LON
		HAVING count(*) = 1
		)

Query Output

TIV_2016
45