sql - Count number of years since last deduction

Question

I have a table similar to below where the same account has its fiscal years (FY) and deductions for each year broken out in multiple rows. Accounts can range from 1 - 20+ years. How do I group to one unique row that shows the current year and how many years its been since the account had a deduction?

from this:

to this:

Started to utilize the CTE approach as I have in the past, but as before it started to get ugly and I know there has to be a simpler approach...

Answer 1

Assuming the current year is the most recent year, you would use aggregation:

select account, max(fy),
       sum(case when fy = max_fy then deductions end) as this_year_deduction,
       max(fy) - max(case when deduction < 0 then fy end) as years_since_deduction
from (select t.*, max(fy) over (partition by account) as max_fy
      from t
     ) t
group by account;

Note: I assume the third column is the most recent deduction. The query uses a window function to extract that.