sqlsnowflake-cloud-data-platform

Complex pattern counting that adds to the count on rows 1,2,3,4,5 but not on row 6,7


Here is the code I have done so far:

WITH RECURSIVE numbers(pk) AS (
  SELECT 1
  UNION ALL
  SELECT pk + 1 FROM numbers WHERE pk < 27
)
SELECT pk, (row_number() over (order by pk asc) -1) %7 +1 as i,
    (case when pk%7 =5 then pk
     when pk%7 =6 then pk-1
    when pk%7 = 0 then pk-2 else pk end) as p 
 FROM numbers;

so I want to start counting on rows 1,2,3,4,5 and stop the count on row 6,7, (column i in the above code gives the row numbers)

Example: meaning row 8 will have count = 6 instead of 8, and so on but every 6,7th row will have the same value of the count as shown in the expected results.

Expected Results: P is the pattern I want to generate, i is the row numbers

pk i p
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 5
7 7 5
8 1 6
9 2 7
10 3 8
11 4 9
12 5 10
13 6 10
14 7 10
15 1 11
16 2 12
17 3 13
18 4 14
19 5 15
20 6 15
21 7 15
22 1 16
23 2 17
24 3 18
25 4 19
26 5 20
27 6 20

Solution

  • Based on comment that i and p should not be part of recursive CTE. First i is calculated based on PK and then P is calculated based on i, which only increments p when i not in 6,7.

    WITH RECURSIVE seq AS (
        SELECT 1 AS pk
        UNION ALL
        SELECT pk + 1
        FROM seq
        WHERE pk < 27
    ),
    i_calc AS (
        SELECT seq.pk,
               CASE 
                   WHEN MOD(seq.pk - 1, 7) = 0 THEN 1 
                   ELSE MOD(seq.pk - 1, 7) + 1 
               END AS i
        FROM seq
    ),
    p_calc AS (
        SELECT i_calc.pk,
               i_calc.i,
               SUM(CASE WHEN i_calc.i IN ( 6, 7) THEN 0 ELSE 1 END) OVER (ORDER BY i_calc.pk) AS p
        FROM i_calc
    )
    SELECT pk, i, p
    FROM p_calc;
    

    Output

    PK I P
    1 1 1
    2 2 2
    3 3 3
    4 4 4
    5 5 5
    6 6 5
    7 7 5
    8 1 6
    9 2 7
    10 3 8
    11 4 9
    12 5 10
    13 6 10
    14 7 10
    15 1 11
    16 2 12
    17 3 13
    18 4 14
    19 5 15
    20 6 15
    21 7 15
    22 1 16
    23 2 17
    24 3 18
    25 4 19
    26 5 20
    27 6 20