Hello All ,
Please will need help in solving below business requirement, excerpts below...
Thanks very much
Oracle version: 12.2.0.1.0
Problem:
EVENTS_FACT_TAB.NEW_EVENT_FLAG - Should tell us if the viewing event was the first view event for the given device
EVENTS_FACT_TAB.REJUV_EVENT_FLAG - Should tell us if the viewing event was the first view event after a specified absence for the given device
for the past 364 Days
Notes for implementation :
First check the Lookup_dim table to see if the device_id is present or not
We should be able to handle multiple view events on a given day for a given device. Only the FIRST view event of the day should be tagged as "NEW",
not the rest of the view events from the same day
This applies to REJUV_EVENT_FLAG as well.
DDL and DML:
rejuv.txt (6.22 KB)Expected Result:
Current Attempt: To resolve_New_event_flag
--load below value into temp table
SELECT FACT_ID,
DEVICE_ID,
VIEW_START,
VIEW_END,
NEW_EVENT_FLAG,
REJUV_EVENT_FLAG ,
ROW_NUMBER () OVER (PARTITION BY device_id ORDER BY VIEW_END DESC) RANK_DEVICE
FROM EVENTS_FACT_TAB;
--update fact table
UPDATE EVENTS_FACT_TAB a
set a.NEW_EVENT_FLAG = 1
where a.device_id in (select b.device_id from Temp_table where rank_device = 1)