Languages

SQL for Data Analytics

Advanced SQL patterns for data analysis and reporting.

Driptanil DattaSoftware Developer

Mar 202514 min read

Page 3: Time Series Analysis — Tracking Trends Over Time 📈

Time is perhaps the most important dimension in any analytical model. Understanding how metrics evolve over days, months, and years is critical for detecting growth and seasonality.

Working with Dates and Timezones

Timezones are the hidden complexity of data analysis. Always normalize your data to a standard (like UTC) before performing math.

• Truncation: DATE_TRUNC('month', timestamp) to group by period.
• Extraction: EXTRACT(HOUR FROM timestamp) to find peak hours.

Simple Trends and Percent-of-Total

Tracking basic growth:

• Trend Calculation: Aggregating metrics by a time grain (D/W/M).
• Percent of Total: Using window functions SUM(val) OVER() to see how one period contributes to the whole.

Indexing for Percent Change

To track growth relative to a specific starting point:

(current_value - starting_value) / starting_value * 100

This allows you to normalize different metrics on a single index scale.

Rolling Time Windows

Moving averages help smoothen out volatility in data.

• Standard Windows: AVG(val) OVER(ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW).
• Sparse Data: Handling days where no events occurred to avoid biased averages.

Year-over-Year (YoY) and Month-over-Month (MoM)

Comparing the current period to the same period in the past is the standard way to measure performance while accounting for seasonal shifts.

LAG(metric, 1) OVER (ORDER BY date_month) -- MoM comparison
LAG(metric, 12) OVER (ORDER BY date_month) -- YoY comparison

Seasonality Analysis

Is your growth real, or just a holiday spike?

• Identifying Cycles: Detecting recurring patterns every 7 days (weekly) or 365 days (annual).
• Deseasonalization: Removing known seasonal effects to see the underlying trend.