This was supposed to be a quick SQL request

A stakeholder asks:

“Can you send me a list of our active customers and their total revenue?”

On the surface, that sounds simple enough.

You have a customer table, an orders table, maybe an order items table, and you know how to join them.

So you write the query, run it, and get a result.

The only problem is the customer count is much higher than expected.

This is where I usually stop looking at the SQL itself and start looking at the logic behind it.

FIRST CHECK

What does one row actually represent?

If the customer table has one row per customer, but the orders table has one row per order, the result will naturally create multiple rows for the same customer.

Add an order items table and now you may have several rows for every order too.

That does not automatically mean the join is wrong. It does mean I need to be very clear about the grain of the final dataset and where I am doing my aggregation.

SELECT
  COUNT(*) AS total_rows,
  COUNT(DISTINCT customer_id) AS customers
FROM customer_revenue;

If I expected one row per customer and I have 12,000 rows for 4,000 customers, I know I need to look more closely.

NEXT

Go back to the request.

“Active customers” sounds specific, but it usually is not.

The same thing applies to “total revenue.”

Lifetime revenue? Revenue this year? Gross revenue? Revenue after refunds?

Before I change the query, I want those definitions clear.

I wrote more about this in my guide on translating business questions into SQL .

THEN

Decide where the aggregation should happen.

If I need one row per customer, I may calculate revenue at the customer level first and join that result back to the customer table.

WITH revenue_by_customer AS (

  SELECT
    o.customer_id,
    SUM(oi.item_revenue) AS total_revenue
  FROM orders o
  JOIN order_items oi
    ON o.order_id = oi.order_id
  GROUP BY o.customer_id

)

SELECT
  c.customer_id,
  r.total_revenue
FROM customers c
LEFT JOIN revenue_by_customer r
  ON c.customer_id = r.customer_id;

Now the revenue result already has one row per customer before I join it back. That makes the final dataset much easier to reason through.

BEFORE I SEND IT

I still want to validate it.

I would compare the result with something I already trust.

Check the active customer count against an existing report.

Compare total revenue with a finance number.

Look at a handful of customers directly in the source system.

Check unusually high values, missing revenue, duplicate customers, and anything that changed more than expected.

This is one of the reasons I think learning SQL syntax is only part of the job. A query can be completely valid and still answer the wrong version of the question.

What is one data request you have received that sounded simple at first and turned into something much bigger?