Atom.

-- 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;