Hey Reader -- I'm trying something new this week! Can you take a minute to fill out this quick survey? Thanks in advance!

Okay, on to this weeks topic:

Imagine this: Your boss walks into the Monday morning meeting and says "Show me revenue trends for the last 6 months."

Everyone looks at you.

Most analysts panic. They open Excel, export CSVs, build pivot tables, make charts. An hour later, they have numbers but no story.

Confident analysts? They write one SQL query and present insights in minutes with full confidents.

Here's how:

The Query That Executives Love

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

-- Monthly revenue trend for executive reporting
SELECT 
    TO_CHAR(p.payment_date, 'YYYY-MM') AS payment_month,
    COUNT(DISTINCT b.booking_id) AS bookings,
    SUM(p.amount) AS monthly_revenue,
    ROUND(AVG(p.amount), 2) AS avg_transaction
FROM payments p
    INNER JOIN bookings b ON p.booking_id = b.booking_id
WHERE p.payment_status = 'completed'
    AND p.payment_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY TO_CHAR(p.payment_date, 'YYYY-MM')
ORDER BY payment_month DESC
LIMIT 6;

Here's what we found for Summit Adventures (limited to 6 months of data to show the concept. In the real world, you'd run all 12 months...):

• November 2025: 4 bookings, over $7,300 revenue
• October 2025: 23 bookings, over $21,500 revenue
• September 2025: 40 bookings, over $84,900 revenue
• August 2025: 64 bookings, over $117,800 revenue
• July 2025: 87 bookings, over $140,900 revenue
• June 2025: 109 bookings, over $205,800 revenue

The story these numbers tell:

Summit Adventures had a strong summer season (June-August: over $464K revenue, 260 bookings) but revenue dropped 90% from June to November.

Think of this as a strategic conversation starter:

• Is this seasonal? (Yep. We can see that adventure travel peaks in summer.)
• Should we add winter expeditions? (Potential marketing opportunity.)
• How do we maintain cash flow in off-season? (The Director of Operations would love to know this.)

One query. Three executive-ready insights. That's what confident analysts deliver.

Breaking Down The Query

Line 3: TO_CHAR() for month formatting Converts timestamps to 'YYYY-MM' format (2025-06) so we can group by month. Without this, you'd group by exact payment time which isn't useful for trends.

Lines 4-6: Three key metrics

• Bookings = volume (how busy were we?)
• Revenue = dollars (what did we earn?)
• Avg transaction = quality (high-value or discount customers?)

All three together tell a complete story.

Line 10: The 12-month rolling window CURRENT_DATE - INTERVAL '12 months' means this query always shows the past year of results, no matter when you run it. No manual date updates needed.

Line 11: GROUP BY matches Line 3 Important rule: Your GROUP BY must match your SELECT for non-aggregated columns. Same TO_CHAR() format on both.

Why This Simple Query Works At Any Company

This pattern adapts to any business:

E-commerce → GROUP BY TO_CHAR(order_date, 'YYYY-MM')

SaaS → GROUP BY TO_CHAR(subscription_start_date, 'YYYY-MM')

Retail → GROUP BY TO_CHAR(transaction_date, 'YYYY-MM')

The structure stays the same. Just change the table names and date column.

Common Mistakes to Avoid

Mistake #1: Not filtering payment_status If you include pending or failed payments, your revenue numbers are fantasy. Always filter for 'completed' or 'succeeded' status.

Mistake #2: Using date ranges instead of intervals

-- DON'T DO THIS
WHERE payment_date >= '2024-01-01'  -- This becomes outdated
-- DO THIS INSTEAD  
WHERE payment_date >= CURRENT_DATE - INTERVAL '12 months'  -- Always current

Mistake #3: Not ordering by month If you don't ORDER BY the month column, results come back random. This look chaotic and messy.

Try This Tomorrow

Open your company database. Run this pattern:

SELECT 
    TO_CHAR(date_column, 'YYYY-MM') AS month,
    COUNT(*) AS volume,
    SUM(amount_column) AS revenue
FROM your_table
WHERE status = 'completed'
    AND date_column >= CURRENT_DATE - INTERVAL '6 months'
GROUP BY TO_CHAR(date_column, 'YYYY-MM')
ORDER BY month DESC;

Replace:

• date_column with your transaction/order/booking date
• amount_column with your revenue field
• your_table with your main transaction table

You'll have executive-ready revenue trends in under a minute.

What Executives Actually Want to See

They don't want raw data dumps. They really want answers:

1. Trends: Is revenue up or down?
2. Volume: Are we getting more customers?
3. Quality: Are customers spending more or less?

This one query delivers all three.

The next time someone asks for "revenue trends," you'll be the analyst who delivers insights, not just numbers.

Until next time,

Brian

P.S. Are you interested in more SQL content or something else like Python, Tableau, or Agentic AI? Let me know in this 2 minute survey when you get this email.