Home›Query Library›Database

Who are your top 20 customers, and how much are they worth?

Pareto principle holds for most B2B businesses — 20% of customers drive 80% of revenue. This query finds yours, including their order history and average deal size.

📊 Any SQL database👥 Sales and finance teams🔤 Plain-English → SQL

The question

Show me my top 20 customers by total revenue this year with order count and average deal size

↓

The SQL Taptic generates

Top Customers by Revenue Query — generated by Taptic Data AI

SELECT
  c.company_name,
  c.account_manager,
  COUNT(DISTINCT o.id)                          AS order_count,
  ROUND(SUM(o.total_amount)::numeric, 2)        AS total_revenue,
  ROUND(SUM(o.total_amount)::numeric
    / NULLIF(COUNT(DISTINCT o.id), 0), 2)       AS avg_deal_size,
  MIN(o.order_date)::date                       AS first_order,
  MAX(o.order_date)::date                       AS last_order,
  ROUND(SUM(o.total_amount)::numeric * 100
    / SUM(SUM(o.total_amount)) OVER ()::numeric, 2) AS pct_of_total_revenue
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.order_date >= DATE_TRUNC('year', CURRENT_DATE)
  AND o.status NOT IN ('cancelled', 'refunded')
GROUP BY c.id, c.company_name, c.account_manager
ORDER BY total_revenue DESC
LIMIT 20

Schema-aware SQL generated from plain English18 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

Joins orders to customers to include company name and account manager in results
Filters to current year and excludes cancelled/refunded orders
Uses a window function (SUM OVER ()) to calculate each customer's share of total revenue in one pass
Includes first and last order dates to reveal customer tenure and recency
NULLIF prevents division-by-zero in the average deal size calculation

What it returns

Result description

Top 20 customers ranked by total revenue with order count, average deal size, first/last order dates, and percentage of total company revenue.

Why it matters

The business impact

Knowing your top customers by revenue enables smarter account management, more targeted retention efforts, and better CAC/LTV modeling. The percentage-of-total column immediately shows concentration risk — if 3 customers represent 60% of revenue, that's a business risk worth knowing.

Generate this automatically

Skip the SQL. Ask the question.

In Taptic Data, you type "Show me my top 20 customers by total revenue this year with ..." 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.