Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save thedatadavis/38fc344b190740d7e5264fb6e899a373 to your computer and use it in GitHub Desktop.
Save thedatadavis/38fc344b190740d7e5264fb6e899a373 to your computer and use it in GitHub Desktop.
Example of using `generate_series` in PostgreSQL to calculate a running sum for each customer
-- Run query here: https://bit.io/christoff12/explore_stuff?view=docs#/retail
with clean_data as (
select
customerid as customer_id,
invoiceno as invoice_id,
stockcode as stock_code,
quantity,
unitprice as unit_price,
to_timestamp(invoicedate, 'MM/DD/YYYY HH24:MI') as invoiced_at,
date_trunc('week', to_timestamp(invoicedate, 'MM/DD/YYYY HH24:MI'))::date as order_week
from "retail"
),
step1 as (
select
customer_id,
min(order_week) as min_order_week,
max(order_week) as max_order_week
from clean_data
where customer_id is not null
group by 1
),
step2 as (
-- Creates a date spine for overall min, max range
select
generate_series(date_range.min_date,
date_range.max_date,
'1 week'::interval)::date as week
from (select
min(min_order_week) as min_date,
max(max_order_week) as max_date
from step1) as date_range
),
step3 as (
-- Returns a row for every week in min, max range per customer_id
select
step1.*,
week
from step1,
lateral (select * from step2 where (week between min_order_week and max_order_week)) as week_fill
),
step4 as (
select
customer_id,
order_week,
coalesce(sum(case when invoice_id not like 'C%' then quantity end), 0) as pos_qty,
coalesce(sum(case when invoice_id like 'C%' then quantity end), 0) as neg_qty
from clean_data
group by 1, 2
)
select
step3.customer_id,
step3.week,
sum(pos_qty + neg_qty) over (partition by step3.customer_id
order by step3.week asc
rows between unbounded preceding and current row)
from step3
left join step4 on step3.customer_id = step4.customer_id
and step3.week = step4.order_week
where step3.customer_id = 16592 -- example (should return 42 rows)
order by step3.customer_id, step3.week
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment