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.
CodePudding user response：
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 |