Add a counter based consecutive dates

Question

I have an employee table with employee name and the dates when the employee was on leave. My task is to identify employees who have takes 3 or 5 consecutive days of leave. I tried to add a row_number but it wouldn't restart correct based on the consecutive dates. The desired counter I am after is shown below. Any suggestions please?

Employee    Leave Date  Desired Counter
John        25-Jan-20   1
John        26-Jan-20   2
John        27-Jan-20   3
John        28-Jan-20   4
John        15-Mar-20   1
John        16-Mar-20   2
Mary        12-Feb-20   1
Mary        13-Feb-20   2
Mary        20-Apr-20   1

Desired output (same as in text)

Answer 1

This is a gaps and island problem: islands represents consecutive days of leaves, and you want to enumerate the rows of each island.

Here is an approach that uses the date difference against a monotonically increasing counter to build the groups:

select t.*,
    row_number() over(
        partition by employee, dateadd(day, -rn, leave_date)
        order by leave_date
    ) counter
from (
    select t.*,
        row_number() over(partition by employee order by leave_date) rn
    from mytable t
) t
order by employee, leave_date

Demo on DB Fiddle