Your SQL Query Results Might Match, But Can Still Be Dead Wrong (Reconciliation Query Included)

Two dollar totals can match to the penny and still describe different business events. Treat either as revenue without defining it, and a clean report answers the wrong finance question.

Money columns create false confidence. `payments` has an amount column. `bookings` has a total amount column. It’s tempting to assume they answer the same question.

They usually don’t. One is money recorded as a payment. The other is the value of a booking. A good analyst checks the definition before presenting either as revenue.

Reconcile the numbers in three steps

1. Define the event behind each row. A booking records a commitment. A payment records money received.
2. Aggregate to a shared grain. Several payment rows can belong to one booking, so sum payments by `booking_id` before you compare.
3. Name the metric for the decision. A sales leader may need booked value. Finance may need payments received or recognized revenue.

I ran this against the live Summit Adventures database, the fake adventure tourism company I created to help people learn business analytics.

WITH payments_by_booking AS (
    SELECT
        booking_id,
        SUM(amount) AS payments_recorded,
        COUNT(*) AS payment_records
    FROM payments
    GROUP BY booking_id
)
SELECT
    COUNT(*) AS bookings_with_payment,
    SUM(payment_records) AS payment_records,
    SUM(payments_recorded) AS payments_recorded,
    SUM(b.total_amount) AS booking_value_for_paid_bookings
FROM payments_by_booking AS pb
JOIN bookings AS b
    ON b.booking_id = pb.booking_id;

Results:

| Bookings with payment | Payment records | Payments recorded | Booking value for paid bookings |
| —: | —: | —: | —: |
| 1,600 | 2,173 | $3,786,550.69 | $3,786,550.69 |

In this snapshot, the totals reconcile exactly. Reassuring, but it doesn’t make the fields interchangeable. A booking records a commitment. A payment records money received. The same total can answer two different business questions.

Define the event before you name the metric

Before a financial metric goes in a report, ask:

1. What business event does this row represent?
2. Can one customer or booking create several rows?
3. Does the amount reflect a commitment, an invoice, a payment, or recognized revenue?
4. Which status or date field defines the version of the metric the stakeholder needs?

Those questions protect your analysis and the people using it to make decisions. Precision in the definition matters as much as precision in the SQL.

Three definitions worth checking first

Payment received: money recorded against a booking, which may include deposits or partial payments.
Booking value: the customer commitment at the time of booking, which can differ from cash collected today.
Recognized revenue: the amount finance records under its accounting policy, often tied to when the trip is delivered.

Three questions before the number leaves your desk

1. Who will use this number? Sales may need booked value. A cash-management conversation may need payments received.
2. What date does the decision care about? Booking date, payment date, and departure date can place the same activity in three different reporting periods.
3. What label will the reader see? Use “payments received” or “booked value” instead of “revenue” when the definition isn’t settled.

Clear labels don’t slow the work down. They keep a leader from carrying the right number into the wrong conversation.

This Week’s Action Item

Find two columns in your work that appear to measure the same outcome. Write one sentence defining what each row represents, then compare the totals. If they differ, list the process steps that could explain why.