Business Intelligence and Data Warehouse Architecture Questions

WITH user_days AS (
  -- derive the calendar date(s) for each session; here we count a session on the start date.
  -- If sessions can span multiple days and you want every day touched, you'd explode ranges.
  SELECT
    user_id,
    CAST(started_at AT TIME ZONE 'UTC' AS DATE) AS day
  FROM sessions
  WHERE started_at >= CURRENT_DATE - INTERVAL '15 days'  -- grab 15 days to compute 14 day-over-day diffs
    AND started_at < CURRENT_DATE + INTERVAL '1 day'
),
distinct_user_days AS (
  SELECT DISTINCT user_id, day
  FROM user_days
),
daily_counts AS (
  SELECT
    day,
    COUNT(DISTINCT user_id) AS dau
  FROM distinct_user_days
  GROUP BY day
)
SELECT
  day,
  dau,
  ROUND(100.0 * (dau - LAG(dau) OVER (ORDER BY day)) / NULLIF(LAG(dau) OVER (ORDER BY day),0), 2) AS pct_change_from_prev_day
FROM daily_counts
WHERE day >= CURRENT_DATE - INTERVAL '13 days'  -- last 14 days including today
ORDER BY day;