I have been looking through the questions around looping through records but have been unable to find anything that could solve my problem
So I have a table of consumer entries into a campaign. The profile_id is specific to a consumer, here we have two consumers. We see the campaign they entered and the date of entry, with a count telling us which entry it is (chronologically)
profile_id campaign create_date entry
74704338 2320 28-07-2015 1
74704338 2388 28-01-2016 2
74704338 2464 29-04-2016 3
74704338 2476 03-05-2016 4
74704338 2505 25-05-2016 5
81990916 2320 05-11-2015 1
81990916 2388 22-01-2016 2
81990916 2464 28-04-2016 3
81990916 2467 28-04-2016 4
81990916 2434 02-05-2016 5
What I want to do is run through each record for each profile (consumer) and get the max. no of days between the n and the n+1 entry, and the campaign associated with it.
So for profile_id 74704338 we should get the following as the days between 1 and 2 is the largest in the sequence, and campaign 2388 was the campaign they entered after this time elapsed
profile_id campaign num_days
74704338 2388 184
Similarly for 81990916 we should get
profile_id campaign num_days
81990916 2464 97
I presume I will be using a declare statement to do this, but have no idea where to start. Would appreciate your help
Many thanks
Analytic functions were created just for this kind of exercises. First we compute the differences (using the lag() function), then the maximum difference within each "partition" by profile_id, and then in the last (outermost) query we select the rows where the difference equals the maximum.
I wrote this using syntax from Oracle 11.2 and above; for earlier versions, it can be rewritten by creating the column aliases inside the subquery definitions (instead of in their declarations).
with
test_data ( profile_id, campaign, create_date ) as (
select 74704338, 2320, to_date('28-07-2015', 'dd-mm-yyyy') from dual union all
select 74704338, 2388, to_date('28-01-2016', 'dd-mm-yyyy') from dual union all
select 74704338, 2464, to_date('29-04-2016', 'dd-mm-yyyy') from dual union all
select 74704338, 2476, to_date('03-05-2016', 'dd-mm-yyyy') from dual union all
select 74704338, 2505, to_date('25-05-2016', 'dd-mm-yyyy') from dual union all
select 81990916, 2320, to_date('05-11-2015', 'dd-mm-yyyy') from dual union all
select 81990916, 2388, to_date('22-01-2016', 'dd-mm-yyyy') from dual union all
select 81990916, 2464, to_date('28-04-2016', 'dd-mm-yyyy') from dual union all
select 81990916, 2467, to_date('28-04-2016', 'dd-mm-yyyy') from dual union all
select 81990916, 2434, to_date('02-05-2016', 'dd-mm-yyyy') from dual
),
diffs ( profile_id, campaign, create_date, diff ) as (
select profile_id, campaign, create_date,
create_date - lag(create_date) over (partition by profile_id
order by create_date)
from test_data
),
with_max ( profile_id, campaign, create_date, diff, max_diff ) as (
select profile_id, campaign, create_date, diff,
max(diff) over (partition by profile_id)
from diffs
)
select profile_id, campaign, create_date, diff
from with_max
where diff = max_diff
;
PROFILE_ID CAMPAIGN CREATE_DATE DIFF
---------- --------- ----------- -----
74704338 2388 28-01-2016 184
81990916 2464 28-04-2016 97
2 rows selected.