Master SQL Time Series Analysis (2026)

Quick Answer: What Is SQL Time Series Analysis?

The Dip That Wasn't Real

A dashboard chart of daily active users shows a jagged pattern — sharp dips every so often that look like outages or usage drops. The actual cause: SELECT DATE(created_at), COUNT(*) FROM events GROUP BY DATE(created_at) only produces a row for a date if at least one event happened that day. A day with genuinely zero events isn't a row with a zero — it's a day that never appears in the result at all. Most charting tools then draw a line straight from the last real data point to the next one, visually implying a gradual decline instead of showing what actually happened: nothing, on a day the report simply skipped.

This is one of the most common and most misleading bugs in time-based reporting, because the query itself never errors — it just quietly omits exactly the rows that would show "zero." Fixing it requires generating the full calendar independently of the data, not relying on the data to define which dates exist.

Date Truncation: Bucketing Timestamps

DATE_TRUNC rounds a timestamp down to the start of a specified unit — day, week, month, year — the standard way to bucket fine-grained timestamp data for aggregation.

-- PostgreSQL / BigQuery
SELECT
  DATE_TRUNC('month', order_date) AS month,
  SUM(order_total) AS monthly_revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- MySQL equivalent for month-level truncation
SELECT
  DATE_FORMAT(order_date, '%Y-%m-01') AS month,
  SUM(order_total) AS monthly_revenue
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m-01')
ORDER BY month;

Grouping directly on a raw timestamp column almost never does what's intended, since two events a second apart still have different exact timestamps and end up in separate groups. DATE_TRUNC (or its MySQL equivalent) is what actually collapses "every event this month" into one bucket. PostgreSQL's own documentation describes DATE_TRUNC as rounding a timestamp down to the nearest specified precision, which is the exact behavior every bucketing technique in this article relies on.

Filling Gaps With a Complete Date Series

This is the actual fix for the hook at the top of this article: generate every date in the range independently, then left join the real data onto it.

-- PostgreSQL: generate_series produces a continuous range of dates
WITH date_series AS (
  SELECT generate_series('2026-01-01'::date, '2026-01-31'::date, '1 day') AS day
)
SELECT
  ds.day,
  COALESCE(COUNT(e.event_id), 0) AS daily_active_users
FROM date_series ds
LEFT JOIN events e ON DATE(e.created_at) = ds.day
GROUP BY ds.day
ORDER BY ds.day;

The date series exists on its own terms, independent of whatever the events table happens to contain. LEFT JOIN keeps every date from that series regardless of matches — the exact pattern from the JOINs article earlier in this cluster. COALESCE then turns the resulting NULL count into an honest 0 for days with no activity.

No generate_series? Here's the MySQL workaround

MySQL has no built-in equivalent to generate_series. The standard workaround is a recursive CTE — the same technique introduced in the subqueries vs CTEs article — or a pre-built date dimension table kept in the database specifically for this purpose.

Moving Averages

A moving average smooths day-to-day noise — window functions with an explicit frame clause are the standard way to compute one in SQL.

-- 7-day moving average of daily revenue
SELECT
  order_date,
  daily_revenue,
  AVG(daily_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS revenue_7day_avg
FROM daily_revenue_summary;

The frame clause — ROWS BETWEEN 6 PRECEDING AND CURRENT ROW — explicitly defines the window. It's the current row plus the six before it, seven rows total, matching a 7-day average. This is the same OVER syntax from the window functions article, applied with an explicit frame instead of the default.

Why 7 days specifically?

A 7-day window is the most common choice in reporting because it naturally smooths out weekly patterns, like lower weekend traffic, without needing a separate day-of-week adjustment.

Period-Over-Period Comparison

Comparing a period to the previous one — month-over-month, year-over-year — is a direct application of LAG on already-aggregated data.

WITH monthly AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(order_total) AS revenue
  FROM orders
  GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
  ROUND(
    (revenue - LAG(revenue) OVER (ORDER BY month)) /
    LAG(revenue) OVER (ORDER BY month) * 100, 1
  ) AS pct_change
FROM monthly
ORDER BY month;

Aggregating to monthly totals first, in a CTE, keeps the LAG calculation clean — it's comparing one row to the row directly before it in an already-summarized, one-row-per-month result, rather than trying to reach across raw transaction-level data. The same pattern works for year-over-year by truncating to year instead of month, or by using LAG(revenue, 12) on monthly data to reach back exactly 12 rows.

Gaps and Islands: Finding Consecutive Streaks

"Gaps and islands" refers to finding unbroken consecutive runs of rows — like a customer's current login streak — separated by real gaps. The classic solution uses a subtraction trick.

WITH numbered AS (
  SELECT
    customer_id,
    login_date,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY login_date) AS rn,
    login_date - (ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY login_date) * INTERVAL '1 day') AS streak_group
  FROM logins
)
SELECT
  customer_id,
  MIN(login_date) AS streak_start,
  MAX(login_date) AS streak_end,
  COUNT(*) AS streak_length
FROM numbered
GROUP BY customer_id, streak_group
ORDER BY customer_id, streak_start;

Why the subtraction trick works

For consecutive dates, subtracting an ever-increasing row number from the date produces the exact same result for every row in that unbroken run. That shared constant acts as an implicit group ID.

The moment there's a real gap in the dates, the row number keeps climbing but the date jumps ahead further — breaking the constant and starting a new "island." Grouping by that computed value is what turns individual login rows into distinct streaks.

A Timezone Reminder

Cross-Database Date Functions

MySQL's lack of a built-in date-series generator is the single biggest practical gap covered in this article — every other technique translates fairly directly, but filling gaps in MySQL specifically requires either a recursive CTE or a pre-populated calendar/date dimension table kept in the database for exactly this purpose.

Real-World Applications of SQL Time Series Analysis

These techniques aren't academic — they show up in nearly every recurring business report:

• Product analytics. Daily/weekly active user counts, retention curves, and feature adoption trends all depend on a complete, gap-filled date series.
• Finance and revenue reporting. Month-over-month and year-over-year revenue comparisons use the exact LAG pattern covered above.
• Operations and reliability. Gaps-and-islands identifies uptime streaks, consecutive failed jobs, or how long a system stayed in a given state.
• Marketing. Moving averages smooth noisy daily ad spend or conversion data into a trend a stakeholder can actually read.

Whichever domain it's applied to, SQL time series analysis follows the same underlying shape: bucket the timestamps, don't let missing dates lie by omission, and layer window functions on top for trend and comparison.

Mistakes That Give Away a Beginner

• Trusting a GROUP BY date report to include every date. The exact trap from this article's opening — missing dates are missing rows, not zero rows.
• Grouping on a raw timestamp instead of a truncated one, silently splitting what should be one bucket into many.
• Computing a moving average without an explicit frame clause and getting the database's default behavior instead of the intended window size.
• Comparing periods on raw transaction data instead of pre-aggregated rows, making the LAG comparison unnecessarily complex or wrong.
• Ignoring time zone handling on a report that spans users in multiple regions, misattributing events to the wrong day.

Practice: Write These Three Queries Yourself

Using an events (event_id, customer_id, created_at) table, try writing these:

Frequently Asked Questions

Conclusion: The Calendar Doesn't Come From the Data

The single idea underlying most of this article: the set of dates a report should cover has to come from somewhere independent of the data itself — a generated series, a recursive CTE, or a date dimension table — never from GROUP BY alone, which only ever shows you the dates that already had something happen. Beyond that, time series work in SQL is mostly the same toolkit from earlier in this cluster — window functions, JOINs, CTEs — aimed specifically at date-ordered data.

Practice the three queries above against real event data — that closes out the advanced tier: window functions, query optimization, SQL vs NoSQL, and time series. The next article starts the applied and career tier with SQL interview questions.