Get count of series/groups based on 14 days of interval



  • I have a table containing range table like this.

    +---------+---------+
    | user_id | view_dt |
    +---------+---------+
    | A       |     1/1 |
    +---------+---------+
    | A       |    1/10 |
    +---------+---------+
    | A       |    1/14 |
    +---------+---------+
    | A       |    1/22 |
    +---------+---------+
    | A       |    1/23 |
    +---------+---------+
    | A       |    1/30 |
    +---------+---------+
    

    I want this data to be grouped based on a 14 day interval. That is, the groups would be:

    GROUP 1: 1/1, 1/10, 1/14

    GROUP 2: 1/22, 1/23, 1/30

    Basically I want to count the orders by interval. So Output should be:

    user_id: A          count of series = 2
    

    Note that I the 1/30 date should belong to GROUP 2 since 1/30 should be compared to the first date of GROUP 2 (1/22) instead of (1/1).

    I think it can be achieved by creating a recursive query because my grouping condition is 14 days from the first date of the group. Just curious if there's an alternative solution to the recursive CTE method?



  • WITH RECURSIVE 
    cte1 AS (
        SELECT *,
               ROW_NUMBER() OVER (PARTITION BY id ORDER BY dt) rn,
               COUNT(*) OVER (PARTITION BY id ORDER BY dt 
                              RANGE BETWEEN CURRENT ROW AND INTERVAL '15 day' FOLLOWING) cnt
        FROM test
        ORDER BY 1,2
    ),
    cte2 AS (
        SELECT *
        FROM cte1 
        WHERE rn = 1
        UNION ALL
        SELECT cte1.*
        FROM cte1
        JOIN cte2 ON cte1.id = cte2.id
                 AND cte1.rn = cte2.rn + cte2.cnt
    )
    SELECT id, COUNT(*)
    FROM cte2
    GROUP BY id;
    

    https://dbfiddle.uk/?rdbms=postgres_12&fiddle=874ecc698183349114594e6f01ece3fd




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2