SQL Queries for Sales Report Dashboards

Most internal sales dashboards are built on top of a handful of recurring SQL patterns: revenue by month, revenue by region, top-selling products, and growth compared to the previous period. This guide gives you copy-paste-ready queries for each of these, based on a typical e-commerce style schema.

Sample schema

orders
------------------------
id          INT PRIMARY KEY
customer_id INT
region      VARCHAR(50)
order_date  DATE
total       DECIMAL(10,2)

order_items
------------------------
id          INT PRIMARY KEY
order_id    INT
product_id  INT
quantity    INT
price       DECIMAL(10,2)

products
------------------------
id          INT PRIMARY KEY
name        VARCHAR(100)

Revenue by month

In PostgreSQL, DATE_TRUNC groups timestamps by month cleanly:

-- PostgreSQL
SELECT
  DATE_TRUNC('month', order_date) AS month,
  SUM(total) AS revenue
FROM orders
GROUP BY month
ORDER BY month;

In MySQL, use DATE_FORMAT instead:

-- MySQL
SELECT
  DATE_FORMAT(order_date, '%Y-%m') AS month,
  SUM(total) AS revenue
FROM orders
GROUP BY month
ORDER BY month;

Revenue by region

SELECT
  region,
  COUNT(*) AS total_orders,
  SUM(total) AS revenue
FROM orders
GROUP BY region
ORDER BY revenue DESC;

Top-selling products

This joins order_items to products and ranks by total revenue generated, limited to the top 10:

SELECT
  p.name AS product_name,
  SUM(oi.quantity) AS units_sold,
  SUM(oi.quantity * oi.price) AS revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.id
GROUP BY p.name
ORDER BY revenue DESC
LIMIT 10;

Month-over-month growth

Use the LAG() window function to compare each month's revenue against the previous month:

WITH monthly AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(total) AS revenue
  FROM orders
  GROUP BY month
)
SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS previous_month_revenue,
  ROUND(
    (revenue - LAG(revenue) OVER (ORDER BY month)) /
    NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100, 2
  ) AS growth_percent
FROM monthly
ORDER BY month;

Tips for building dashboard-ready SQL:

• Use NULLIF to avoid divide-by-zero errors when calculating percentages.
• Always add an index on order_date and region if you filter or group by them frequently.
• Store pre-aggregated summary tables for very large datasets to avoid recomputing on every dashboard load.
• Use LIMIT with ORDER BY together — LIMIT alone without ORDER BY gives unpredictable rows.

Generating custom report queries

If your schema differs from the example above, an AI SQL generator can adapt these patterns to your exact table and column names — just describe the report you need, such as "top 5 regions by revenue this quarter."

Frequently Asked Questions