How can I write a SQL query for this? I need a SQL query for this

See more:

SQL

Oracle

title country market date

Title A Australia 54 01-02-18
Title A Australia 54 02-04-18
Title A Australia 54 29-09-18
Title A Australia 54 17-04-19
Title A Australia 54 15-04-20
Title A Australia 54 20-04-20
Title A Australia 54 10-05-22
Title A Australia 54 09-05-23
Title B Australia 54 29-09-18
Title B Germany 54 07-01-19
Let us consider three dates a,b,c and the three dates belongs to same title, country and market. Now my requirement is if the difference between two consecutive dates is less than 365 days then cycle should 1 else it should be incremented by one to next number. If the cycle is incremented then the reference date should be changed to the current date and the difference should be measured between next date and changed reference date and the process continues on till the current group i.e., dates belongs to same title, country and market are processed .

Then the process begins again from the beginning for the next set.

the output should as below: title country market date cycle

Title A Australia 54 01-02-18 1
Title A Australia 54 02-04-18 1
Title A Australia 54 29-09-18 1
Title A Australia 54 17-04-19 2
Title A Australia 54 15-04-20 2
Title A Australia 54 20-04-20 3
Title A Australia 54 10-05-22 4
Title A Australia 54 09-05-23 4
Title B Australia 54 29-09-18 1
Title B Germany 54 07-01-19 1

What I have tried:

tried with case statements and tried with multiple case and decode statements