585. Investments in 2016

SQL 50 · Subqueries (day 6). Decode it with the hints, solve it on LeetCode, then compare with the reference solution in MySQLPostgreSQL.

MediumSolve it on LeetCode ↗

Your work

Status Notes Your solution (MySQLPostgreSQL)

Hints

Open one at a time. Try to finish with as few as you can.

1. What does one output row represent?

One number.

2. Approach

In a CTE, COUNT(*) OVER (PARTITION BY tiv_2015) and COUNT(*) OVER (PARTITION BY lat, lon). Keep rows with the first > 1 and the second = 1, ROUND(SUM(tiv_2016), 2).

3. The trap

Two separate group conditions on the same rows; window counts keep each row.Two group conditions on the same rows; window counts keep each row. The columns are floats, so cast to numeric before ROUND.

Reference solution

Tested on MySQL 8.0 and PostgreSQL 16. Switch dialect at the top of the page.

Show the MySQLPostgreSQL solution

WITH t AS (
  SELECT tiv_2016,
    COUNT(*) OVER (PARTITION BY tiv_2015) AS same_tiv,
    COUNT(*) OVER (PARTITION BY lat, lon) AS same_loc
  FROM Insurance
)
SELECT ROUND(SUM(tiv_2016), 2) AS tiv_2016
FROM t
WHERE same_tiv > 1 AND same_loc = 1;WITH t AS (
  SELECT tiv_2016,
    COUNT(*) OVER (PARTITION BY tiv_2015) AS same_tiv,
    COUNT(*) OVER (PARTITION BY lat, lon) AS same_loc
  FROM Insurance
)
SELECT ROUND(SUM(tiv_2016)::numeric, 2) AS tiv_2016
FROM t
WHERE same_tiv > 1 AND same_loc = 1;

Patterns used

Window functions · MySQL vs PostgreSQL