How can I return desired record when using pivot

I am using a pivot table to return records that have been updated in the past 7 days. If account has been updated, only return contype ‘AAA’ and ‘BBB’ for the ID. Each ID can have up to 8 ‘types’. I want to only return records with cont_desc= 01 BUT contypes that are not AAA or BBB, cont_desc does not have to be 01. With that said, I want all cont_desc in pivot table to be 01. It’s a bit difficult to explain so I will give an example:

Table can look something like this:

ID    contype     date         contdesc
1     AAA         2020-01-30   01
1     AAA         2019-05-05   0002
1     BBB         2020-01-02   01
1     CCC         2020-02-23   38372
2     AAA         2020-01-22   93021
2     AAA         2020-01-30   01
2     BBB         2019-03-09   01
2     DDD         2020-02-20   92821

Notice, ID 1 will get pulled because CCC has last update within last 7 days so it will return the AAA and BBB records of the ID. Also notice, ID 2 will get pulled because contype DDD has been updated within the last 7 days BUT it will pull AAA, id_cont_desc = 93021. I want records to have 01 at all times. I can’t include this in my where clause because records that aren’t AAA or BBB does not have to be 01.

SELECT *
FROM( SELECT
        id_nbr AS ID,
        contact_type AS contype,
        contact_id_desc AS contdesc
      FROM table
      WHERE ((contact_type = 'AAA' OR contact_type = 'BBB' AND cont_id_desc = '01') OR contact_type NOT = 'AAA' OR 
        contact_type NOT = 'BBB'
        AND (last_update >= CURRENT_DATE - INTERVAL '7' DAY)
        QUALIFY ROW_NUMBER() OVER (PARTITION BY id_nbr, contact_type
        ORDER BY last_update) = 1) as internal_select
      PIVOT (MAX(contype) contype,MAX(contdesc) contdesc FOR contype IN ('AAA','BBB')) derived_Pilot;

It may return something like:

  ID  'AAA'_contype     'AAA'_contdesc   'BBB'_contype     'BBB'_contdesc
  1    AAA              01                BBB               01
  2    AAA              93021             BBB               01

Record 1 looks great. All contdesc’s are equal to 01. Notice record 2, ‘AAA’_contdesc = 93021. This is because a record that is NOT equal to AAA or BBB (but has been updated in the past 7 days) but returned contype AAA with contdesc not equal to 01. I apologize in advance, this is a bit confusing but I tried my best.

Any suggestions are appreciated! Thanks in advance!

Answer

Is this what you want?

select t.*
from (select t.*,
             max(date) over (partition by id) as max_date
      from t
     ) t
where max_date >= CURRENT_DATE - INTERVAL '7' DAY) and
      contract_type in ('AAA', 'BBB') and
      contdesc = '01';

Then your desired results would appear to be:

select id,
       max(case when contract_type = 'AAA' then contract_type end) as AAA_contract_type,
       max(case when contract_type = 'AAA' then contdesc end) as AAA_contdesc,
       max(case when contract_type = 'BBB' then contract_type end) as BBB_contract_type,
       max(case when contract_type = 'BBB' then contdesc end) as BBB_contdesc
from (select t.*,
             max(date) over (partition by id) as max_date
      from t
     ) t
where max_date >= CURRENT_DATE - INTERVAL '7' DAY) and
      contract_type in ('AAA', 'BBB') and
      contdesc = '01'
group by id;