Basically, I have outbound call history data for agents and need to flag when they are working on the autodialer, doing manual calls, and doing voicemails. The logic for determining when an agent is working on voicemails is shown by the value ‘LM’ in the column ‘Status’. The Call_time column contains timestamps in the hh:mm:ss format ordered asc which shows us all the calls an agent has taken throughout the day. I need to write a query that calculates the elapsed time between timestamps based on everytime there are at least 4 ‘LM’s in a row and stops whenever the status is anything else.

Im using Snowflake btw (its a query editor)

I tried using the row number function in a window function to find when the column has at least 4 or more values for ‘LM’ but it yielded inefficient results.