A self-contained SQL playground to make sense of Postgres window functions. Thanks
to the now, kind of legendary, Mitchell’s Advent of Code series.
-- window playground
DROP SCHEMA IF EXISTS wpg CASCADE;
CREATE SCHEMA wpg;
-- Create table with a created_at column
CREATE TABLE wpg.random_data (
id SERIAL PRIMARY KEY,
name VARCHAR(50),
active_users INT,
email VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Seed random data with one row for each day in the last 30 days
INSERT INTO wpg.random_data (name, active_users, email, created_at)
SELECT
md5(random()::text) as name,
floor(random() * 100) as active_users,
substr(md5(random()::text), 0, 10) || '@example.com' as email,
current_date - s.a as created_at
FROM generate_series(0, 29) as s(a);
SELECT * from wpg.random_data;
-- compare daily
WITH lagdata AS (
SELECT created_at,
active_users,
lag(active_users) over (ORDER BY created_at) as prev
FROM wpg.random_data
ORDER BY created_at
)
SELECT *
FROM lagdata;
-- compare weekly ()
WITH lagdata AS (
SELECT date_trunc('week', created_at) as week_start,
sum(active_users) as total_active_users
FROM wpg.random_data
GROUP BY week_start
ORDER BY week_start
)
SELECT *
FROM lagdata;
-- but include the previous week's users
WITH weekly_data AS (
SELECT date_trunc('week', created_at) as week_start,
sum(active_users) as total_active_users
FROM wpg.random_data
GROUP BY week_start
ORDER BY week_start
),
lagdata AS (
SELECT
week_start,
total_active_users,
LAG(total_active_users) OVER (ORDER BY week_start) as prev_week_active_users
FROM weekly_data
)
SELECT *
FROM lagdata;