The CEO Wants Capacity Numbers by 4pm. Here’s the Query I’d Run. (CTE Included)

An executive question arrives in a single sentence: “Do we have enough capacity for our busiest trips?” The fastest answer usually starts with one total: open seats, booked participants, or guide assignments.

Any one of those alone can make a category look over or under capacity. A recommendation you can defend needs all four measures: trips, seats, participants, and guides.

The Gordon Ramsay Blueprint is how you diagnose a request like this without getting lost in the details. Start with the operating system, identify the measures that matter, then make the next decision clear.

In my day job, I open larger requests with a short discovery conversation about the business outcome. It keeps a quick data pull from turning into a fast answer to the wrong question.

Four measures for the first capacity view

1. Count scheduled trips. Shows the operational activity each expedition type is carrying.
2. Add seats and participants separately. Planned capacity is the ceiling. Current participants show the demand already on the books.
3. Bring in guide assignments at the trip level. Staffing can become the constraint even when seats are still open.

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

WITH instance_metrics AS (
    SELECT
        ei.instance_id,
        e.expedition_type,
        e.max_participants,
        ei.current_participants,
        COUNT(ga.assignment_id) AS guide_assignments
    FROM expeditions AS e
    JOIN expedition_instances AS ei
        ON ei.expedition_id = e.expedition_id
    LEFT JOIN guide_assignments AS ga
        ON ga.instance_id = ei.instance_id
    GROUP BY
        ei.instance_id,
        e.expedition_type,
        e.max_participants,
        ei.current_participants
)
SELECT
    expedition_type,
    COUNT(*) AS scheduled_instances,
    SUM(max_participants) AS planned_capacity,
    SUM(current_participants) AS current_participants,
    SUM(guide_assignments) AS guide_assignments
FROM instance_metrics
GROUP BY expedition_type
ORDER BY current_participants DESC;

Results:

| Expedition type | Scheduled instances | Planned capacity | Current participants | Guide assignments |
| — | —: | —: | —: | —: |
| Safari | 124 | 1,355 | 746 | 153 |
| Photography | 121 | 1,285 | 716 | 153 |
| Climbing | 89 | 871 | 508 | 117 |
| Hiking | 78 | 819 | 417 | 118 |
| Cultural | 88 | 809 | 411 | 107 |

Safari and photography have the highest current participation. That’s a diagnostic view, not a staffing recommendation on its own. It tells us which categories deserve the next questions:

How close are individual departures to capacity?
How many guides do we need per trip type?
Are guide assignments spread across dates, or concentrated in a peak period?

Write the decision before the SQL

When a request feels urgent, write down the business decision first. Then identify the minimum measures that decision needs. Here that’s trips, seats, participants, and guides. Each measure lives in a different table and has a clear reason to be included.

The fast answer isn’t the query with the most columns. It’s the query that gives the team a reliable first view and names the next verification step.

Capacity totals need a second question

A category total can hide a full departure. The next query should look at individual trip dates and remaining seats.
A guide count isn’t a staffing rule. You still need the required guide-to-participant ratio for each trip type.
Join order changes the math. Aggregate participants and seats at the trip-instance level before a one-to-many guide-assignment join can duplicate them.

The next calculation: fill rate by departure

For each scheduled trip, divide `current_participants` by `max_participants`. A category can look comfortably staffed overall while one departure is nearly full and another has plenty of open seats. That view tells the team whether they need more inventory, a staffing adjustment, or better demand generation for specific dates.

This Week’s Action Item

Choose one recurring urgent request from work. Write the decision behind it, then list the three to five measures needed to answer it. Only after that, identify the tables and join keys.