Time as a Dimension

Why Time Matters

Time is the most common dimension in analytics:

• "How did sales change month-over-month?"
• "Which day of the week has the most activity?"
• "Is usage growing or declining?"

Extracting Time Components

In SQLite, use strftime() to extract parts of a date:

strftime('%Y', date)     -- Year: '2026'
strftime('%m', date)     -- Month: '01'
strftime('%w', date)     -- Day of week: '0' (Sunday) to '6'
strftime('%Y-%m', date)  -- Year-Month: '2026-01'

Example: Monthly Trend

SELECT strftime('%Y-%m', date) as month,
       SUM(total_revenue) as revenue
FROM daily_stats
GROUP BY month
ORDER BY month;

Your Task

Analyze daily_stats by day of the week:

• Show: day_of_week (0=Sunday to 6=Saturday), avg_sessions, avg_revenue
• Use strftime('%w', date) for day of week
• Round averages to 1 decimal
• Order by day_of_week