Time Series Analysis in SQL: Mastering Moving Averages, YoY, and MoM Trends

Can't see the revenue trend? How to implement moving averages, YoY, and MoM comparisons in SQL.

TL;DR

• Time Series Analysis: Analyze data patterns over time
• Moving Averages: Remove noise, reveal trends
• YoY/MoM: Measure growth rates vs same period
• Window Functions: Implement with LAG, LEAD, AVG OVER

1. Why Time Series Analysis?

The Raw Data Problem

Jan 1: $100,000
Jan 2: $150,000
Jan 3: $80,000
Jan 4: $200,000

Question: Is revenue going up or down?

→ Daily fluctuations hide the trend!

Solutions

• Moving Average: Smooth out volatility
• YoY/MoM: Compare same periods
• Cumulative Sum: See overall flow

2. Basic Table Structure

-- Daily sales table
CREATE TABLE daily_sales (
    date DATE PRIMARY KEY,
    revenue DECIMAL(15, 2),
    orders INT,
    users INT
);

-- Or aggregate from transactions
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10, 2),
    created_at TIMESTAMP
);

3. Moving Averages

7-Day Moving Average

SELECT
    date,
    revenue,
    ROUND(AVG(revenue) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 2) as ma_7day,
    ROUND(AVG(revenue) OVER (
        ORDER BY date
        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ), 2) as ma_30day
FROM daily_sales
ORDER BY date;

Result:

Centered Moving Average

SELECT
    date,
    revenue,
    ROUND(AVG(revenue) OVER (
        ORDER BY date
        ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
    ), 2) as centered_ma_7
FROM daily_sales
ORDER BY date;

4. Day-over-Day / Week-over-Week

Daily Growth Rates

SELECT
    date,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY date) as prev_day,
    LAG(revenue, 7) OVER (ORDER BY date) as prev_week,
    ROUND((revenue - LAG(revenue, 1) OVER (ORDER BY date))
        / NULLIF(LAG(revenue, 1) OVER (ORDER BY date), 0) * 100, 2) as dod_growth,
    ROUND((revenue - LAG(revenue, 7) OVER (ORDER BY date))
        / NULLIF(LAG(revenue, 7) OVER (ORDER BY date), 0) * 100, 2) as wow_growth
FROM daily_sales
ORDER BY date;

Result:

5. Monthly Aggregation and MoM

Monthly Revenue with Month-over-Month

WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', date) as month,
        SUM(revenue) as revenue,
        SUM(orders) as orders
    FROM daily_sales
    GROUP BY DATE_TRUNC('month', date)
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) as prev_month,
    ROUND((revenue - LAG(revenue) OVER (ORDER BY month))
        / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100, 2) as mom_growth,
    SUM(revenue) OVER (ORDER BY month) as ytd_revenue
FROM monthly_sales
ORDER BY month;

Result:

6. Year-over-Year (YoY)

Same Month Last Year Comparison

WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', date) as month,
        EXTRACT(YEAR FROM date) as year,
        EXTRACT(MONTH FROM date) as month_num,
        SUM(revenue) as revenue
    FROM daily_sales
    GROUP BY DATE_TRUNC('month', date), EXTRACT(YEAR FROM date), EXTRACT(MONTH FROM date)
)
SELECT
    month,
    revenue,
    LAG(revenue, 12) OVER (ORDER BY month) as same_month_last_year,
    ROUND((revenue - LAG(revenue, 12) OVER (ORDER BY month))
        / NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0) * 100, 2) as yoy_growth
FROM monthly_sales
ORDER BY month;

YoY with JOIN (More Accurate)

WITH monthly_sales AS (
    SELECT
        EXTRACT(YEAR FROM date) as year,
        EXTRACT(MONTH FROM date) as month,
        SUM(revenue) as revenue
    FROM daily_sales
    GROUP BY EXTRACT(YEAR FROM date), EXTRACT(MONTH FROM date)
)
SELECT
    a.year,
    a.month,
    a.revenue as current_year,
    b.revenue as last_year,
    ROUND((a.revenue - b.revenue) / NULLIF(b.revenue, 0) * 100, 2) as yoy_growth
FROM monthly_sales a
LEFT JOIN monthly_sales b
    ON a.month = b.month
    AND a.year = b.year + 1
ORDER BY a.year, a.month;

7. Cumulative Totals and Achievement

Month-to-Date Achievement vs Target

WITH daily_with_target AS (
    SELECT
        date,
        revenue,
        DATE_TRUNC('month', date) as month,
        1000000 as monthly_target  -- Monthly target $1M
    FROM daily_sales
),
cumulative AS (
    SELECT
        date,
        revenue,
        month,
        monthly_target,
        SUM(revenue) OVER (
            PARTITION BY month
            ORDER BY date
        ) as mtd_revenue,
        EXTRACT(DAY FROM date) as day_of_month,
        EXTRACT(DAY FROM (month + INTERVAL '1 month' - INTERVAL '1 day')) as days_in_month
    FROM daily_with_target
)
SELECT
    date,
    revenue,
    mtd_revenue,
    monthly_target,
    ROUND(mtd_revenue * 100.0 / monthly_target, 2) as achievement_pct,
    -- Projected month-end revenue
    ROUND(mtd_revenue * days_in_month / day_of_month, 0) as projected_month_end
FROM cumulative
ORDER BY date;

8. Seasonality Analysis

Day-of-Week Patterns

SELECT
    EXTRACT(DOW FROM date) as day_of_week,
    TO_CHAR(date, 'Day') as day_name,
    ROUND(AVG(revenue), 0) as avg_revenue,
    ROUND(AVG(revenue) / SUM(AVG(revenue)) OVER () * 7 * 100, 2) as index_vs_avg
FROM daily_sales
GROUP BY EXTRACT(DOW FROM date), TO_CHAR(date, 'Day')
ORDER BY EXTRACT(DOW FROM date);

Result:

→ Friday is 131% of average, Sunday only 70%

Monthly Patterns

SELECT
    EXTRACT(MONTH FROM date) as month,
    TO_CHAR(date, 'Month') as month_name,
    ROUND(AVG(revenue), 0) as avg_daily_revenue,
    ROUND(AVG(revenue) / AVG(AVG(revenue)) OVER () * 100, 2) as seasonality_index
FROM daily_sales
GROUP BY EXTRACT(MONTH FROM date), TO_CHAR(date, 'Month')
ORDER BY EXTRACT(MONTH FROM date);

9. Trend Decomposition

Separate Trend from Noise with Moving Average

WITH trend_analysis AS (
    SELECT
        date,
        revenue,
        AVG(revenue) OVER (
            ORDER BY date
            ROWS BETWEEN 14 PRECEDING AND 14 FOLLOWING
        ) as trend,
        revenue - AVG(revenue) OVER (
            ORDER BY date
            ROWS BETWEEN 14 PRECEDING AND 14 FOLLOWING
        ) as residual
    FROM daily_sales
)
SELECT
    date,
    revenue,
    ROUND(trend, 0) as trend,
    ROUND(residual, 0) as residual,
    ROUND(residual * 100.0 / NULLIF(trend, 0), 2) as residual_pct
FROM trend_analysis
WHERE trend IS NOT NULL
ORDER BY date;

10. Compound Growth Analysis

CAGR (Compound Annual Growth Rate)

WITH yearly_sales AS (
    SELECT
        EXTRACT(YEAR FROM date) as year,
        SUM(revenue) as annual_revenue
    FROM daily_sales
    GROUP BY EXTRACT(YEAR FROM date)
),
cagr_calc AS (
    SELECT
        MIN(year) as start_year,
        MAX(year) as end_year,
        MIN(annual_revenue) as start_revenue,
        MAX(annual_revenue) as end_revenue,
        MAX(year) - MIN(year) as years
    FROM yearly_sales
)
SELECT
    start_year,
    end_year,
    start_revenue,
    end_revenue,
    years,
    ROUND((POWER(end_revenue * 1.0 / start_revenue, 1.0 / years) - 1) * 100, 2) as cagr_pct
FROM cagr_calc;

11. Forecasting and Target Setting

Trend-Based Projection

WITH recent_trend AS (
    SELECT
        date,
        revenue,
        -- Average daily growth over last 30 days
        AVG(revenue - LAG(revenue) OVER (ORDER BY date)) OVER (
            ORDER BY date
            ROWS BETWEEN 30 PRECEDING AND 1 PRECEDING
        ) as avg_daily_growth
    FROM daily_sales
)
SELECT
    date,
    revenue,
    ROUND(avg_daily_growth, 0) as avg_daily_growth,
    -- 7-day forecast
    ROUND(revenue + avg_daily_growth * 7, 0) as forecast_7d
FROM recent_trend
WHERE date = (SELECT MAX(date) FROM daily_sales);

12. Dashboard Query

Comprehensive Time Series Report

WITH daily_metrics AS (
    SELECT
        date,
        revenue,
        orders,
        revenue / NULLIF(orders, 0) as aov,
        LAG(revenue, 1) OVER (ORDER BY date) as prev_day_rev,
        LAG(revenue, 7) OVER (ORDER BY date) as prev_week_rev,
        LAG(revenue, 365) OVER (ORDER BY date) as prev_year_rev,
        AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as ma_7,
        AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) as ma_30
    FROM daily_sales
)
SELECT
    date,
    revenue,
    orders,
    ROUND(aov, 0) as aov,
    ROUND(ma_7, 0) as ma_7day,
    ROUND(ma_30, 0) as ma_30day,
    ROUND((revenue - prev_day_rev) / NULLIF(prev_day_rev, 0) * 100, 1) as dod_pct,
    ROUND((revenue - prev_week_rev) / NULLIF(prev_week_rev, 0) * 100, 1) as wow_pct,
    ROUND((revenue - prev_year_rev) / NULLIF(prev_year_rev, 0) * 100, 1) as yoy_pct,
    CASE
        WHEN revenue > ma_7 * 1.2 THEN 'Spike'
        WHEN revenue < ma_7 * 0.8 THEN 'Drop'
        ELSE 'Normal'
    END as anomaly_flag
FROM daily_metrics
ORDER BY date DESC
LIMIT 30;

Conclusion

Time Series Key Points:

• Moving averages remove noise
• Same-period comparison for fair growth rates
• Seasonality analysis improves forecast accuracy

References

1. Mode Analytics - Time Series Analysis in SQL
2. PostgreSQL Window Functions Documentation
3. Redshift - Date and Time Functions