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.
Adapted from Mastering System Design from Basics to Cracking Interviews (Udemy) — https://www.udemy.com/course/mastering-system-design-from-basics-to-cracking-interviews/
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 * 100This 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 comparisonSeasonality 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.
Always be careful with "partial periods" (e.g., comparing a full month of February to a half-finished March).