Скачать или смотреть SQL Interview Prep: Filtering with Subqueries & Aggregation (LeetCode 585)

Intro: In this video, we tackle LeetCode 585: Investments in 2016, a Medium-level SQL problem often asked in data science interviews. We need to calculate the sum of investments for 2016 based on two specific criteria involving unique locations and shared investment history.

What you'll learn in this video:

How to handle complex filtering criteria.

Using GROUP BY and HAVING to find duplicates and unique values.

Understanding Tuple syntax (lat, lon) for filtering multiple columns simultaneously.

How to structure Subqueries for cleaner logic.

The Logic:

Criterion 1: The policyholder must have the same tiv_2015 value as at least one other policyholder (Count is greater than 1).

Criterion 2: The policyholder must be located at a unique lat, lon coordinate (Count = 1).