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

Max503

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

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