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 my_table
(episode_id number,
file_id number,
offer_id number,
order_date date
);
insert all
into my_table values (1234,3000,700, to_date('2020/10/10','YYYY/MM/DD'))
into my_table values (1234,3000,800, to_date('2020/10/10','YYYY/MM/DD'))
into my_table values (1234,3000,900, to_date('2020/10/10','YYYY/MM/DD'))
into my_table values (1234,3000,800, to_date('2020/10/11','YYYY/MM/DD'))
into my_table values (1234,3000,900, to_date('2020/10/11','YYYY/MM/DD'))
into my_table values (1234,4000,700, to_date('2020/10/10','YYYY/MM/DD'))
into my_table values (1234,4000,800, to_date('2020/10/10','YYYY/MM/DD'))
into my_table values (1234,4000,700, to_date('2020/10/11','YYYY/MM/DD'))
into my_table values (4321,3000,700, to_date('2020/10/10','YYYY/MM/DD'))
into my_table values (4321,3000,800, to_date('2020/10/10','YYYY/MM/DD'))
into my_table values (4321,3000,900, to_date('2020/10/10','YYYY/MM/DD'))
into my_table values (4321,3000,800, to_date('2020/10/11','YYYY/MM/DD'))
into my_table values (4321,3000,900, to_date('2020/10/11','YYYY/MM/DD'))
into my_table values (4321,4000,700, to_date('2020/10/10','YYYY/MM/DD'))
into my_table values (4321,4000,800, to_date('2020/10/10','YYYY/MM/DD'))
into my_table values (4321,5000,700, to_date('2020/10/10','YYYY/MM/DD'))
into my_table values (4321,5000,700, to_date('2020/10/11','YYYY/MM/DD'))
into my_table values (4321,4000,700, to_date('2020/10/11','YYYY/MM/DD'))
into my_table values (4321,3000,700, to_date('2020/10/12','YYYY/MM/DD'))
SELECT * FROM DUAL;
19 rows affected
select episode_id,
file_id,
offer_id,
order_date,
LAG(order_date) OVER(
PARTITION BY episode_id, file_id, offer_id
ORDER BY order_date
) AS previous_order_date
FROM my_table
ORDER BY ROWNUM
| EPISODE_ID | FILE_ID | OFFER_ID | ORDER_DATE | PREVIOUS_ORDER_DATE |
|---|---|---|---|---|
| 1234 | 3000 | 700 | 2020-10-10 00:00:00 | null |
| 1234 | 3000 | 800 | 2020-10-10 00:00:00 | null |
| 1234 | 3000 | 900 | 2020-10-10 00:00:00 | null |
| 1234 | 3000 | 800 | 2020-10-11 00:00:00 | 2020-10-10 00:00:00 |
| 1234 | 3000 | 900 | 2020-10-11 00:00:00 | 2020-10-10 00:00:00 |
| 1234 | 4000 | 700 | 2020-10-10 00:00:00 | null |
| 1234 | 4000 | 800 | 2020-10-10 00:00:00 | null |
| 1234 | 4000 | 700 | 2020-10-11 00:00:00 | 2020-10-10 00:00:00 |
| 4321 | 3000 | 700 | 2020-10-10 00:00:00 | null |
| 4321 | 3000 | 800 | 2020-10-10 00:00:00 | null |
| 4321 | 3000 | 900 | 2020-10-10 00:00:00 | null |
| 4321 | 3000 | 800 | 2020-10-11 00:00:00 | 2020-10-10 00:00:00 |
| 4321 | 3000 | 900 | 2020-10-11 00:00:00 | 2020-10-10 00:00:00 |
| 4321 | 4000 | 700 | 2020-10-10 00:00:00 | null |
| 4321 | 4000 | 800 | 2020-10-10 00:00:00 | null |
| 4321 | 5000 | 700 | 2020-10-10 00:00:00 | null |
| 4321 | 5000 | 700 | 2020-10-11 00:00:00 | 2020-10-10 00:00:00 |
| 4321 | 4000 | 700 | 2020-10-11 00:00:00 | 2020-10-10 00:00:00 |
| 4321 | 3000 | 700 | 2020-10-12 00:00:00 | 2020-10-10 00:00:00 |
select *
FROM my_table
MATCH_RECOGNIZE(
PARTITION BY episode_id, file_id
ORDER BY order_date DESC, offer_id DESC
MEASURES
current_row.offer_id AS offer_id,
current_row.order_date AS order_date,
prev_day.order_date AS previous_order_date
AFTER MATCH SKIP TO NEXT ROW
PATTERN ( current_row {- same_day* -} prev_day? )
DEFINE
same_day AS TRUNC(current_row.order_date) = TRUNC(same_day.order_date)
)
ORDER BY episode_id, file_id, order_date, offer_id
| EPISODE_ID | FILE_ID | OFFER_ID | ORDER_DATE | PREVIOUS_ORDER_DATE |
|---|---|---|---|---|
| 1234 | 3000 | 700 | 2020-10-10 00:00:00 | null |
| 1234 | 3000 | 800 | 2020-10-10 00:00:00 | null |
| 1234 | 3000 | 900 | 2020-10-10 00:00:00 | null |
| 1234 | 3000 | 800 | 2020-10-11 00:00:00 | 2020-10-10 00:00:00 |
| 1234 | 3000 | 900 | 2020-10-11 00:00:00 | 2020-10-10 00:00:00 |
| 1234 | 4000 | 700 | 2020-10-10 00:00:00 | null |
| 1234 | 4000 | 800 | 2020-10-10 00:00:00 | null |
| 1234 | 4000 | 700 | 2020-10-11 00:00:00 | 2020-10-10 00:00:00 |
| 4321 | 3000 | 700 | 2020-10-10 00:00:00 | null |
| 4321 | 3000 | 800 | 2020-10-10 00:00:00 | null |
| 4321 | 3000 | 900 | 2020-10-10 00:00:00 | null |
| 4321 | 3000 | 800 | 2020-10-11 00:00:00 | 2020-10-10 00:00:00 |
| 4321 | 3000 | 900 | 2020-10-11 00:00:00 | 2020-10-10 00:00:00 |
| 4321 | 3000 | 700 | 2020-10-12 00:00:00 | 2020-10-11 00:00:00 |
| 4321 | 4000 | 700 | 2020-10-10 00:00:00 | null |
| 4321 | 4000 | 800 | 2020-10-10 00:00:00 | null |
| 4321 | 4000 | 700 | 2020-10-11 00:00:00 | 2020-10-10 00:00:00 |
| 4321 | 5000 | 700 | 2020-10-10 00:00:00 | null |
| 4321 | 5000 | 700 | 2020-10-11 00:00:00 | 2020-10-10 00:00:00 |