How to filter results by specifying the criteria you want the MAX date stamp for?

Question

Please bear with me as I am a beginner and could not find this answer elsewhere. I am working with employee data and need to determine which Product Group a given Team was associated with on the last day that team was active in our system. Each employee record includes the Product Group they sat in, their team, and whether the team is still active or not. The data table I am using has daily partitions designated by the "ds" field dating back 8+ years.

Using the following SQL, I can see what the last date a team sat under a given product group was:

SELECT product_group_name, team_name, max(ds) as last_active_ds
FROM
    employee_table ep
WHERE
        is_active = TRUE
        AND team_name = 'Moon'

GROUP BY 1,2

This returns:

Product Group	Team	Last Active Date
Space Group	Moon	08-19-2022
Water Group	Moon	06-15-2022

In the output above, you can see the Moon team used to sit under the Water Group but moved to the Space Group after 6-15-2022. But all I care about in this instance is that the Moon team was Last Active on 8-19-2022 and at the time they sat under the Space group.

How can I modify the above query to only return "Space Group" as the product group they last sat under at the time of being deprecated? I tried the CTE below which I thought would do it but I am running into pretty much the same issue. Appreciate any help.

WITH latest_active_ds AS (
    SELECT DISTINCT 
    team_name, 
    MAX(ds) as latest_active
FROM
    employee_table ep 
WHERE
    is_active = TRUE
    AND team_name = 'Moon'
GROUP BY 1
)

SELECT DISTINCT lad.team_name, lad.latest_active, ep.product_group_name
FROM 
latest_active_ds lad
LEFT JOIN employee table ep on lad.team_name = ep.team_name AND lad.latest_active = ep.ds
WHERE lad.team_name = 'Moon'
AND lad.latest_active = ep.ds

Answer 1

It seems to me that a simple row_number would help in your case:

with cte as (
    select product_group_name, 
           team_name,
           ds,
           row_number() over(partition by team_name order by ds desc ) rnk
    from employee_table 
) select product_group_name,team_name ,ds
  from cte
  where rnk =1;

Note, I guess the date is in proper date format otherwise you need to use str_to_date function in the order by clause. If you want only the result for the team Moon add and team='Moon' at the end of the query.