Time Series Analysis with SQL: Trends, Growth, and Moving Averages

If you work with data, you work with time. Sales happen over time. Users sign up over time. Servers crash over time.

But raw time-series data is often messy and noisy. A daily sales chart might look like a jagged mountain range. To spot the real trends, you need to smooth it out. Today, we'll build a complete analytics report using SQL.

A Real Business Scenario: Weekly Reporting for a Product Team

Time-series analysis is what turns a stream of events into something a team can act on.

Imagine a product team reviewing weekly performance:

• signups are growing, but weekends always dip
• revenue spikes on campaign days
• support tickets surge after each release
• the current month is only partially complete

If the query does not account for grain, missing periods, and partial windows, the chart may look sophisticated while the interpretation is wrong. Time-series SQL is less about one clever function and more about building the right sequence of steps.

1. The 7-Day Moving Average

Why use a moving average? Because Monday sales might always be low and Friday sales high. Looking at a single day is misleading. A Rolling 7-Day Average smooths out these weekly fluctuations.

We use Window Functions for this:

AVG(sales) OVER (
  ORDER BY date 
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)

2. Month-over-Month (MoM) Growth

Growth is the heartbeat of a startup. To calculate it, we need to compare this month's value with last month's value.

We use the LAG() function to look backwards:

LAG(revenue, 1) OVER (ORDER BY month) as previous_month_revenue

A Common Mistake: Comparing Buckets That Are Not Actually Comparable

This is one of the most common failures in trend reporting. The SQL runs, the chart renders, and the conclusion is still wrong because the buckets do not represent equivalent periods.

Examples:

• comparing a partial current month with a full previous month
• aggregating UTC timestamps when the business reports in a local timezone
• mixing event timestamps and order-settlement timestamps in the same trend line
• smoothing a sparse series without gap filling first

Time-series work is fragile because small bucketing mistakes propagate into every downstream growth rate and rolling average.

Interactive Demo: Analytics Dashboard

Let's combine these concepts. We have a daily_signups table interactively generated below. We will calculate a rolling average and growth rate.

Handling Missing Dates

Real data often has holes. No sales on Christmas? The row just doesn't exist. This breaks moving average calculations (since "3 rows back" might mean 3 weeks back, not 3 days).

The solution is to Gap Fill using a Recursive CTE (which we covered in another article!).

1. Generate a series of all dates.
2. LEFT JOIN your actual data to this series.
3. Use COALESCE(sales, 0) to turn NULLs into zeros.

The Difference Between Trend and Noise

Time-series SQL is usually not about computing one fancy function. It is about separating the signal from the daily volatility.

That is why analysts reach for:

• moving averages
• lag comparisons
• month-over-month or week-over-week growth
• gap filling
• period bucketing

Each of those answers a different question. A growth rate explains change. A moving average explains trend. Gap filling explains continuity. Mixing them together without naming the question leads to reports that look polished but are hard to interpret.

A Practical Workflow for Time-Series Reporting

Use this sequence when building a trend query:

1. Normalize timestamps to the correct grain first.
2. Fill missing periods if continuity matters.
3. Aggregate to one row per time bucket.
4. Add lag-based comparisons.
5. Add smoothing only after the raw trend is already correct.

That order matters because smoothing a broken or incomplete series just hides the bug.

Common Time-Series Mistakes

Mistake 1: Comparing incomplete current periods

If the current month has only three days of data and last month is complete, a raw month-over-month comparison is misleading.

Mistake 2: Confusing row windows with time windows

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW means "last 7 rows", not necessarily "last 7 calendar days." If dates are missing, those are very different things.

Mistake 3: Ignoring seasonality

Day-of-week effects, month-end spikes, and campaign bursts can distort simple trend interpretations. SQL can calculate the metric, but the analysis still needs judgment.

Boundary and Performance Notes

Time-series queries often look concise, but production-scale reporting adds a few practical constraints:

• truncating timestamps on the fly can be expensive unless the table is partitioned or indexed in a compatible way
• window functions often require a full sort within each partition
• gap-filling queries can expand row counts quickly for long date ranges
• one reporting table may need several grains, such as hourly, daily, and monthly, instead of recalculating everything from raw events every time

That is why many teams eventually maintain date dimensions, summary tables, or materialized aggregates for repeated time-series reporting.

When NOT to Use a Simple Trend Query

Avoid relying on a basic time-series summary when:

• the real question is cohort retention rather than raw activity over time
• the business process has irregular event timing and needs session logic or attribution rules
• structural breaks like pricing changes or product launches make before/after comparisons non-trivial
• stakeholders need forecasting rather than historical description

In those cases, a plain lag or moving-average report is useful context, but not the whole answer.

Official References

• PostgreSQL date/time functions for period bucketing and timestamp handling.
• PostgreSQL window function tutorial for ordered analytical calculations like lag and moving averages.
• SQLite date and time functions for SQLite-specific time bucketing behavior.

Related SQL Challenges

If you want to turn the same time-order reasoning into smaller practice reps, start here:

• Rising Temperature - A direct day-over-day comparison problem that trains date offsets and previous-row reasoning.
• Rank Scores - Useful when you want to reinforce the window-function side of ordered analytical SQL.

Related Articles

• SQL Window Frames: ROWS vs RANGE for the framing rules behind moving averages and rolling metrics.
• Cohort Analysis with SQL for another way to study behavior over time when the unit is a user group instead of a raw time bucket.
• Building Conversion Funnels in SQL for step-based journey analysis when the question is progression rather than trend.

Conclusion

SQL is strong at time-series analysis because it can aggregate, compare periods, and smooth trends close to the raw data. The difficult part is not writing LAG() or AVG() OVER (...). It is making sure the time buckets, missing periods, and comparison windows match the business question.