Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE PER_PERSON_ADDR_USAGES_F (PERSON_ID, ADDR_ID, TYPE, EFF_START_DATE, EFF_END_DATE) AS
SELECT 6207445, '7390814', 'HOME', DATE '2024-02-26', DATE '4712-12-31' FROM DUAL UNION ALL
SELECT 6207445, '0042548', 'HOME', DATE '2023-05-15', DATE '4712-12-31' FROM DUAL UNION ALL
SELECT 6207445, '6462255', 'HOME', DATE '2022-01-31', DATE '4712-12-31' FROM DUAL UNION ALL
SELECT 6207445, '6462287', 'MAIL', DATE '2022-01-31', DATE '4712-12-31' FROM DUAL UNION ALL
SELECT 6207445, '0042571', 'MAIL', DATE '2023-05-15', DATE '4712-12-31' FROM DUAL UNION ALL
SELECT 1111111, '0042578', 'MAIL', DATE '2023-05-15', DATE '4712-12-31' FROM DUAL;
6 rows affected

SELECT person_id,
       type, 
       COUNT(*) AS rec_count
FROM   PER_PERSON_ADDR_USAGES_F 
GROUP BY person_id, type
ORDER BY rec_count DESC


PERSON_ID
TYPE
REC_COUNT


6207445
HOME
3

6207445
MAIL
2

1111111
MAIL
1


SELECT person_id,
       type, 
       MAX(addr_id) KEEP (DENSE_RANK LAST ORDER BY eff_start_date) AS addr_id,
       COUNT(*) AS rec_count
FROM   PER_PERSON_ADDR_USAGES_F 
GROUP BY person_id, type
ORDER BY rec_count DESC


PERSON_ID
TYPE
ADDR_ID
REC_COUNT


6207445
HOME
7390814
3

6207445
MAIL
0042571
2

1111111
MAIL
0042578
1


SELECT person_id,
       type, 
       addr_id,
       rec_count
FROM   (
  SELECT person_id,
         type, 
         addr_id,
         ROW_NUMBER() OVER (PARTITION BY person_id, type ORDER BY eff_start_date DESC)
           AS rn,
         COUNT(*) OVER (PARTITION BY person_id, type) AS rec_count
  FROM   PER_PERSON_ADDR_USAGES_F 
)
WHERE  rn = 1
ORDER BY rec_count DESC


PERSON_ID
TYPE
ADDR_ID
REC_COUNT


6207445
HOME
7390814
3

6207445
MAIL
0042571
2

1111111
MAIL
0042578
1