PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Streak for a given endDate SQL (Postgres)

enter image description here

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

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

Tags:

2

Answers


  1. 0

    Most simple is to use recursivity :

    WITH RECURSIVE streak AS (
    SELECT 
        d.date,
        d.number,
        1 AS streak_counter
    FROM data d
    WHERE d.date = '2024-11-07'.  --> Your date here

    UNION ALL

    SELECT 
        d.date,
        d.number,
        streak.streak_counter + 1
    FROM data d
    INNER JOIN streak ON d.date = streak.date - INTERVAL '1 day'
    WHERE d.number >= 1000
)
SELECT MAX(streak_counter) FROM streak;
    Login or Signup to reply.
  2. 0
    • 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.

    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
    t.date,
    COALESCE(cd.streak_length, 0) AS streak_length
FROM test t
LEFT JOIN consecutive_dates cd ON t.date = cd.date
ORDER BY t.date;

    Output

    date streak_length
    2024-11-02 1
    2024-11-03 0
    2024-11-05 1
    2024-11-06 2
    2024-11-07 3
    2024-11-08 0
    2024-11-14 1
    2024-11-15 2
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search