SQL for Product Managers
Time-Based Analysis: Dates, Trends, and Comparisons
Almost every product question has a time dimension hiding inside it, even ones that don't sound like it. "Is this good" almost always secretly means "compared to when."
The workhorse function here is DATE_TRUNC, which rounds a timestamp down to the start of a chosen period, letting you group by day, week, month, or year cleanly.
SELECT
DATE_TRUNC('week', order_date) AS week,
COUNT(*) AS num_orders,
SUM(amount) AS revenue
FROM orders
GROUP BY 1
ORDER BY 1;
This gives you a clean weekly trend line, one row per week, ready to chart. Note the GROUP BY 1 and ORDER BY 1 shorthand, referring to the first column in the SELECT list by position rather than retyping the full expression. It's common in real-world SQL and worth recognizing even before you use it yourself.
Common time-based comparisons you'll be asked for constantly:
Question | Pattern |
|---|---|
Week over week change | Two DATE_TRUNC('week', ...) groups, compared |
Same period last year | Filter both the current range and the equivalent range a year prior, then compare |
Rolling 7-day or 30-day average | A window function, covered in Lesson 5.1 |
Before vs. after a launch date | A CASE WHEN splitting rows into "before" and "after" buckets, then aggregating each |
THE COMPARISON THAT LOOKS FAIR AND ISN'T
Comparing "this week" to "last week" sounds neutral, but if your product has a strong day-of-week pattern (B2B tools are often quiet on weekends, consumer apps often spike on weekends), a partial current week compared to a complete prior week will mislead you every time. Compare like periods: full week to full week, and be explicit in anything you share about whether the most recent period is complete or still accumulating data.
YOUR MOVE THIS WEEK
Write a query that shows your core metric, however you define it, by week for the last eight weeks. Look at it before you look at any existing dashboard version of the same thing. Do the numbers match? If not, you've just found a real discrepancy worth investigating, which is exactly the kind of finding this course is meant to produce.