Real Business Question: Temporal Velocity
In business analytics, a static number like "We made $500,000 in revenue" is meaningless without time context. Did that $500,000 arrive over 3 days, 6 months, or 2 years? Is revenue accelerating, plateauing, or decaying?
2026-01-15 14:23:45). Before analysis, analysts normalize granular timestamps into standard reporting buckets: Days, Weeks, Months, Quarters, or Years.Time-Based Grouping: Macro vs. Micro Windows
Timestamp
Exact transaction second
Daily
Operational tracking
Monthly (MoM)
Core business reporting
Yearly (YoY)
Long-term strategic trend
Monthly Sales & Date Extraction
SUBSTRING(order_date, 1, 7) AS month,
COUNT(*) AS orders,
SUM(sales) AS monthly_revenue
FROM orders
GROUP BY month
ORDER BY month ASC;
Month-over-Month (MoM) Growth with LAG()
SELECT
SUBSTRING(order_date, 1, 7) AS month,
SUM(sales) AS revenue
FROM orders
GROUP BY month
)
SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
revenue - LAG(revenue) OVER (ORDER BY month) AS dollar_change,
ROUND(((revenue - LAG(revenue) OVER (ORDER BY month)) / NULLIF(LAG(revenue) OVER (ORDER BY month), 0)) * 100, 2) || '%' AS percentage_growth
FROM monthly_sales
ORDER BY month ASC;
Year-over-Year (YoY) Seasonality Controls
Why do analysts prioritize YoY comparisons? In retail, comparing January against December usually shows a steep decline because December has Christmas & holiday surges. Comparing January 2026 vs. January 2025 eliminates seasonal noise and reveals true year-over-year organic growth.
Missing Periods & Continuous Timelines
GROUP BY month query will simply omit February entirely! The rows will jump from January directly to March. In production reporting, analysts join against a calendar date dimension table to keep continuous zeroes.Interpreting Trends Like an Analytics Pro
Consistently higher monthly peaks and troughs over consecutive quarters.
Decaying transaction volume, lower AOV, or declining YoY cohorts.
Predictable spikes recurring on specific weekdays or annual quarters.
Calculate total orders and total sales revenue for each month. Format month as "YYYY-MM" and sort chronologically.