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

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

-- 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
);

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;

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;

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;

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;

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;

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;

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;

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);

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);

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;

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;

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);

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;