SQL ORACLE - Find the consecutive days

I am newbie on the field and I've been struggling with a problem for a few days now, and it's driving me crazy. In short: I'm looking for users who placed orders on consecutive days. For example: 20230715, 20230715, 20230716, 20230717, 20230717, 20230718.

Users who did not make purchases consecutively should not be included, for example: 20230715, 20230715, 20230719.

Could you help me write an SQL Oracle code for this? Thank you very much. I also tried to find any solution for this topic, but I haven't found anything so far.

Here is one of my attempts among many. In addition to this, I also tried using the lag() and lead() functions, but with limited success.

I want to extract the orders of customers where consecutive days are found. Also i wanted to manage matching instances in a single row/users because later, i would like to calculate the spendings at those days/users

WITH ranked_data AS (
    SELECT
        "USER",
        "COMPANY",
        "CREATIONDATE",
        ROW_NUMBER() OVER (PARTITION BY "USER", "COMPANY" ORDER BY "CREATIONDATE") AS rn
    FROM
        P_TEST_P
)
SELECT
    "USER",
    "COMPANY",
    LISTAGG("CREATIONDATE", ', ') WITHIN GROUP (ORDER BY "CREATIONDATE") AS "CONSECUTIVE_DATES"
FROM
    ranked_data
GROUP BY
    "USER",
    "COMPANY";

Input example:

USER;Company;creationdate
Liza;Amigurumi KFT;20230712
Liza;Amigurumi KFT;20230713
Liza;Amigurumi KFT;20230714
Liza;Amigurumi KFT;20230715
Liza;Amigurumi KFT;20230728
Liza;Amigurumi KFT;20230718
Liza;Amigurumi KFT;20230722
Liza;Amigurumi KFT;20230723
Liza;Amigurumi KFT;20230724
Liza;Amigurumi KFT;20230729
Liza;Amigurumi KFT;20230729
Liza;Amigurumi KFT;20230729
Liza;Bubu btk;20230703
Liza;Bubu btk;20230703
Liza;Bubu btk;20230708
Liza;Bubu btk;20230711
Liza;Bubu btk;20230722
Liza;PGGNG;20230728
Liza;PGGNG;20230728
Liza;PGGNG;20230728
Liza;PGGNG;20230730
Patrik;YNWA;20230701
Patrik;YNWA;20230706
Patrik;YNWA;20230708
Patrik;YNWA;20230709
Patrik;HUNF;20230725
Patrik;HUNF;20230729
Laszlo;FOUR GRG;20230712
Laszlo;FOUR GRG;20230713
Laszlo;FOUR GRG;20230713
Laszlo;FOUR GRG;20230714

SELECT * FROM (
	SELECT * FROM P_TEST_P 
	MATCH_RECOGNIZE (
		PARTITION BY usr
		ORDER BY creationdate
		MEASURES FIRST(creationdate) AS from_dat, LAST(creationdate) AS to_dat
		PATTERN( conseq+ strt )
		DEFINE 
			conseq AS NEXT(dat) = dat + 1
	)
)
MATCH_RECOGNIZE (
	PARTITION BY usr
	ORDER BY from_dat, to_dat
	MEASURES FIRST(from_dat) AS from_dat, MAX(to_dat) AS to_dat
	PATTERN( merged* strt )
	DEFINE 
		merged AS NEXT(from_dat) = to_dat
)
;

If you change your mind and want to take into account the company, just add it in the PARTITION BY clauses.

CREATE TABLE P_TEST_P AS
SELECT 'Liza' AS "USER", 'Amigurumi KFT' AS "COMPANY", TO_DATE('20230712', 'YYYYMMDD') AS "CREATIONDATE" FROM DUAL UNION ALL
SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230713', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230714', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230715', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230728', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230718', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230722', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230723', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230724', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230729', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230729', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230729', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'Bubu btk', TO_DATE('20230703', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'Bubu btk', TO_DATE('20230703', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'Bubu btk', TO_DATE('20230708', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'Bubu btk', TO_DATE('20230711', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'Bubu btk', TO_DATE('20230722', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'PGGNG', TO_DATE('20230728', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'PGGNG', TO_DATE('20230728', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'PGGNG', TO_DATE('20230728', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'PGGNG', TO_DATE('20230730', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Patrik', 'YNWA', TO_DATE('20230701', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Patrik', 'YNWA', TO_DATE('20230706', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Patrik', 'YNWA', TO_DATE('20230708', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Patrik', 'YNWA', TO_DATE('20230709', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Patrik', 'HUNF', TO_DATE('20230725', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Patrik', 'HUNF', TO_DATE('20230729', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Laszlo', 'FOUR GRG', TO_DATE('20230712', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Laszlo', 'FOUR GRG', TO_DATE('20230713', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Laszlo', 'FOUR GRG', TO_DATE('20230713', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Laszlo', 'FOUR GRG', TO_DATE('20230714', 'YYYYMMDD') FROM DUAL;

31 rows affected

WITH ranked_data AS (
SELECT
"USER",
"COMPANY",
"CREATIONDATE",
ROW_NUMBER() OVER (PARTITION BY "USER", "COMPANY" ORDER BY "CREATIONDATE") AS rn
FROM
P_TEST_P
)
SELECT
"USER",
"COMPANY",
LISTAGG("CREATIONDATE", ', ') WITHIN GROUP (ORDER BY "CREATIONDATE") AS "CONSECUTIVE_DATES"
FROM
ranked_data
WHERE
EXISTS (
SELECT 1
FROM ranked_data r2
WHERE
r2."USER" = ranked_data."USER"
AND r2."COMPANY" = ranked_data."COMPANY"
AND r2.rn = ranked_data.rn + 1
AND r2."CREATIONDATE" = ranked_data."CREATIONDATE" + 1
)
GROUP BY
"USER",
"COMPANY";

fiddle