1

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

1 Answer 1

0

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.

1
  • Thanks a lot @ergest this worked exactly as I needed.
    – Ryan
    Sep 21, 2022 at 16:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.