Data and Trend Analysis with Pattern Recognition Questions

WITH params AS (
  SELECT
    (current_date - interval '2 years')::date AS start_date,
    current_date::date AS end_date
),
daily AS (
  -- aggregate raw transactions per day
  SELECT
    t_date::date AS date,
    SUM(amount) AS daily_total
  FROM transactions
  JOIN params ON true
  WHERE t_date::date BETWEEN params.start_date AND params.end_date
  GROUP BY t_date::date
),
calendar AS (
  -- continuous date series for last 2 years
  SELECT generate_series(start_date, end_date, interval '1 day')::date AS date
  FROM params
),
series AS (
  -- left join to ensure every date exists; missing days become zero
  SELECT
    c.date,
    COALESCE(d.daily_total, 0) AS daily_total
  FROM calendar c
  LEFT JOIN daily d USING (date)
),
ma AS (
  -- compute 7-day and 30-day moving averages (including current day)
  SELECT
    date,
    daily_total,
    ROUND(AVG(daily_total) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)::numeric, 2) AS 7_day_MA,
    ROUND(AVG(daily_total) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)::numeric, 2) AS 30_day_MA
  FROM series
),
changes AS (
  SELECT
    m.*,

    -- week-over-week pct change: compare to same weekday last week (date - 7 days)
    LAG(daily_total, 7) OVER (ORDER BY date) AS daily_last_week,
    NULLIF(LAG(daily_total, 7) OVER (ORDER BY date), 0) AS prev_week_nonzero,

    -- year-over-year: compare to same date last year
    LAG(daily_total, 365) OVER (ORDER BY date) AS daily_last_year
  FROM ma m
)
SELECT
  date,
  daily_total,
  7_day_MA,
  30_day_MA,

  -- WoW pct change using 7-day lag (same weekday last week). Handle division-by-zero and NULLs.
  CASE
    WHEN daily_last_week IS NULL THEN NULL
    WHEN daily_last_week = 0 THEN NULL
    ELSE ROUND( (daily_total - daily_last_week)::numeric / daily_last_week * 100, 2)
  END AS week_over_week_pct_change,

  -- YoY pct change using 365-day lag (same calendar date last year). NULL for missing prior-year date.
  CASE
    WHEN daily_last_year IS NULL THEN NULL
    WHEN daily_last_year = 0 THEN NULL
    ELSE ROUND( (daily_total - daily_last_year)::numeric / daily_last_year * 100, 2)
  END AS year_over_year_pct_change
FROM changes
ORDER BY date;

import pandas as pd, numpy as np
from statsmodels.tsa.seasonal import STL
from scipy.signal import welch
from statsmodels.graphics.tsaplots import plot_acf

# series: pd.Series indexed by hourly DatetimeIndex
s = np.log1p(series)
stl = STL(s, period=24)  # initial period for daily
res = stl.fit()
detrended = s - res.trend
f, Pxx = welch(detrended.dropna(), fs=1)  # fs=1 per hour
periods = 1 / f