Home›Query Library›Database

Which regions are driving your revenue — and which are lagging?

Revenue by region is one of the most common business analysis questions across any industry. This is how Taptic's AI generates it from plain English against your actual schema.

📊 Any SQL database👥 Business analysts at companies with regional sales data🔤 Plain-English → SQL

The question

Show me total revenue by region for this quarter compared to last quarter

↓

The SQL Taptic generates

Revenue by Region Query — generated by Taptic Data AI

WITH quarters AS (
  SELECT
    region,
    SUM(CASE
      WHEN order_date >= DATE_TRUNC('quarter', CURRENT_DATE)
       AND order_date <  DATE_TRUNC('quarter', CURRENT_DATE) + INTERVAL '3 months'
      THEN total_amount ELSE 0
    END)                             AS current_quarter,
    SUM(CASE
      WHEN order_date >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months'
       AND order_date <  DATE_TRUNC('quarter', CURRENT_DATE)
      THEN total_amount ELSE 0
    END)                             AS prior_quarter
  FROM orders
  WHERE order_date >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months'
  GROUP BY region
)
SELECT
  region,
  ROUND(current_quarter, 2)         AS current_q_revenue,
  ROUND(prior_quarter, 2)           AS prior_q_revenue,
  ROUND(current_quarter - prior_quarter, 2) AS change,
  ROUND(
    (current_quarter - prior_quarter)
    / NULLIF(prior_quarter, 0) * 100, 2
  )                                 AS pct_change
FROM quarters
ORDER BY current_quarter DESC

Schema-aware SQL generated from plain English28 lines

This query was generated by Taptic Data from plain English against a real Any SQL database schema. In Taptic, you type the question — the AI writes the SQL, runs it, and returns the result. You can edit the SQL, ask for explanations, and save it as a refreshable report.

How this query works

Breaking it down line by line

Uses a CTE with conditional SUM to calculate both quarters in a single pass over the data
DATE_TRUNC('quarter') automatically handles the current quarter start regardless of the date
CASE statements allow both quarters to be calculated in one GROUP BY — no UNION needed
Final SELECT adds absolute and percentage change vs prior quarter
NULLIF protects against division by zero for regions with no prior quarter revenue

What it returns

Result description

A table of regions with current quarter revenue, prior quarter revenue, absolute change, and percentage change — sorted by current revenue descending.

Why it matters

The business impact

Regional revenue comparison is fundamental to territory management, resource allocation, and sales compensation. Seeing Q/Q change by region immediately surfaces which markets are accelerating and which need attention — information that typically takes days to compile manually.

Generate this automatically

Skip the SQL. Ask the question.

In Taptic Data, you type "Show me total revenue by region for this quarter compared to..." and this SQL runs automatically against your real Any SQL database data.

Try Taptic Free — $29.99/mo

No credit card required. Connect your data source in under 5 minutes.