Display group having certain value only on max date and not in other entries of group

I am looking to display the group which is grouped on id column where trans_cd='Audit' only on max trans_proc_dt of that group. There should not be any other trans_cd='Audit' execpt for max trans_proc_dt.

    ID  TRANS_PROC_DT   TRANS_CD    TRANS_AMT
165     5/13/2020       Renewal         553
165     10/22/2020      Cancellation    -376
165     11/24/2020      Audit   3
165     6/2/2021        Change  0
165     6/2/2021        Audit   -7
165     6/3/2021        Audit   0
497     5/1/2020        Renewal 1394
497     1/11/2021       Cancellation    -578
497     2/10/2021       Audit   -3
497     4/28/2021       Audit   76
497     5/12/2021       Audit   -73
497     6/2/2021        Change  0
511     4/27/2020       Renewal 4409
511     7/30/2020       Change  0
511     10/5/2020       Cancellation    -2558
511     2/18/2021       Audit   2806
577     5/15/2020       Renewal 829
577     2/12/2021       Audit   -123
577     4/28/2021       Audit   118
577     5/12/2021       Audit   5
577     6/2/2021        Change  0
577     6/2/2021        Audit   -5
577     6/3/2021        Audit   0
577     12/4/2020       Renewal 1996
577     6/2/2021        Change  0
751     5/13/2020       Renewal 1307
751     1/28/2021       Cancellation    -523
751     3/3/2021        Audit   481
751     4/28/2021       Audit   120
751     5/12/2021       Audit   -601
751     6/2/2021        Change  0
751     6/2/2021        Audit   601
751     6/3/2021        Audit   0
984     5/13/2020       Renewal 1081
984     11/2/2020       Change  0
984     6/3/2021        Audit   0

My output should be

ID  TRANS_PROC_DT   TRANS_CD    TRANS_AMT
511     4/27/2020       Renewal 4409
511     7/30/2020       Change  0
511     10/5/2020       Cancellation    -2558
511     1/27/2021       Renewal 4409
511     2/18/2021       Audit   2806
984     5/13/2020       Renewal 1081
984     11/2/2020       Change  0
984     6/3/2021        Audit   0

I can’t think how to go about getting my result set.

Answer

You can use analytic functions to calculate absolute maximum date and minimum date of Audit code per group, then compare them: if no more such transactions occurred on other dates, they should be equal.

with a(ID, TRANS_PROC_DT, TRANS_CD, TRANS_AMT) as (
select 165, '5/13/2020', 'Renewal', 553 from dual union all
select 165, '10/22/2020', 'Cancellation', -376 from dual union all
select 165, '11/24/2020', 'Audit', 3 from dual union all
select 165, '6/2/2021', 'Change', 0 from dual union all
select 165, '6/2/2021', 'Audit', -7 from dual union all
select 165, '6/3/2021', 'Audit', 0 from dual union all
select 497, '5/1/2020', 'Renewal', 1394 from dual union all
select 497, '1/11/2021', 'Cancellation', -578 from dual union all
select 497, '2/10/2021', 'Audit', -3 from dual union all
select 497, '4/28/2021', 'Audit', 76 from dual union all
select 497, '5/12/2021', 'Audit', -73 from dual union all
select 497, '6/2/2021', 'Change', 0 from dual union all
select 511, '4/27/2020', 'Renewal', 4409 from dual union all
select 511, '7/30/2020', 'Change', 0 from dual union all
select 511, '10/5/2020', 'Cancellation', -2558 from dual union all
select 511, '2/18/2021', 'Audit', 2806 from dual union all
select 577, '5/15/2020', 'Renewal', 829 from dual union all
select 577, '2/12/2021', 'Audit', -123 from dual union all
select 577, '4/28/2021', 'Audit', 118 from dual union all
select 577, '5/12/2021', 'Audit', 5 from dual union all
select 577, '6/2/2021', 'Change', 0 from dual union all
select 577, '6/2/2021', 'Audit', -5 from dual union all
select 577, '6/3/2021', 'Audit', 0 from dual union all
select 577, '12/4/2020', 'Renewal', 1996 from dual union all
select 577, '6/2/2021', 'Change', 0 from dual union all
select 751, '5/13/2020', 'Renewal', 1307 from dual union all
select 751, '1/28/2021', 'Cancellation', -523 from dual union all
select 751, '3/3/2021', 'Audit', 481 from dual union all
select 751, '4/28/2021', 'Audit', 120 from dual union all
select 751, '5/12/2021', 'Audit', -601 from dual union all
select 751, '6/2/2021', 'Change', 0 from dual union all
select 751, '6/2/2021', 'Audit', 601 from dual union all
select 751, '6/3/2021', 'Audit', 0 from dual union all
select 984, '5/13/2020', 'Renewal', 1081 from dual union all
select 984, '11/2/2020', 'Change', 0 from dual union all
select 984, '6/3/2021', 'Audit', 0 from dual
)
, last_dt as (
  select
    a.*
    , min(
       case trans_cd
       when 'Audit'
       then to_date(TRANS_PROC_DT, 'mm/dd/yyyy')
       end
    ) over(partition by id)
    as audit_dt
    , max(to_date(TRANS_PROC_DT, 'mm/dd/yyyy'))
      over(partition by id)
    as max_dt

  from a
)
select
  id
  , trans_proc_dt
  , trans_cd
  , trans_amt
from last_dt
where max_dt = audit_dt
order by id, to_date(TRANS_PROC_DT, 'mm/dd/yyyy')

 ID | TRANS_PROC_DT | TRANS_CD     | TRANS_AMT
--: | :------------ | :----------- | --------:
511 | 4/27/2020     | Renewal      |      4409
511 | 7/30/2020     | Change       |         0
511 | 10/5/2020     | Cancellation |     -2558
511 | 2/18/2021     | Audit        |      2806
984 | 5/13/2020     | Renewal      |      1081
984 | 11/2/2020     | Change       |         0
984 | 6/3/2021      | Audit        |         0

db<>fiddle here