SQL Query to Calculate Total Investments in 2016 Based on Conditions - LeetCode 585 Solution
Introduction:
In this problem, we aim to calculate the total investment value in 2016 for policyholders who meet two conditions: they have the same total investment value in 2015 as other policyholders, and they live in a unique location (latitude and longitude pair). The result should be rounded to two decimal places.
Problem Description:
We are given a table called Insurance, which contains policyholder details including their policy ID, total investment values for 2015 and 2016, and their city's latitude and longitude. The task is to compute the sum of all total investment values for 2016 (tiv_2016) for policyholders who:
Have the same tiv_2015 value as one or more other policyholders.
Are not located in the same city as any other policyholder (i.e., their latitude and longitude pair is unique).
The result should be rounded to two decimal places.
Table Structure:
Table: Insurance
pid (int): Policy ID (Primary Key)
tiv_2015 (float): Total investment value in 2015.
tiv_2016 (float): Total investment value in 2016.
lat (float): Latitude of the policyholder's city.
lon (float): Longitude of the policyholder's city.
+-------------+-------+
| Column Name | Type |
+-------------+-------+
| pid | int |
| tiv_2015 | float |
| tiv_2016 | float |
| lat | float |
| lon | float |
+-------------+-------+
Step-by-Step Solution:
Identify policyholders with the same tiv_2015:
Group the data by tiv_2015 and filter for those that appear more than once.
Find unique city locations:
Group by the latitude (lat) and longitude (lon) to identify policyholders located in unique cities.
Filter based on both conditions:
Combine the two conditions to find the relevant policyholders.
Sum the total investment values for 2016:
Sum the tiv_2016 values of the filtered policyholders and round the result to two decimal places.
Writing the SQL Query:
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(*) greater than 1
)
AND (lat, lon) IN (
SELECT lat, lon
FROM Insurance
GROUP BY lat, lon
HAVING COUNT(*) = 1
);
Explanation of SQL Query:
The subquery for tiv_2015 groups records by tiv_2015 and ensures only values appearing more than once are included.
The subquery for lat and lon groups records by these columns to find unique locations.
The main query filters records based on both conditions and sums their tiv_2016 values, rounding the result to two decimal places.
What You Will Learn:
How to use GROUP BY and HAVING to filter records based on conditions.
Using subqueries to meet multiple criteria in SQL.
How to perform aggregation (SUM) and rounding (ROUND) in SQL queries.
#SQL #LeetCode #DataScience #InsuranceData #SQLQueries #GroupBy #DataAnalysis #PostgreSQL #TechTutorial #LearnSQL #DataEngineering