postgresql - MIN function in SQL not working as expected

Question

I have a table that lists players of a game and the history of their level changes within the game (beginner, intermediate, advanced, elite, pro). I am trying to build a query that will accurately identify people who reached a level of advanced or higher for the first time so far in 2021 (it is possible for players to skip ranks, so I want the earliest date that they reached either he advanced, elite, or pro levels). I'm currently using this query:

SELECT *
FROM (
SELECT p."ID", ra."NewRank", MIN(ra."EffectiveDate") AS "first_time_adv"
FROM rankachievement ra
JOIN player p
ON ra."ID" = p."ID"
WHERE ra."NewRank" IN ('Advanced',
        'Elite',
        'Pro')
GROUP BY 1, 2) AS t
WHERE t."first_time_adv" > '1/1/2021'
ORDER BY 1 

Right now, this is pulling in all of the people who reached the advanced level for the first time in 2021, but it is also pulling in some people that had previously reached advanced in 2020 and have now achieved an even higher level- I don't want to include these people, which is why I had used MIN with the date.

For example, it is correctly pulling in Players 1 and 2, who both reached advanced on January 2nd, and player 4, who reached elite on January 4th after skipping over the advanced level (went straight from intermediate to elite). But it is also pulling in Player 3, who reached advanced on December 30th, 2020 and then reached elite on January 10th- I do not want player 3 to be included because they reached advanced for the first time before 2021. How can I get my query to exclude people like this?

question from:https://stackoverflow.com/questions/65904853/min-function-in-sql-not-working-as-expected

Answer 1

You're getting two results for Player 3... one with the advanced and one with the elite because you're grouping by NewRank. The one where Player 3 reached advanced gets removed from the result set by your WHERE t.first_time_adv > '1/1/2021' and the elite passes through. I suggest trying to use a FILTER and OVER with MIN().

Your results from the inner query include something like this:

id | new_rank | MIN(EffectiveDate)
---+----------+-------------------
1  | advanced | the min date for this record (12/30/2020)
1  | elite    | the min date for this record (01/10/2021)

This is because you're getting the MIN while grouping both ID AND NewRank. You want the MIN over ALL records of that player. If you grouped by only ID you might get the behavior you were looking for but that would require you to remove the NewRank from the SELECT clause.

---

I suspect you want the rank in your final result set so try something like this:

WITH data AS
(
SELECT p."ID"
     , ra."NewRank"
     , ra."EffectiveDate"
     , MIN(ra."EffectiveDate")
       FILTER (WHERE ra."NewRank" = 'Advanced')
       OVER (PARTITION BY p."ID") AS "first_time_adv"
  FROM rankachievement ra
  JOIN player p ON ra."ID" = p."ID"
 WHERE ra."NewRank" IN ('Advanced', 'Elite', 'Pro')
)
SELECT *
  FROM data d
 WHERE d."first_time_adv" > '1/1/2021'
ORDER BY 1
;

Previously you were finding the MIN(EffectiveDate) for any rank IN ('Advanced', 'Elite', 'Pro')... now you're truly finding the EffectiveDate for when a player reached 'Advanced'.