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

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

• sql
• snowflake-cloud-data-platform

1 Answer 1

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