I am quite beginner in oracle sql, and i am trying to solve some competitive tasks in my freetime. I have come upon one query that i am struggling with.
The task is to get the same occurences of values in 3 different columns, and get them only if the time difference is exactly 1 year. for eg: if there are two rows, and one is for example :2019.01.01 and 2018.01.01 thats good, but if theres another row for the same triplets and a date with 2018.05.05. then the above two is an incorrect solution, cause theres another record between them. but 2017.01.01 , 2018.01.01. and 2019.01.01 is an acceptalbe solution.
The task asks me to use the LEAD analytic function with a partition by following up
A very similar task was almost the same, with the difference that it didnt matter if they had another row between the 1 year difference, my solution for that if it might help understanding my problem is something like this:
select DISTINCT a.user_code, a.item_code, a.selling_method, a.time, b.time from task_table a, task_table b where (a.user_code, a.item_code, a.selling_method) in( select user_code, item_code, selling_method from task_table group by user_code, item_code, selling_method having count(*) > 2 ) and ADD_MONTHS(a.time, 12) = b.time order by user_code, item_code, selling_method;
Theres 1 million record in the table, and you have to get the rows where the usercode, itemcode and sellingmethod triplet are the same, and it is getting mixed with some extra stuff, so basically what i am looking for now is: EDIT: there have to be more than 2 occurences of the same triplets
usercode 5 itemcode 4 sellingmethod 3 time:2018.01.01 usercode 5 itemcode 4 sellingmethod 3 time:2017.01.01 usercode 5 itemcode 4 sellingmethod 3 time:2016.01.01
and there are no usercode itemcode sellingmethod with values 5 ,4 and 3 with dates inbetween two of them
I can mostly understand the LEAD function by reading documentation, but i’ve been struggling to make a good query for hours