I have the following data in my database:
I'm trying to write an SQL query that will count the Retailer IDs where the FSL Status is "Imputed" for three consecutive months, followed immediately by a "Projected" status. I want to include only those cases where the "Projected" status occurs right after three consecutive "Imputed" statuses.
Can someone help me construct this SQL query?
2条答案
按热度按时间8mmmxcuj1#
It's not elegant but it works:
In the select I put a count distinct, but Count(*) could be enough. You know the data, you choose.
jtjikinw2#
You can make use of cumulative sum
sum() over (order by ...)
to identify the required group (3 Imputed + 1 Projected).cte
identify thegrp
.cte2
counts number of rows within thegrp