Counting number of rows up to a specified value by an id

Question

Hello,

I have a dataset with id, test date, test_type, test result.

ID     Test_Date     Test_Type     Test_Result

1       2024-03-21    A                     Fail

1      2024-04-21     A                     Fail

1      2024-04-30    A                     Pass

1      2025-05-15     B                      Fail

1     2025-05-31      B                      Pass

I need to create a variable counting number of attempts to pass for each Test_Type. So the desired data would be:

ID     Test_Date     Test_Type     Test_Result   Attempts

1       2024-03-21    A                     Fail                    3

1      2024-04-21     A                     Fail                    3

1      2024-04-30    A                     Pass                  3

1      2025-05-15     B                      Fail                   2

1     2025-05-31      B                      Pass                 2

Could someone help with a SQL code?

Thanks in advance!

Max

Riyaz Ali-Oracle · Answer

Hi @User_KNFCO ,

Please try the below:

SELECT

ID,

Test_Date,

Test_Type,

Test_Result,

COUNT(*) OVER (

PARTITION BY ID, Test_Type

ORDER BY Test_Date

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

) AS Attempts

FROM (

SELECT *,

MIN(CASE WHEN Test_Result = 'Pass' THEN Test_Date END)

OVER (PARTITION BY ID, Test_Type) AS First_Pass_Date

FROM your_table

) sub

WHERE Test_Date <= First_Pass_Date

ORDER BY ID, Test_Type, Test_Date;

Thanks,

Riyaz Ali