The Question a Single Table Cannot Answer (And the JOIN That Can)

A booking table shows who reserved and what they paid. It won’t tell you which trip type is driving cancellations. That answer is spread across tables, and a one-table query leaves the business story half-finished.

Real business questions rarely live in one table. A scheduled trip tells you when something happened. A catalog table tells you what was sold. The analysis becomes useful once you connect the pieces without losing the thread.

The FBI Evidence Board Blueprint is the shortcut. Pin up the sources, find the red string between them, and join only what the question needs.

Trace the answer before you write the JOIN

1. Circle the business fields. Booked value and cancellations live in `bookings`. Expedition type lives in `expeditions`.
2. Find the bridge. `expedition_instances` connects a reservation to the expedition catalog.
3. Prove one relationship at a time. Join two tables, inspect a few rows, then add the third and aggregate.

For Summit Adventures, the fake adventure tourism company I created to help people learn business analytics, I wanted to compare booked value and cancellations by expedition type. The path: `bookings -> expedition_instances -> expeditions`.

SELECT
    e.expedition_type,
    COUNT(b.booking_id) AS bookings,
    COUNT(b.cancellation_date) AS cancellations,
    ROUND(
        100.0 * COUNT(b.cancellation_date) / COUNT(b.booking_id),
        1
    ) AS cancellation_rate,
    SUM(b.total_amount) AS booked_value
FROM bookings AS b
JOIN expedition_instances AS ei
    ON ei.instance_id = b.instance_id
JOIN expeditions AS e
    ON e.expedition_id = ei.expedition_id
GROUP BY e.expedition_type
ORDER BY booked_value DESC;

Results:

| Expedition type | Bookings | Cancellation rate | Booked value |
| — | —: | —: | —: |
| Photography | 404 | 48.3% | $823,399.98 |
| Cultural | 288 | 40.6% | $782,145.45 |
| Safari | 369 | 46.3% | $754,356.02 |
| Hiking | 256 | 46.9% | $740,625.77 |
| Climbing | 283 | 51.9% | $686,023.47 |

Two different conversations sit in that table:

Photography leads booked value. Worth understanding what’s working.
Climbing has the highest cancellation rate. Worth understanding what’s breaking.

No single table could answer that. `bookings` has the money and cancellation dates. `expeditions` has the category. `expedition_instances` is the bridge.

Map the path before you write the JOIN

Say the question in plain English. List the tables that hold each part of the answer. Then identify the key that connects them.

For this question:

1. Booking value and cancellations live in `bookings`.
2. Expedition type lives in `expeditions`.
3. `instance_id` and `expedition_id` are the path between them.

Takes less time than debugging a JOIN that returns duplicate rows or misses a relationship.

The result is a conversation starter, not a verdict. Summit could next compare cancellation rates, trip dates, and booking sources. That work starts with a reliable connection between tables.

Watch for the duplicate-row trap

One-to-many joins inflate totals. Only count rows and sum money once you know what one row in the joined result represents.
Join keys have a job. `instance_id` links a booking to one scheduled trip. `expedition_id` links that trip to its catalog record.
Counts need a denominator. The cancellation count is useful. The cancellation rate is what shows why climbing deserves attention despite fewer bookings.

Before you aggregate, run a small `SELECT` with the three keys and `LIMIT 10`. You should be able to explain every row: one booking, one scheduled instance, one expedition record. That quick check catches a surprising number of bad joins.

This Week’s Action Item

Pick a question that needs two or three tables. Write the table names and join keys in a note before opening your SQL editor. Then build the smallest query that proves the connection works.