sql for finding value gaps

Question

Hi,

I have the following table:

anomaly:

that looks like this:

exec_id account_id is_alerted

100 123 0
.
.
95 123 0
94 123 0
93 123 0
92 123 0
91 123 1
100 234 0
.
.
95 234 0
94 234 0
93 234 0
92 234 0
91 234 0

I need a query that returns the accounts that meet the following condition:

for each account - the gap between is_alerted = 0 and is alerted = 1 is exactly 12 exec_ids

Answer 1

Please check:

CREATE TABLE #anomaly(exec_id INT,account_id INT,is_alerted INT)
INSERT INTO #anomaly VALUES(100 ,123 ,0),(95 ,123 ,0),(94, 123, 0),(93 ,123, 0),
(92 ,123 ,0),
(91 ,123, 1),(100 ,234, 0),(95 ,234, 0),(94, 234 ,0),(93, 234 ,0),(92, 234 ,0)
,(91 ,234,0)

SELECT * FROM  #anomaly 

;WITH cte
as(SELECT *,ROW_NUMBER() OVER(PARTITION BY account_id ORDER BY account_id) rr
FROM #anomaly)
,cte2 as(SELECT account_id,MIN(rr) m1 
FROM cte
WHERE is_alerted=0
GROUP BY account_id)
,cte3 as(
SELECT account_id,MIN(rr) m2
FROM cte
WHERE is_alerted=1
GROUP BY account_id)

SELECT * FROM 
cte2 c2
JOIN cte3 c3
ON c2.account_id=c3.account_id AND abs(m2-m1)=12

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.