profile

Starting With Data

🟢 How to Answer Any "Show Me the Last 3 Months" Question With SQL

Published about 1 month ago • 4 min read

Hi Reader,

Time-based questions come up constantly in business.

"Show me bookings from the last 90 days."

"How does this quarter compare to last quarter?"

"What's the week-over-week trend?"

And most analysts handle them the worst possible way: by hardcoding dates.

-- Don't do this
WHERE booking_date >= '2026-01-01' AND booking_date < '2026-04-01'

This works today.

Next quarter, you have to remember to change the dates. You won't remember. And the report will silently show stale data until someone notices.

Here are three date function patterns that make your time-based queries automatic.

Pattern 1: Rolling Windows With INTERVAL

"Show me the last N days/months/years" — this is probably the most common time-based request.

-- Bookings from the last 90 days
SELECT 
    b.booking_id,
    c.first_name || ' ' || c.last_name AS customer_name,
    e.expedition_name,
    b.booking_date::date AS booked_on,
    b.total_amount
FROM bookings b
    INNER JOIN customers c ON b.customer_id = c.customer_id
    INNER JOIN expedition_instances ei ON b.instance_id = ei.instance_id
    INNER JOIN expeditions e ON ei.expedition_id = e.expedition_id
WHERE b.booking_date >= CURRENT_DATE - INTERVAL '90 days'
    AND b.status IN ('completed', 'confirmed')
ORDER BY b.booking_date DESC;

I ran this against the Summit Adventures database (the fake adventure tourism company I created to help people learn business analytics).

The key line:

WHERE b.booking_date >=CURRENT_DATE-INTERVAL'90 days'

CURRENT_DATE always returns today's date. INTERVAL '90 days' subtracts 90 days from it. Run this query on March 1st, it looks back to December 1st. Run it on June 15th, it looks back to March 17th. No manual updates needed.

Common INTERVAL values:

  • INTERVAL '7 days' — last week
  • INTERVAL '30 days' — last month (approximate)
  • INTERVAL '90 days' — last quarter (approximate)
  • INTERVAL '6 months' — last half year
  • INTERVAL '1 year' — last year

Pattern 2: Grouping by Time Period With DATE_TRUNC

"Show me revenue by month" or "bookings by week" — you need to group timestamps into periods.

-- Weekly booking volume for the last 3 monthsSELECT 
    DATE_TRUNC('week', b.booking_date)::dateAS week_starting,
    COUNT(*) AS bookings_this_week,
    SUM(b.total_amount) AS weekly_revenue,
    ROUND(AVG(b.total_amount), 2) AS avg_booking_value
FROM bookings b
WHERE b.booking_date >=CURRENT_DATE-INTERVAL'3 months'AND b.status IN ('completed', 'confirmed')
GROUPBY DATE_TRUNC('week', b.booking_date)
ORDERBY week_starting DESC;

DATE_TRUNC('week', booking_date) takes any timestamp and rounds it down to the start of that week. So bookings on Monday, Wednesday, and Friday of the same week all get grouped together.

DATE_TRUNC options:

  • 'day' — group by day
  • 'week' — group by week (starting Monday)
  • 'month' — group by month (starting on the 1st)
  • 'quarter' — group by quarter (Q1, Q2, Q3, Q4)
  • 'year' — group by year

Pattern 3: Comparing Time Periods

"How did this month compare to last month?" — this combines date functions with the comparison techniques we covered a few weeks ago.

-- This month vs last month: booking volume and revenue
WITH this_month AS (
    SELECT 
        COUNT(*) AS bookings,
        COALESCE(SUM(total_amount), 0) AS revenue
    FROM bookings
    WHERE booking_date >= DATE_TRUNC('month', CURRENT_DATE)
        AND status IN ('completed', 'confirmed')
),
last_month AS (
    SELECT 
        COUNT(*) AS bookings,
        COALESCE(SUM(total_amount), 0) AS revenue
    FROM bookings
    WHERE booking_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month'
        AND booking_date < DATE_TRUNC('month', CURRENT_DATE)
        AND status IN ('completed', 'confirmed')
)
SELECT 
    tm.bookings AS this_month_bookings,
    lm.bookings AS last_month_bookings,
    tm.bookings - lm.bookings AS booking_change,
    tm.revenue AS this_month_revenue,
    lm.revenue AS last_month_revenue,
    CASE 
        WHEN lm.revenue > 0 
        THEN ROUND((tm.revenue - lm.revenue) * 100.0 / lm.revenue, 1)
        ELSE NULL 
    END AS revenue_change_pct
FROM this_month tm, last_month lm;

This query always compares the current month-to-date with the previous full month. No hardcoded dates. It updates automatically every time you run it.

Putting It All Together: The Executive Dashboard Query

Here's how these patterns combine into a practical business report:

-- Executive summary: current performance with trend context
WITH current_quarter AS (
    SELECT 
        COUNT(DISTINCT b.booking_id) AS bookings,
        COUNT(DISTINCT b.customer_id) AS unique_customers,
        SUM(p.amount) AS revenue
    FROM bookings b
        INNER JOIN payments p ON b.booking_id = p.booking_id
    WHERE b.booking_date >= DATE_TRUNC('quarter', CURRENT_DATE)
        AND p.payment_status = 'completed'
        AND b.status IN ('completed', 'confirmed')
),
previous_quarter AS (
    SELECT 
        COUNT(DISTINCT b.booking_id) AS bookings,
        COUNT(DISTINCT b.customer_id) AS unique_customers,
        SUM(p.amount) AS revenue
    FROM bookings b
        INNER JOIN payments p ON b.booking_id = p.booking_id
    WHERE b.booking_date >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months'
        AND b.booking_date < DATE_TRUNC('quarter', CURRENT_DATE)
        AND p.payment_status = 'completed'
        AND b.status IN ('completed', 'confirmed')
)
SELECT 
    cq.bookings AS current_qtr_bookings,
    pq.bookings AS prev_qtr_bookings,
    cq.revenue AS current_qtr_revenue,
    pq.revenue AS prev_qtr_revenue,
    cq.unique_customers AS current_qtr_customers,
    CASE 
        WHEN pq.revenue > 0 
        THEN ROUND((cq.revenue - pq.revenue) * 100.0 / pq.revenue, 1) 
        ELSE NULL 
    END AS revenue_change_pct
FROM current_quarter cq, previous_quarter pq;

This gives your CEO everything in one query: current quarter performance, previous quarter comparison, and the percentage change. All automatic.

Try This At Your Job

Take a report you update manually (changing date ranges each time) and replace the hardcoded dates with CURRENT_DATE and INTERVAL. You'll never have to touch those dates again.

Until next time,

Brian

Brian Graves, creator of Analytics in Action

Say 👋 on X/Twitter, LinkedIn, or book a call with me. You can always reply to these emails. I check them all.

​

P.S. Time-based analysis is the focus of Module 3 of SQL for Business Impact, where we use the Heartrate Monitor Blueprint to understand booking patterns over time. If you want to go deeper into trend analysis, seasonal patterns, and time-series queries, check it out at sqlforbusinessimpact.com.

P.P.S. Do you have reports with hardcoded dates that break every month? Reply and tell me about them — I might feature the fix in a future email. I read every response.

Starting With Data

Weekly data analytics tutorials and guides. 5 minutes to read.

Learn to build analytics projects with SQL, Tableau, Excel, and Python. For data analysts looking to level up their career and complete beginners looking to get started. No fluff. No theory. Just step-by-step tutorials anyone can follow.

Read more from Starting With Data

Hello Reader, This week's newsletter is a bit different. There's SQL in here, but the real topic is career strategy. Because one of the most common questions I get from readers is some version of: "I've been learning SQL for a few months. How do I prove I can actually do this job?" The honest answer: a portfolio of 2-3 projects that demonstrate business thinking, not just technical syntax. The problem is most portfolios I've reviewed look the same. A Kaggle competition. A tutorial from...

4 days ago • 5 min read

Hello Reader, A common analytics question that sounds simple but isn't: "Which customers have booked the same type of expedition more than once?" You can't answer this with a regular JOIN between two different tables. The information lives in one table. You need to compare rows within that table to find patterns. That's what a self-JOIN does. It joins a table to itself. It sounds unusual, but once you see the pattern, you'll recognize situations where it's exactly what you need. The Business...

11 days ago • 4 min read

Hello Reader, You've built a clean customer report. Locations, booking history, revenue by region. You send it off to whoever asked for it. Ten minutes later: "Why does this report show blank cells in the location column? And why do the totals not add up?" Welcome to the NULL problem. You're going to learn how to solve it once and for all today. In the Summit Adventures database (the fake adventure tourism company I created to help people learn business analytics), 78.2% of customers don't...

18 days ago • 4 min read