How to Turn Monthly Revenue Data Into Executive-Level Insights (Code Included)

Hello ,

Imagine this: Your CEO 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.

Here’s the exact query I use.

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:

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.

This isn’t just data. It’s a strategic conversation starter:
Is this seasonal? (Yes — adventure travel peaks in summer)
Should we add winter expeditions? (Marketing opportunity)
How do we maintain cash flow in off-season? (Operations question)

One query. Three executive-level 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 — not 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 last year, 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 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.

The Insight That Matters Most

Look at the average transaction amounts in our data:

June: $1,583 average
October: $863 average
November: $1,230 average

October had 23 bookings but the lowest average transaction. That suggests discount promotions or shorter trips.

November had only 4 bookings but higher average — could be premium winter expeditions.

This is the level of insight business leaders value.

You’re not just reporting numbers. You’re identifying patterns that drive business decisions:
Should we discount in slow months? (Maybe not — it lowers transaction value)
Should we focus on premium offerings? (November data suggests yes)
What’s our breakeven point for monthly revenue? (Operations can answer with this data)

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. Executives see chaos instead of trends.

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

They don’t want raw data dumps. They want:
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 ([say hi on twitter!](https://twitter.com/briangraves))

—

P.S. Queries like this are exactly what I teach in SQL for Business Impact — turning business questions into SQL answers that executives love. The course launch promotion just ended, but you can join the waitlist for the next cohort at [sqlforbusinessimpact.com](https://sqlforbusinessimpact.com).

P.P.S. What revenue questions do you get asked most often at work? Hit reply and tell me — I might feature your question in a future email.