SQL Date and Time — Applied Week Starts, Sessions, Averages

SELECT
  sub_id,
  plan,
  GREATEST(started_on, DATE '2026-05-01') AS billed_from,   -- later of month start and contract start
  LEAST(COALESCE(ended_on, DATE '2026-06-01'), DATE '2026-06-01') AS billed_to,  -- active means "runs to next month"
  LEAST(COALESCE(ended_on, DATE '2026-06-01'), DATE '2026-06-01')
    - GREATEST(started_on, DATE '2026-05-01') AS billed_days   -- half-open, so the difference is the day count
FROM   subscriptions
WHERE  started_on < DATE '2026-06-01'                          -- drop contracts starting after May
  AND  COALESCE(ended_on, DATE '2026-06-01') > DATE '2026-05-01'  -- drop contracts ending before May
ORDER BY sub_id;

/*
  Execution order (logical evaluation order):
  1. FROM subscriptions           → Read 5 rows
  2. WHERE overlaps May           → Narrow to 4 rows
  3. SELECT GREATEST / LEAST      → Fix both ends of the overlap
  4. SELECT end - start           → Compute billable days
  5. ORDER BY sub_id              → Ascending by contract id
  */

WITH marked AS (
  SELECT
    event_id, user_id, occurred_at,
    CASE WHEN occurred_at - LAG(occurred_at) OVER (PARTITION BY user_id ORDER BY occurred_at)
              >= INTERVAL '30 minutes'
         THEN 1 ELSE 0 END AS is_new   -- flag the boundaries
  FROM app_events
), numbered AS (
  SELECT
    user_id, occurred_at,
    SUM(is_new) OVER (PARTITION BY user_id ORDER BY occurred_at) + 1 AS session_no  -- running total of flags
  FROM marked
)
SELECT
  user_id,
  session_no,
  MIN(occurred_at) AS started_at,
  MAX(occurred_at) AS ended_at,
  COUNT(*)          AS events
FROM     numbered
GROUP BY user_id, session_no
ORDER BY user_id, session_no;

/*
  Execution order (logical evaluation order):
  1. FROM app_events              → Read 6 rows
  2. LAG for the previous time    → Compute gaps and raise flags
  3. SUM(...) OVER running total  → Fix the session number
  4. GROUP BY user_id, session_no → Collapse to one row per session
  5. ORDER BY user_id, session_no → Ascending by user and number
  */

SELECT
  sold_on,
  amount,
  ROUND(AVG(amount) OVER (
          ORDER BY sold_on
          RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW), 1) AS avg_7d  -- seven days by date
FROM   daily_sales
ORDER BY sold_on;

/*
  Execution order (logical evaluation order):
  1. FROM daily_sales             → Read 6 rows
  2. OVER (ORDER BY sold_on)      → Order by date
  3. RANGE INTERVAL '6 days'      → Fix a seven-day window per row
  4. AVG + ROUND                  → Average the window to one decimal
  5. ORDER BY sold_on             → Ascending by date
  */

SELECT
  d.day::date AS day,
  p.price
FROM generate_series(
       DATE '2026-07-01',
       DATE '2026-07-05',
       INTERVAL '1 day') AS d(day)          -- spine of dates (5 rows)
LEFT JOIN LATERAL (
  SELECT c.price
  FROM   price_changes c
  WHERE  c.changed_on <= d.day::date            -- only changes up to that day
  ORDER BY c.changed_on DESC
  LIMIT  1) p ON TRUE                        -- take the most recent one
ORDER BY day;

/*
  Execution order (logical evaluation order):
  1. generate_series(...)         → Generate 5 days
  2. LATERAL subquery per row     → Latest change on or before that day
  3. LEFT JOIN ... ON TRUE        → Attach the price to the day
  4. SELECT day, price            → Select 2 columns
  5. ORDER BY day                 → Ascending by date
  */