SQL Query counts to get latest records count only

Question

I have table with userID, FY, clientID
I want a SQL statement that gives me the latest interaction count with the client by the user.

Example: In the below table I have User 1 who had interactions with client "10001" in 2024, 23, 22, after that in 2021 he had interaction with other client called "10002" and in the past in 2020, he had interaction with old client "10001".

But, I want to see the latest and current interactions User is having with the clients.
So, it should be 3 for the userID 1

UserID	FY	ClientID
1	2024	10001
1	2023	10001
1	2022	10001
1	2021	10002
2	2024	10003
2	2023	10003
3	2024	10001
1	2020	10001

2	2016	10003

Results I am looking for are:

UserID	FY	ClientID	CountOfRecentInteractions
1	2024	10001	3
2	2024	10003	2
3	2024	10001	1

Is this possible with SQL query?

score 0 · Answer 1 · answered Aug 18 '23 at 17:02

Something like this:

with rec as (
  select top 1 with ties * from YourTable order by ROW_NUMBER() over (partition by UserID order by FY desc)
  union all select t.*  from rec inner join @TABLE1 t on t.UserID = rec.UserID and t.ClientID = rec.ClientID and t.FY=rec.FY-1
  )
select UserID,MAX(FY) FY,ClientID,COUNT(1) from rec group by UserID,ClientID order by UserID

score 0 · Answer 2 · answered Aug 21 '23 at 10:43

Finding the latest row is the easy part. You can use row_number()

For the Count of Interactions, that is the gap and islands problem. For this, you can use LAG() and compare current with previous row ClientID and set a flag (g). After that performing a cumulative sum of the flag will gives you the grouping required

with cte1 as
(
  select UserID,
         FY, ClientID,
         g = case when ClientID <> lag(ClientID) over (partition by UserID 
                                                           order by FY desc)
                  then 1
                  else 0
                  end
  from   T
),
cte2 as
(
  select *, grp = sum(g) over(partition by UserID order by FY desc)
  from   cte1
),
cte3 as
(
  select *, 
         CountOfRecentInteractions = count(*) over (partition by UserID, grp),
         Rank = row_number() over (partition by UserID 
                                       order by FY desc)
  from   cte2
)
select UserID, FY, ClientID, CountOfRecentInteractions
from   cte3
where  Rank = 1
order by UserID

SQL Query counts to get latest records count only

2 Answers2