Comparing results of a previous query within a recursive query

Question

I have set up a query to find the earliest time Person X was promoted to a specific rank-

"firsttimeEMplus" AS (
    SELECT MIN(ra."EffectiveDate")
    FROM rankachievement ra
    WHERE ra."NewRank" IN ('Executive Manager', 
         'Associate Director', 
         'Director', 
         'Senior Director', 
         'Executive Director', 
         'Managing Director'))

Now what I would like to do is to use the result of this query as part of a CASE statement within my recursive query where I figure out if Person X was promoted before their parent. So, where the child firsttimeEMplus < parent firsttimeEMplus

SELECT (CASE WHEN (**child**"firsttimeEMplus" <= **parent**"firsttimeEMplus) THEN etc....

But I am confused on what syntax would allow me to compare the parent to the child like this, or if I can even do something like this within a CASE statement. If anyone could provide guidance or resources or where I could find examples of how this might work, it would be much appreciated. Thanks!

Answer 1

This is without recursion.

The idea is to get a list of all your persons with their first_time_em date. Using a left join and a filter clause will return all persons whether they ever made it to EM level.

Join this result back onto itself, connecting each record with its parent record. Again this is a left join since the person at the top of the hierarchy should not have a parent.

The case statement could be just a boolean comparison with coalesce since all we need is true/false. The possible conditions are:

1. Child's first_time_em is before parent's first_time_em, which yields true
2. Child has a first_time_em and the boss does not (null), which coalesce() turns into 9999-12-31, which yields true
3. Child has no first_time_em (null), yields false by the else
4. Child's first_time_em is after parent's first_time_em, yields false by the 'else`
5. Neither child nor parent have a first_time_em, yields false by the else

with person_achievement as (
  select p.person_id, p.parent_id, 
         MIN(ra."EffectiveDate") 
           filter (when ra."NewRank" in 
             ('Executive Manager', 'Associate Director', 'Director', 
              'Senior Director', 'Executive Director', 'Managing Director')
           ) as first_time_em
    from person p
    left join rankachievement ra 
      on ra.person_id = p.person_id
)
select pc.*, 
       case
         when pc.first_time_em < coalesce(pp.first_time_em, '9999-12-31') then true
         else false
       end as made_em_before_boss
  from person_achievement pc
  left join person_achievement pp
    on pp.person_id = pc.parent_id
;