add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name (cust_id, event, event_date, camp_id) AS
SELECT 1, 'OPENED', DATE '2021-09-06', 80 FROM DUAL UNION ALL
SELECT 1, 'SENT', DATE '2021-09-06', 80 FROM DUAL UNION ALL
SELECT 1, 'SENT', DATE '2021-09-14', 80 FROM DUAL UNION ALL
SELECT 2, 'CLICK', DATE '2021-04-15', 80 FROM DUAL UNION ALL
SELECT 2, 'OPENED', DATE '2021-04-15', 80 FROM DUAL UNION ALL
SELECT 2, 'SENT', DATE '2021-04-15', 80 FROM DUAL UNION ALL
SELECT 2, 'CONVERTED', DATE '2021-04-15', 80 FROM DUAL UNION ALL
SELECT 2, 'WEBSITE_VISIT', DATE '2021-04-15', 80 FROM DUAL UNION ALL
SELECT 3, 'OPENED', DATE '2021-06-01', 80 FROM DUAL UNION ALL
SELECT 3, 'SENT', DATE '2021-06-01', 80 FROM DUAL UNION ALL
SELECT 3, 'OPENED', DATE '2021-06-09', 80 FROM DUAL UNION ALL
SELECT 3, 'SENT', DATE '2021-06-09', 80 FROM DUAL UNION ALL
SELECT 3, 'CLICK', DATE '2021-06-10', 80 FROM DUAL UNION ALL
SELECT 3, 'OPENED', DATE '2021-06-10', 80 FROM DUAL UNION ALL
SELECT 3, 'SENT', DATE '2021-06-10', 80 FROM DUAL UNION ALL
SELECT 3, 'CONVERTED', DATE '2021-06-10', 80 FROM DUAL UNION ALL
SELECT 3, 'WEBSITE_VISIT', DATE '2021-06-10', 80 FROM DUAL UNION ALL
SELECT 4, 'SENT', DATE '2021-09-06', 80 FROM DUAL UNION ALL
SELECT 4, 'SENT', DATE '2021-09-14', 80 FROM DUAL UNION ALL
SELECT 5, 'OPENED', DATE '2021-09-20', 80 FROM DUAL UNION ALL
SELECT 5, 'SENT', DATE '2021-09-20', 80 FROM DUAL UNION ALL
SELECT 5, 'SENT', DATE '2021-09-28', 80 FROM DUAL UNION ALL
SELECT 5, 'CLICK', DATE '2021-10-03', 80 FROM DUAL UNION ALL
SELECT 5, 'OPENED', DATE '2021-10-03', 80 FROM DUAL UNION ALL
SELECT 5, 'SENT', DATE '2021-10-03', 80 FROM DUAL UNION ALL
SELECT 5, 'CONVERTED', DATE '2021-10-03', 80 FROM DUAL UNION ALL
SELECT 5, 'WEBSITE_VISIT', DATE '2021-10-03', 80 FROM DUAL UNION ALL
SELECT 5, 'OPENED', DATE '2021-11-05', 80 FROM DUAL UNION ALL
SELECT 6, 'OPENED', DATE '2021-06-01', 80 FROM DUAL UNION ALL
SELECT 6, 'SENT', DATE '2021-06-01', 80 FROM DUAL UNION ALL
SELECT 6, 'OPENED', DATE '2021-06-09', 80 FROM DUAL UNION ALL
SELECT 6, 'SENT', DATE '2021-06-09', 80 FROM DUAL UNION ALL
SELECT 7, 'OPENED', DATE '2021-07-26', 80 FROM DUAL UNION ALL
SELECT 7, 'SENT', DATE '2021-07-26', 80 FROM DUAL UNION ALL
SELECT 7, 'CLICK', DATE '2021-08-03', 80 FROM DUAL UNION ALL
48 rows affected
SELECT cust_id,
event,
event_date,
camp_id,
CASE email_ind
WHEN 'CONVERTED' THEN 'CONVERTED'
WHEN 'INITIAL' THEN 'INITIAL'
WHEN 'REMINDER' THEN 'REMINDER'
END AS email_ind,
CASE
WHEN email_ind = 'CONVERTED'
AND has_reminder = 1
THEN 'REMINDER'
WHEN email_ind = 'CONVERTED'
THEN 'INITIAL'
END AS conversion_type
FROM (SELECT t.*, ROWNUM AS rn FROM table_name t)
MATCH_RECOGNIZE (
PARTITION BY cust_id
ORDER BY rn
MEASURES
CLASSIFIER() AS email_ind,
COUNT(reminder.cust_id) AS has_reminder
ALL ROWS PER MATCH
PATTERN ( ("INITIAL" not_sent*)? (reminder not_sent*)? not_reminder converted | other )
DEFINE
"INITIAL" AS event = 'SENT',
reminder AS event = 'SENT',
not_reminder AS event = 'SENT',
not_sent AS event <> 'SENT',
converted AS event = 'CONVERTED'
)
CUST_ID EVENT EVENT_DATE CAMP_ID EMAIL_IND CONVERSION_TYPE
1 OPENED 06-SEP-21 80 null null
1 SENT 06-SEP-21 80 null null
1 SENT 14-SEP-21 80 null null
2 CLICK 15-APR-21 80 null null
2 OPENED 15-APR-21 80 null null
2 SENT 15-APR-21 80 null null
2 CONVERTED 15-APR-21 80 CONVERTED INITIAL
2 WEBSITE_VISIT 15-APR-21 80 null null
3 OPENED 01-JUN-21 80 null null
3 SENT 01-JUN-21 80 INITIAL null
3 OPENED 09-JUN-21 80 null null
3 SENT 09-JUN-21 80 REMINDER null
3 CLICK 10-JUN-21 80 null null
3 OPENED 10-JUN-21 80 null null
3 SENT 10-JUN-21 80 null null
3 CONVERTED 10-JUN-21 80 CONVERTED REMINDER
3 WEBSITE_VISIT 10-JUN-21 80 null null
4 SENT 06-SEP-21 80 null null
4 SENT 14-SEP-21 80 null null
5 OPENED 20-SEP-21 80 null null
5 SENT 20-SEP-21 80 INITIAL null
5 SENT 28-SEP-21 80 REMINDER null
5 CLICK 03-OCT-21 80 null null
5 OPENED 03-OCT-21 80 null null
5 SENT 03-OCT-21 80 null null
5 CONVERTED 03-OCT-21 80 CONVERTED REMINDER
5 WEBSITE_VISIT 03-OCT-21 80 null null
5 OPENED 05-NOV-21 80 null null
6 OPENED 01-JUN-21 80 null null
6 SENT 01-JUN-21 80 null null
6 OPENED 09-JUN-21 80 null null
6 SENT 09-JUN-21 80 null null
7 OPENED 26-JUL-21 80 null null
7 SENT 26-JUL-21 80 INITIAL null
7 CLICK 03-AUG-21 80 null null
7 OPENED 03-AUG-21 80 null null
7 SENT 03-AUG-21 80 null null
7 CONVERTED 03-AUG-21 80 CONVERTED INITIAL
7 WEBSITE_VISIT 03-AUG-21 80 null null
7 CLICK 04-AUG-21 80 null null
7 OPENED 04-AUG-21 80 null null
7 SENT 04-AUG-21 80 null null
8 OPENED 12-JUL-21 80 null null
8 SENT 12-JUL-21 80 null null
8 OPENED 20-JUL-21 80 null null
8 SENT 20-JUL-21 80 null null
9 SENT 29-APR-21 80 null null
9 SENT 07-MAY-21 80 null null