Window analytics

What you will be able to do

• Compute a running sum with SUM() OVER
• Use LAG to compare to the previous period
• Build a simple day-over-day change

Why this matters for data work

Trends need previous row context: yesterday’s revenue, prior order for a customer, cumulative spend. LAG/LEAD and running SUM are the toolkit.

Concepts

• SUM(x) OVER (PARTITION BY … ORDER BY … ROWS UNBOUNDED PRECEDING)
• LAG(col, 1) OVER (ORDER BY …) — previous row value.
• Frame clauses matter for running aggregates; default varies by function — be explicit when unsure.

Practice on your machine

export PGPASSWORD=dcubes PGHOST=127.0.0.1 PGPORT=15432 PGUSER=dcubes PGDATABASE=retail
psql << 'SQL'
WITH daily AS (
  SELECT order_ts::date AS d, round(sum(line_total), 2) AS revenue
  FROM orders
  WHERE status = 'completed'
  GROUP BY 1
)
SELECT
  d,
  revenue,
  lag(revenue) OVER (ORDER BY d) AS prev_day,
  round(revenue - lag(revenue) OVER (ORDER BY d), 2) AS dod_change,
  round(sum(revenue) OVER (ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) AS cumulative
FROM daily
ORDER BY d DESC
LIMIT 14;
SQL

What you should see

• One row per day with revenue.
• prev_day and dod_change (first day NULL prev).
• Growing cumulative when ordered ascending (check full sort).

Common mistakes

• LAG without ORDER BY — meaningless.
• Comparing unsorted timestamps.
• Running sum frame wrong — totals jump unexpectedly.

Next

CASE and set ops