Repeating the data after a particular time period

Question

I have the following input table:

Day	Item_Class	City	Cutoff	Transfer
1	Default	Kolkata	0	0
2	Campaign	Kolkata	0	0
3	Campaign2	Kolkata	0	0
1	Campaign	Jaipur	0	0
2	Default	Jaipur	0	0
3	Campaign2	Jaipur	0	0
1	Default	Bangalore	0	0
2	Big Pack	Bangalore	0	0
3	Campaign2	Bangalore	0	0
1	Default	Delhi	0	0
2	Campaign2	Delhi	0	0
3	Campaign2	Delhi	0	0

How do I get the same item_classes for a particular city for the subsequent days (day 4, 5, 6) through sql, it should repeat on its own after 3 days?

Sample output:

Day	Item_Class	City	Cutoff	Transfer
4	Default	Kolkata	0	0
5	Campaign	Kolkata	0	0
6	Campaign2	Kolkata	0	0
4	Campaign	Jaipur	0	0
5	Default	Jaipur	0	0
6	Campaign2	Jaipur	0	0
4	Default	Bangalore	0	0
5	Big Pack	Bangalore	0	0
6	Campaign2	Bangalore	0	0
4	Default	Delhi	0	0
5	Campaign2	Delhi	0	0
6	Campaign2	Delhi	0	0

Answer 1

You could do this multiple ways but a simple while loop to iterate through the records and increment the day by 3 for each loop works well. I implemented using a SnowScripting Anonymous Block: https://docs.snowflake.com/en/developer-guide/snowflake-scripting/blocks.html but this could be done by a stored proc with parameters.

First lets create the table and load the data

CREATE TABLE CITY_DATA (DAY NUMBER, ITEM_CLASS VARCHAR, City VARCHAR,   Cutoff NUMBER,  Transfer NUMBER);

INSERT INTO CITY_DATA VALUES
(1, 'Default',  'Kolkata',  0,  0),
(2, 'Campaign', 'Kolkata',  0,  0),
(3, 'Campaign2', 'Kolkata', 0,  0),
(1, 'Campaign', 'Jaipur',   0,  0),
(2, 'Default',  'Jaipur',   0,  0),
(3, 'Campaign2', 'Jaipur',  0,  0),
(1, 'Default',  'Bangalore',    0,  0),
(2, 'Big Pack', 'Bangalore',    0,  0),
(3, 'Campaign2', 'Bangalore',   0,  0),
(1, 'Default',  'Delhi',    0,  0),
(2, 'Campaign2', 'Delhi',   0,  0),
(3, 'Campaign2' ,'Delhi',   0,  0);

From there we will use this as our seed values to iterate over. Because we dont want to recreate the entire set of data each loop, we will need a high low filter from 1 to 3 on the initial run, then add 3 to it each loop. Initial SQL would look like this:

INSERT INTO CITY_DATA
SELECT DAY + 3, ITEM_CLASS, CITY, CUTOFF, TRANSFER FROM CITY_DATA
WHERE DAY BETWEEN 1 and 3;

Now we can put this in our looping construct and set the num_loops value to 10. This will generate 10 rows of data but this could be changed.

Here is the full procedure

execute immediate 
$$
declare
   num_loops number := 10; --set the number of loops you want to iterative and create data for 
   min_day number := 1; --filter for starting range from 1 to 3
   max_day number := 3; --filter for ending range from 1 to 3
   day_increase := 3; -- each day we want to add three new days 
   day_incrementer := 3; -- we need to keep track of what value we are currently on 
   insert_stmt varchar;
begin
    let counter := 1;
   
    while (counter < num_loops) do
    
    insert_stmt :=  
        'INSERT INTO CITY_DATA              
          SELECT  DAY +' ||  :day_increase || ', ITEM_CLASS, CITY, CUTOFF, TRANSFER FROM CITY_DATA          
          WHERE DAY BETWEEN ' || :min_day || ' and ' || max_day;
         execute immediate :insert_stmt;
         day_incrementer := day_incrementer + day_increase;
          min_day := min_day + day_increase;
          max_day := max_day + day_increase;
          counter := counter + 1;
            
    end while;
    return counter || ' number of batches inserted with ending value of: ' || :day_incrementer;
end;
$$;

output:

10 number of batches inserted with ending value of: 30

Answer 2

If you don't want to use Anonymous Block (EXECUTE IMMEDIATE) and WHILE loop, you can use Recursive CTE as an alternative:

insert into city_data
with recursive rep as (
    select *, 0 i
    from city_data
    where day between 1 and 3
    union all
    select r.day+3, r.item_class, r.city, r.cutoff, r.transfer, r.i+1 i
    from rep r
    where r.day+3 <= 30 and r.day between 1+i*3 and 3+i*3
)
select r.day, r.item_class, r.city, r.cutoff, r.transfer
from rep r
where not exists (
    select 1 from city_data c
    where c.day = r.day and c.city = r.city
);

In the above query, rep is a recursive CTE that generates the rows to be inserted.

The strategy is as below:

1. Fetch the "seed" rows from the city_data table (the part before UNION ALL)
2. Add a column i to count the number of iterations (0 for the seed rows)
3. In the next iteration, fetch the rows generated in the last iteration (or the seed rows for the first iteration)
   • It can be done by filtering the generated rows by using the day between 1+i*3 and 3+i*3 condition using the iteration count
     • 1-3 for the seed rows (i = 0)
     • 4-6 for the first iteration rows (i = 1)
     • 7-9 for the second iteration rows (i = 2)
     • ...
4. Generate new rows from the last iteration rows by replacing day with day+3
5. Limit the maximum day value to 30 to avoid infinite recursions
   • In this case, I limit the maximum value up to 30 for example, but you can change it as you want

Finally, you can insert the rows generated by the recursive CTE after removing the unnecessary i column, and removing the seed rows that are already in the target table by using the NOT EXISTS clause in the outer query.