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.

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

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.

Conclusion

SQL isn't just for fetching data; it's for analyzing it. By pushing calculations like Moving Averages and Growth Rates into the database layer, you make your reporting faster and your application code cleaner.