Cruise Industry
Post-Pandemic Recovery

Phase 1 — Ask

Business Questions

Defining the problem and what we're trying to learn from the data.

Phase 2 — Prepare

Data Sources & Methodology

All data sourced from publicly available, verified financial disclosures and industry reports.

Phase 3 & 4 — Process & Analyze

Key Metrics at a Glance

2024 performance vs 2019 pre-pandemic baseline across both companies.

Complete Dataset

Phase 3 — Process (SQL)

Data Processing with SQL

Key SQL queries used to clean, filter, and transform the raw dataset before analysis. Data was structured in a relational table called cruise_data with one row per company per year.

-- Create the cruise data table
CREATE TABLE cruise_data (
  year              INT,
  company           VARCHAR(50),
  passengers_m      DECIMAL(5,2),
  occupancy_pct     INT,
  total_revenue_b   DECIMAL(6,2),
  ticket_revenue_b  DECIMAL(6,2),
  onboard_revenue_b DECIMAL(6,2),
  adj_ebitda_b      DECIMAL(6,2),
  net_yield_yoy_pct DECIMAL(5,2),
  notes             VARCHAR(100)
);

-- Sample insert (Carnival 2024)
INSERT INTO cruise_data VALUES (
  2024, 'Carnival', 14.0, 104,
  25.0, 14.9, 10.1, 6.1, 8.7,
  'Record revenues and occupancy'
);

-- Check for null values
SELECT *
FROM cruise_data
WHERE passengers_m IS NULL
   OR total_revenue_b IS NULL
   OR occupancy_pct IS NULL;

-- Check for duplicate records
SELECT year, company, COUNT(*) AS count
FROM cruise_data
GROUP BY year, company
HAVING COUNT(*) > 1;

-- Verify year range
SELECT MIN(year) AS first_year,
       MAX(year) AS last_year,
       COUNT(DISTINCT company) AS companies
FROM cruise_data;

-- Recovery vs pre-COVID baseline
SELECT
  a.company,
  b.passengers_m    AS passengers_2019,
  a.passengers_m    AS passengers_2024,
  ROUND((a.passengers_m - b.passengers_m)
    / b.passengers_m * 100, 1) AS pax_growth_pct,
  b.total_revenue_b AS revenue_2019,
  a.total_revenue_b AS revenue_2024,
  ROUND((a.total_revenue_b - b.total_revenue_b)
    / b.total_revenue_b * 100, 1) AS rev_growth_pct
FROM cruise_data a
JOIN cruise_data b
  ON a.company = b.company
WHERE a.year = 2024
  AND b.year = 2019
ORDER BY rev_growth_pct DESC;

-- Revenue per passenger (efficiency metric)
SELECT
  year,
  company,
  total_revenue_b,
  passengers_m,
  ROUND(total_revenue_b / passengers_m, 3)
    AS rev_per_passenger_b,
  adj_ebitda_b,
  ROUND(adj_ebitda_b / total_revenue_b * 100, 1)
    AS ebitda_margin_pct
FROM cruise_data
WHERE year IN (2019, 2022, 2023, 2024)
ORDER BY year, company;

-- Avg revenue per passenger by company
SELECT company,
  ROUND(AVG(total_revenue_b / passengers_m), 3)
    AS avg_rev_per_passenger
FROM cruise_data
WHERE year BETWEEN 2022 AND 2024
GROUP BY company;

-- When did each company hit 100% occupancy?
SELECT company,
  MIN(year) AS first_year_above_100pct
FROM cruise_data
WHERE occupancy_pct >= 100
  AND year > 2020
GROUP BY company;

-- Full occupancy trend
SELECT year, company, occupancy_pct,
  CASE
    WHEN occupancy_pct >= 100 THEN 'Fully Recovered'
    WHEN occupancy_pct >= 80  THEN 'Near Recovery'
    WHEN occupancy_pct >= 50  THEN 'Ramping Up'
    ELSE 'Severely Impacted'
  END AS recovery_status
FROM cruise_data
ORDER BY company, year;

-- YoY growth using LAG window function
SELECT
  year, company,
  passengers_m,
  LAG(passengers_m) OVER (
    PARTITION BY company ORDER BY year
  ) AS prev_year_pax,
  ROUND(
    (passengers_m - LAG(passengers_m) OVER (
      PARTITION BY company ORDER BY year)
    ) / LAG(passengers_m) OVER (
      PARTITION BY company ORDER BY year
    ) * 100, 1
  ) AS yoy_pax_growth_pct,
  total_revenue_b,
  adj_ebitda_b
FROM cruise_data
ORDER BY company, year;

Phase 5 — Share

Key Findings

Four data-driven insights from the 2019–2024 cruise industry analysis.

Phase 6 — Act

Recommendations

Actionable insights for cruise industry stakeholders based on the data analysis.