How to count based on time logic & another column within a group in SQL

Kevin Sun 0
Viewed 30

I have a table that contains the columns accountID sub_account_ID and TimeStamp. I want to write an SQL query that can tell me how many times an accountID switched to another sub_account_ID. This table logs actions that the accountID made, so not all rows in this table is a switching of sub_account_ID action. A switch happens only if the sub_account_ID changed in this table at a later timestamp.

AccountID Sub_Account_ID Timestamp
1 A 1:00
1 A 2:00
1 B 3:00
1 A 4:00
1 B 5:00
1 B 6:00

The result I want in the end is to show that account_id 1 had 3 changes (sub_account switch between 2:00-3:00, 3:00-4:00, and 4:00-5:00).

I am thinking we need a groupby account_ID and some type of window function (maybe lag), but can't wrap my head around the logic needed.

window-functions sql
1 Answers

I used partition by AccountID in case you will want to use this with more than one AccountID at a time.

select AccountID    
      ,Sub_Account_ID   
      ,concat(pre_time,'-',"Timestamp")                              as time_of_change
      ,sum(change) over(partition by AccountID order by "Timestamp") as running_sum_change
from   (
       select *
              ,case when lag(Sub_Account_ID) over(partition by AccountID order by "Timestamp") <> Sub_Account_ID then 1 end as change
              ,lag("Timestamp") over(order by "Timestamp") as pre_time
       from t
       ) t
where  change = 1
AccountID Sub_Account_ID time_of_change running_sum_change
1 B 2:00-3:00 1
1 A 3:00-4:00 2
1 B 4:00-5:00 3

Fiddle

DannySlor0
Related