Select N preceding rows when column match is true I am attempting to select 10 rows preceding any row where the keyPerformanceIndicator column is TRUE (order by descending date/time).  I suspect this is somehow achieved through a window function in combination with a lag function, but to be honest, I'm lost.  Any help is appreciated.  For context, I'm doing this in BigQuery.
Example dataset




date
time
keyPerformanceIndicator




3/8/2021
15:28:00
TRUE


3/8/2021
15:27:00
TRUE


3/8/2021
15:26:00
FALSE


3/8/2021
15:25:00
FALSE


3/8/2021
15:24:00
FALSE


3/8/2021
15:23:00
FALSE


3/8/2021
15:22:00
FALSE


3/8/2021
15:21:00
FALSE


3/8/2021
15:20:00
FALSE


3/8/2021
15:19:00
FALSE


3/8/2021
15:18:00
FALSE


3/8/2021
15:17:00
TRUE


3/8/2021
15:16:00
FALSE


3/8/2021
15:15:00
FALSE


3/8/2021
15:14:00
FALSE


3/8/2021
15:13:00
FALSE


3/8/2021
15:12:00
FALSE


3/8/2021
15:11:00
FALSE


3/8/2021
15:10:00
FALSE


3/8/2021
15:09:00
FALSE


3/8/2021
15:08:00
FALSE


3/8/2021
15:07:00
FALSE


3/8/2021
15:06:00
FALSE


3/8/2021
15:05:00
FALSE


3/8/2021
15:04:00
FALSE


3/8/2021
15:03:00
FALSE


3/8/2021
15:02:00
FALSE


3/8/2021
15:01:00
FALSE


3/8/2021
15:00:00
FALSE


3/8/2021
14:59:00
FALSE


3/8/2021
14:58:00
FALSE


3/8/2021
14:57:00
FALSE


3/8/2021
14:56:00
FALSE




Anticipated Outcome:




date
time
keyPerformanceIndicator




3/8/2021
15:28:00
TRUE


3/8/2021
15:27:00
TRUE


3/8/2021
15:26:00
FALSE


3/8/2021
15:25:00
FALSE


3/8/2021
15:24:00
FALSE


3/8/2021
15:23:00
FALSE


3/8/2021
15:22:00
FALSE


3/8/2021
15:21:00
FALSE


3/8/2021
15:20:00
FALSE


3/8/2021
15:19:00
FALSE


3/8/2021
15:18:00
FALSE


3/8/2021
15:17:00
TRUE


3/8/2021
15:16:00
FALSE


3/8/2021
15:15:00
FALSE


3/8/2021
15:14:00
FALSE


3/8/2021
15:13:00
FALSE


3/8/2021
15:12:00
FALSE


3/8/2021
15:11:00
FALSE


3/8/2021
15:10:00
FALSE


3/8/2021
15:09:00
FALSE


3/8/2021
15:08:00
FALSE


3/8/2021
15:07:00
FALSE




Thanks!
WITH cte AS (
SELECT *, 
       SUM(keyPerformanceIndicator) OVER (ORDER BY `date`, `time` 
                                          ROWS BETWEEN 10 PRECEDING 
                                                   AND CURRENT ROW) is_true_within_10
FROM datatable
)
SELECT * FROM cte WHERE is_true_within_10

If BigQuery does not support implicit convertions then use SUM(CASE keyPerformanceIndicator WHEN TRUE THEN 1 ELSE 0 END) and WHERE is_true_within_10 &gt; 0.