Streak for a given endDate SQL (Postgres)

Input data

for a given date I want to get the streak (dates backward in a row where number is above/equals a certain threshold => in the examples always 1000)

Examples:

• date input 2024-11-15 => Result 2
• date input 2024-11-14 => Result 1
• date input 2024-11-8 => Result 0
• date input 2024-11-7 => Result 3
• date input 2024-11-5 => Result 1

If I search using a date which doesn't appear in the data (for example, 2014-11-12), I want the result to be 0.

I would greatly appreciate your help. Thank you so much!

I have another example:

Input data

query for date 2024-12-14 should return 2 (count 14. 13.) There is a gap between 13. and 10. So the 10. should not count.

• Streak CTE will assign a row_number to all the dates where number is >= 1000.
• Then streak is SELF JOINED to find the dates which are consecutive and the number of days are counted based on difference of row numbers.

EDIT

• since single output is expected, one additional LEFT JOIN is added to return the streak when the date is present, otherwise return 0.

Fiddle

WITH streaks AS (
    SELECT 
        t.date,
        t.number,
        ROW_NUMBER() OVER (ORDER BY t.date) AS rn
    FROM test t
    WHERE t.number >= 1000
),
consecutive_dates AS (
    SELECT
        s1.date,
        COUNT(*) AS streak_length
    FROM streaks s1
    LEFT JOIN streaks s2 
        ON s2.rn <= s1.rn AND s2.date = s1.date - INTERVAL '1 day' * (s1.rn - s2.rn)
    GROUP BY s1.date
)
SELECT
    i.date,
    COALESCE(cd.streak_length, 0) AS streak_length
FROM (SELECT '2024-11-13'::DATE AS date) i 
LEFT JOIN consecutive_dates cd ON i.date = cd.date;

Output

EDIT : DATE contains timestamp, we just use the date portion from the timestamp.

Fiddle

WITH streaks AS (
    SELECT 
        t.date::DATE AS date,  
        t.number,
        ROW_NUMBER() OVER (ORDER BY t.date::DATE) AS rn  
    FROM test1 t
    WHERE t.number >= 1000
),
consecutive_dates AS (
    SELECT
        s1.date,
        COUNT(*) AS streak_length
    FROM streaks s1
    LEFT JOIN streaks s2 
        ON s2.rn <= s1.rn 
        AND s2.date = s1.date - INTERVAL '1 day' * (s1.rn - s2.rn)
    GROUP BY s1.date
)
SELECT
    i.date,
    COALESCE(cd.streak_length, 0) AS streak_length
FROM (SELECT '2024-11-15'::DATE AS date) i 
LEFT JOIN consecutive_dates cd ON i.date = cd.date;