SQL code for consecutive years as donor

I haven't optimized this query because I only use it from time to time and usually on smaller groups. So I would suggest putting in another cte with a window/partition function to get rid of dupes. Or use a group by clause to get rid of the dupes. Also, this query is only looking for the max amount of consecutive in a set amount of years.

But overall the query works well for my needs:

with cte1 as
(select *, year(cont_dt) as contyear
from T_CONTRIBUTION x
)
select
a.customer_no, fname, lname, ayear, byear, yeardiff, ranki
from
(
select
a.customer_no,
a.contyear as ayear,
b.contyear as byear,
(b.contyear - a.contyear)+1 yeardiff,
dense_rank() over (partition by a.customer_no order by (b.contyear - a.contyear) desc) ranki
from
cte1 a
join cte1 b on a.customer_no = b.customer_no
and b.contyear > a.contyear
where
b.contyear - a.contyear =
(select count(distinct contyear)-1
from cte1 a1
where a.customer_no = a1.customer_no
and a1.contyear between a.contyear and b.contyear and cont_dt >= '2013-01-01' )
) a
inner join T_CUSTOMER m
on a.customer_no = m.customer_no
inner join T_CONTRIBUTION k
on a.customer_no = k.customer_no
inner join
(SELECT a1.customer_no
FROM t_list_contents AS a1
WHERE a1.list_no IN (62259)) AS e
ON e.customer_no = a.customer_no
where ranki = 1 and cust_type in (1,7) and cont_dt <= '2018-12-31'
order by a.customer_no

I haven't optimized this query because I only use it from time to time and usually on smaller groups. So I would suggest putting in another cte with a window/partition function to get rid of dupes. Or use a group by clause to get rid of the dupes. Also, this query is only looking for the max amount of consecutive in a set amount of years.

But overall the query works well for my needs:

with cte1 as
(select *, year(cont_dt) as contyear
from T_CONTRIBUTION x
)
select
a.customer_no, fname, lname, ayear, byear, yeardiff, ranki
from
(
select
a.customer_no,
a.contyear as ayear,
b.contyear as byear,
(b.contyear - a.contyear)+1 yeardiff,
dense_rank() over (partition by a.customer_no order by (b.contyear - a.contyear) desc) ranki
from
cte1 a
join cte1 b on a.customer_no = b.customer_no
and b.contyear > a.contyear
where
b.contyear - a.contyear =
(select count(distinct contyear)-1
from cte1 a1
where a.customer_no = a1.customer_no
and a1.contyear between a.contyear and b.contyear and cont_dt >= '2013-01-01' )
) a
inner join T_CUSTOMER m
on a.customer_no = m.customer_no
inner join T_CONTRIBUTION k
on a.customer_no = k.customer_no
inner join
(SELECT a1.customer_no
FROM t_list_contents AS a1
WHERE a1.list_no IN (62259)) AS e
ON e.customer_no = a.customer_no
where ranki = 1 and cust_type in (1,7) and cont_dt <= '2018-12-31'
order by a.customer_no