Oracle 18c

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 table_name (Dt, ACCOUNT, NAME) AS
SELECT DATE '2024-01-01', 'A', 'XOXO' FROM DUAL UNION ALL
SELECT DATE '2024-01-02', 'A', 'XOXO' FROM DUAL UNION ALL
SELECT DATE '2024-01-02', 'A', 'OXXO' FROM DUAL UNION ALL
SELECT DATE '2024-01-04', 'A', 'XOOX' FROM DUAL UNION ALL
SELECT DATE '2024-01-05', 'A', 'OOXO' FROM DUAL UNION ALL
SELECT DATE '2024-01-06', 'A', 'XOXO' FROM DUAL;;
6 rows affected

SELECT DISTINCT
       dt,
       account,
       SUM(CASE rn WHEN 1 THEN 1 END) OVER (
         PARTITION BY account
         ORDER BY dt
         RANGE BETWEEN UNBOUNDED PRECEDING AND INTERVAL '1' DAY PRECEDING
       ) AS count_distinct_name
FROM   (
  SELECT dt,
         account,
         ROW_NUMBER() OVER (PARTITION BY account, name ORDER BY dt) AS rn
  FROM   table_name
)
ORDER BY account, dt;


DT
ACCOUNT
COUNT_DISTINCT_NAME


2024-01-01 00:00:00
A
null

2024-01-02 00:00:00
A
1

2024-01-04 00:00:00
A
2

2024-01-05 00:00:00
A
3

2024-01-06 00:00:00
A
4

DT	ACCOUNT	COUNT_DISTINCT_NAME
2024-01-01 00:00:00	A	null
2024-01-02 00:00:00	A	1
2024-01-04 00:00:00	A	2
2024-01-05 00:00:00	A	3
2024-01-06 00:00:00	A	4