Hi everybody,
I was asked in an interview how I can simulate the lead analytic function using queries. below is the data presented
with dataset as
(
select 1 id , to_date('01/14/2021','mm/dd/yyyy') date1 from dual union all
select 1 id , to_date('01/28/2021','mm/dd/yyyy') date1 from dual union all
select 2 id , to_date('02/05/2021','mm/dd/yyyy') date1 from dual union all
select 3 id , to_date('02/05/2021','mm/dd/yyyy') date1 from dual union all
select 3 id , to_date('02/21/2021','mm/dd/yyyy') date1 from dual
)
they wanted to display the output such as:
id date1 date2
1 01/14/2021 01/28/2021
2 02/05/2021
3 02/05/2021 02/21/2021
I wrote a query using lead function. my query was correct using lead but they wanted me to use queries only. the only thing i could think of was self join and join by id col. so i wrote the following:
SELECT a.date1 , case when a.date1 == b.date1 then null else b.date1 end date2
FROM dataset a
join dataset b on(a.id= b.id)
where a.date1 <= b.date1
I was told I am on the right track using self join. when i got home, I ran the same query and notice I was half correct and my query didnt produce the correct output they wanted. I am getting duplicates and thats most likely because of the self join
my question is, how can I modify my query to display the output above. the query should simulate the LEAD analytic function. I am curious to know how it is done using self join or queries only using oracle 11g. any ideas? thanks in advance