Thirty questions someone at the resort needs answered
Each one is written the way it would arrive at work: from a person, without telling you which tables to join or what you will find. Every question has a real answer in the data.
How to read an entry. Asked by is who wants the answer, so write your result for that person. Start with names the tables most likely to matter. Level runs from 1 (a few queries) to 3 (several steps and a judgment call). A good answer says what you found, how sure you are, what else could explain it and what you would do about it.
Pricing, passes and promotions
Did date-based pricing work?
Asked by the VP of Revenue. In March 2019 one-day tickets moved from one flat price to prices that depend on the date. She wants to know what it did to crowding on the busiest days and to ticket revenue, and whether she should defend it at the next board meeting.
- Start with
- calendar, tickets, park_entries, bookings
- Skills
- SQL, statistics
- Size
- Works in any size
- Level
- 2 of 3
Was restructuring the annual pass a good trade?
Asked by the Director of Pass Programs. In January 2024 the two annual passes were replaced by three tiers. Pass revenue went up. He has been asked whether the resort lost anything it should care about.
- Start with
- annual_passes, pass_products, transactions, venues
- Skills
- SQL, business analysis
- Size
- Works in any size
- Level
- 3 of 3
Is the skip-the-line product good for guests?
Asked by the Chief Experience Officer. Paso Rapido launched in February 2022 and sells well. She wants to know how it affects the day for the guests who buy it and for the guests who do not.
- Skills
- SQL, statistics
- Size
- Needs Standard
- Level
- 3 of 3
Does the Stay and Play discount pay for itself?
Asked by the Director of Hotel Revenue. The 20% hotel package has run four times. Marketing calls it a success because thousands of bookings used the code. He wants to know how many of those bookings the discount actually caused.
- Start with
- bookings, promotions, hotel_stays, calendar
- Skills
- SQL, statistics, business analysis
- Size
- Works in any size
- Level
- 3 of 3
Should the mobile order offer be rolled out to everyone?
Asked by the Product Manager, Guest App. In fall 2021 a random half of eligible households were offered a discount on mobile food orders. She needs a recommendation: roll it out to all, to some, or drop it.
- Start with
- experiment_assignments, transactions, promotions
- Skills
- statistics, SQL
- Size
- Needs Standard
- Level
- 2 of 3
Guest experience
What part of a trip decides whether a guest comes back?
Asked by the Chief Experience Officer. She has budget to improve one part of the visit and wants it spent where it changes rebooking most.
- Start with
- survey_day_ratings, survey_responses, bookings
- Skills
- statistics, SQL
- Size
- Needs Standard
- Level
- 3 of 3
Are rain vouchers worth giving away?
Asked by the Director of Guest Services. Staff hand out free return vouchers on washed-out days, at their discretion. Finance sees a giveaway. He wants to know what the resort gets back.
- Start with
- return_vouchers, bookings, weather_hourly, park_entries
- Skills
- SQL, statistics
- Size
- Works in any size
- Level
- 2 of 3
Do the Paseo concert nights sell annual passes?
Asked by the Marketing Manager, Local Market. Noches de Velaluz has run every spring and fall since 2019 and costs money to put on. She wants to know whether it brings locals into the pass program, and how strongly she can claim that.
- Start with
- events, event_registrations, annual_passes, households
- Skills
- SQL, business analysis, statistics
- Size
- Works in any size
- Level
- 2 of 3
How do families with very young children use the parks?
Asked by the Director of Park Operations. He is planning dining hours and stroller services and suspects these families have a different day from everyone else.
- Start with
- guests, park_entries, transactions, households
- Skills
- SQL, dashboard
- Size
- Works in any size
- Level
- 2 of 3
Is guest satisfaction going up or down?
Asked by the Chief Executive. Each park's general manager reports that satisfaction in their park has improved every year. The resort-wide score she is shown has not. She wants to know who is right.
- Start with
- survey_responses, survey_day_ratings, park_entries
- Skills
- statistics, dashboard
- Size
- Works in any size
- Level
- 2 of 3
What did Cabo Trueno change?
Asked by the VP of Development. The new land opened in Punta Tormenta in May 2021. Before proposing the next one she wants a full account of what it did: to crowds, to waits elsewhere, to food sales, across all three parks.
- Start with
- lands, attractions, park_entries, wait_snapshots, ride_events, transactions, venues
- Skills
- SQL, dashboard
- Size
- Works in any size
- Level
- 3 of 3
Which park does lightning hurt more?
Asked by the Director of Park Operations. Both theme parks close outdoor rides when lightning is near. He wants to know how much ride capacity each park loses to weather and why they differ.
- Start with
- ride_downtime, attractions, weather_hourly, lands
- Skills
- SQL, dashboard
- Size
- Works in any size
- Level
- 1 of 3
How did the resort recover from the hurricane?
Asked by the Chief Financial Officer. The resort closed for six days in fall 2022. She wants to know how long demand took to come back, and whether it came back at the same pace for every kind of guest. She also wants a forecast method she can reuse.
- Start with
- bookings, households, park_entries, calendar
- Skills
- forecasting, SQL
- Size
- Works in any size
- Level
- 3 of 3
Rides and maintenance
Why does El Remolino keep breaking down?
Asked by the VP of Engineering. The coaster's closures have become a regular guest complaint. He wants to know when it started, what is behind it, and what it would take to fix.
- Start with
- ride_downtime, work_orders, attractions, budgets
- Skills
- SQL, business analysis, dashboard
- Size
- Works in any size
- Level
- 3 of 3
Food, retail and suppliers
Why are food waits so uneven?
Asked by the Director of Food and Beverage. Some days a quick service restaurant turns orders around in minutes and other days the same place is slow, with the same menu. She wants the cause.
- Start with
- transactions, shifts, work_locations, venues, survey_day_ratings
- Skills
- SQL, statistics
- Size
- Works in any size
- Level
- 3 of 3
Why do seafood dishes keep disappearing from menus?
Asked by the Executive Chef. Guests and servers both complain that seafood items are unavailable more and more often. He wants to know how often, since when, and whose fault it is.
- Start with
- transaction_lines, products, purchase_orders, suppliers, inventory_daily
- Skills
- SQL, business analysis
- Size
- Works in any size
- Level
- 2 of 3
Did snack and candy sales really change in April 2023?
Asked by the Finance Manager. The monthly category report shows some categories jumping and others collapsing from one month to the next. Nobody in operations noticed anything. She needs a category trend she can trust across the whole eight years.
- Start with
- transaction_lines, products, category_code_map, transactions
- Skills
- SQL, business analysis
- Size
- Works in any size
- Level
- 2 of 3
People
Did the 2023 wage increase reduce turnover?
Asked by the Chief People Officer. Hourly pay went up in April 2023, largely to stop people leaving. She needs to report whether it worked, and whether it worked everywhere.
- Start with
- employees, employment_history, departments, positions
- Skills
- SQL, statistics
- Size
- Works in any size
- Level
- 2 of 3
Is staff training worth the hours?
Asked by the Training Manager. Training takes people off the floor and managers resent it. He wants evidence, one way or the other, that it shows up in something guests notice.
- Start with
- training_completions, training_courses, employees, work_locations, complaints, transactions
- Skills
- SQL, statistics
- Size
- Works in any size
- Level
- 3 of 3
Where is the overtime coming from?
Asked by the Chief Financial Officer. Overtime cost is up and she does not know why. She wants the department, the date it began and the cause.
- Start with
- payroll, shifts, employees, departments
- Skills
- SQL, dashboard
- Size
- Works in any size
- Level
- 2 of 3
Process and service
Why do refunds take so long?
Asked by the Director of Guest Services. Guests complain that refunds are slow. Staff say most are fast. He wants to know which refunds are slow, since when, and where in the process the time goes.
- Start with
- refunds, refund_events
- Skills
- process mining, SQL
- Size
- Works in any size
- Level
- 2 of 3
Is the IT help desk working?
Asked by the Chief Information Officer. Overall ticket numbers look acceptable. Complaints from other departments say otherwise. She wants to know where the problem is and what is causing it.
- Start with
- it_tickets, it_systems, employees
- Skills
- SQL, dashboard, business analysis
- Size
- Works in any size
- Level
- 2 of 3
What should the guest app do next?
Asked by the Product Manager, Guest App. The backlog is full of ideas from inside the company. He wants one requirement that comes from guests themselves, written up with the evidence, and he suspects it is hiding somewhere nobody has read closely.
- Start with
- complaints, ride_downtime, survey_responses
- Skills
- text analysis, business analysis
- Size
- Works in any size
- Level
- 3 of 3
Why do systems keep going down?
Asked by the Chief Information Officer. Outages come in clusters. She wants to know whether they are bad luck or something the department is doing to itself, and what rule would prevent the most.
- Start with
- it_incidents, change_requests, it_systems
- Skills
- SQL, statistics
- Size
- Works in any size
- Level
- 2 of 3
Marketing and finance
How many bookings do the email campaigns really drive?
Asked by the Chief Marketing Officer. The attribution report credits email with a large number of bookings at almost no cost, and the team wants to triple the send volume. Before agreeing, she wants a second opinion on that number.
- Start with
- campaigns, campaign_audience, booking_attribution, bookings
- Skills
- statistics, business analysis
- Size
- Needs Standard
- Level
- 3 of 3
Which department cannot budget its labor?
Asked by the Controller. Every department misses its labor budget sometimes. He wants to know whether anyone misses it in the same way every year, and what they should change in how they build the budget.
- Start with
- budgets, gl_entries, gl_accounts, departments, payroll
- Skills
- SQL, dashboard
- Size
- Works in any size
- Level
- 2 of 3
Does deferred ticket revenue reconcile?
Asked by the External Auditor. She has asked for the deferred ticket revenue balance in the ledger to be tied to the ticketing system at every month end. Find any month that does not tie, explain the difference to the dollar, and say whether it was corrected.
- Start with
- gl_entries, gl_accounts, tickets
- Skills
- SQL, business analysis
- Size
- Needs Full
- Level
- 3 of 3
Three more that nobody planned
Why did every hourly department blow its labor budget in summer 2023?
Asked by the Controller.
- Start with
- budgets, gl_entries, employment_history
- Skills
- SQL, business analysis
- Size
- Works in any size
- Level
- 1 of 3
When should the resort add first aid staff?
Asked by the Director of Safety.
- Start with
- safety_incidents, weather_hourly, park_entries, park_hours
- Skills
- statistics, forecasting
- Size
- Works in any (rates per visit need Full)
- Level
- 2 of 3
Which menu items and shops make the money?
Asked by the Director of Food and Beverage. She knows sales by item. She has never seen margin by item, venue or category.
- Start with
- transaction_lines, product_ingredients, inventory_items, purchase_order_lines, products, venues
- Skills
- SQL, dashboard
- Size
- Works in any size
- Level
- 2 of 3
Open projects
No planted answer here. These are the standard portfolio builds, and the data is wide enough to support all of them.
- Executive dashboard. Attendance, occupancy, revenue per guest, satisfaction and labor cost on one page, with the 2022 closure handled sensibly.
- Attendance forecast. Daily park attendance from the calendar, weather, prices and school breaks. Train on 2018 to 2023 and test on 2024 and 2025.
- Wait time model. Predict posted waits by attraction and quarter hour, then check posted against actual waits in ride_events.
- Hotel revenue management. Occupancy, average rate and revenue per available room by hotel, room type and season.
- Customer segmentation. Cluster households on visit frequency, spend, party type and market. Then describe each segment in plain words.
- Customer lifetime value. Value per household by acquisition year and market, and how long a new household takes to pay back.
- Market basket. What sells together at quick service restaurants and in shops.
- Income statement and variance pack. Monthly income statement from the ledger with budget variances and commentary.
- Workforce planning. Staff hours against attendance by department. Where is the resort over or under staffed by season?
- Supplier scorecard. On-time rate, cost trend and spend share for all twelve suppliers.
- Maintenance reliability. Time between failures and time to repair by ride type and age.
- Requirements package. Pick any question above and write it up as a business analyst would: problem statement, stakeholders, current and future state, requirements, acceptance criteria and a measure of success.
Checking your work. The answers are published separately, behind a spoiler notice. A finding is worth more in a portfolio when it is yours, so try the question first. If you publish what you find, please mark spoilers. When you are ready, this page says where the answers are.