I have a table with example data like this:

I have a table with example data like this:

I'd like to use SQL in Snowflake to achieve this result:

Update: Some indications:

• For the month 2023-12 we have 1 quantity, so we started the month with 1 and we end the month with 1.
• For the month 2024-01 we started the month with 1 quantity (since the increase of 47 quantities was only made on the 2nd of January 2024) and ended the month of January with 1+47+24 = 72.
• For the months 2024-03 to 2024-11 there were no operations, so we always start each month with 72 and end it with 72.
• For the month 2024-12 we start with 72 (i.e. 72 at the first of the month) as there was an addition of 72 at 2024-12-22 and cancellation the next day -72 so we end the month with 72.
• For month 2025-01 we start with 72 and end the month with 72.

Thank you in advance

• sql
• snowflake-cloud-data-platform

• 1 Please update your question to explain how you’re generating the result table from your source data - what are the rules you are applying to that result in the rows you are showing? – NickW Commented Feb 7 at 17:42
• your output does not include any logic as @NickW mentioned so its difficult to work with assumption, also please show your attempt. – samhita Commented Feb 7 at 17:45
• Sorry, I modified my question by some indications. Thank you – allaouakh Commented Feb 7 at 19:07
• For the month of 2023-12, should the begin be 0 instead of 1? since at the end of 2023-11 and 1st of 2023-12 there is no entry. First entry for 2023-12 is not on 1st but on 2023-12-22? – samhita Commented Feb 7 at 20:55
• Yes 0 instead of 1 for 2023-12 – allaouakh Commented Feb 8 at 10:01

1 Answer 1

First we generate a row for each month.you can use a calendar table for this purpose, in this example I have used a custom generator to get a list of months for 15 months.

For each month, change in seat is calculated by summing all the seats for that month, note the months which does not have any seat will be assigned 0.

End for a month is calculated by summing seats for all previous months till that month for a REF(considering you would have multiple REFs).This is achieved by

  over (partition by ref order by month rows between unbounded preceding and current row) as end

Begin is the End value for the previous month, which is calculated by

lag(end, 1, 0) over (partition by ref order by month) as begin

Sample query

with distinct_refs as (
    select distinct ref
    from test_table
),
months as (
    select 
        d.ref,
        to_char(date_trunc('month', dateadd(month, seq4() - 1, '2024-01-01')), 'yyyy-mm') as month
    from 
        distinct_refs d
        cross join table(generator(rowcount => 15)) -- 15 months from 2023-11 to 2025-01
),
changes as (
    select 
        ref,
        date_trunc('month', start_date) as month,
        sum(seat) as seat_change
    from test_table
    group by ref, date_trunc('month', start_date)
),
monthly_seats as (
    select 
        m.ref, 
        m.month, 
        nvl(c.seat_change, 0) as seat_change
    from 
        months m
        left join changes c on m.ref = c.ref and m.month = to_char(c.month, 'yyyy-mm')
),
running_totals as (
    select 
        ref,
        month, 
        seat_change, 
        sum(seat_change) over (partition by ref order by month rows between unbounded preceding and current row) as end
    from monthly_seats
)
select 
    ref,
    month,
    lag(end, 1, 0) over (partition by ref order by month) as begin,
    end
from running_totals
order by ref, month;

Output