Airbnb Senior Data Analyst Interview Questions and Answers 2026

WITH booked_nights AS (
    SELECT
        l.city,
        SUM(
            DATEDIFF('day', b.checkin_date, b.checkout_date)
        ) AS total_booked
    FROM bookings b
    JOIN listings l ON b.listing_id = l.listing_id
    WHERE b.status = 'confirmed'
      AND b.checkin_date >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY l.city
),
available_nights AS (
    SELECT
        city,
        SUM(available_days_per_month * 3) AS total_available  -- 3 months
    FROM listings
    GROUP BY city
)
SELECT
    a.city,
    b.total_booked,
    a.total_available,
    ROUND(
        100.0 * b.total_booked / NULLIF(a.total_available, 0), 1
    ) AS occupancy_rate_pct
FROM available_nights a
LEFT JOIN booked_nights b USING (city)
ORDER BY occupancy_rate_pct DESC NULLS LAST;

WITH host_stats AS (
    SELECT
        h.host_id,
        COUNT(CASE WHEN b.status = 'completed' THEN 1 END)
            AS completed_stays,
        ROUND(100.0 * AVG(CASE WHEN m.responded_within_24h THEN 1.0 ELSE 0 END), 1)
            AS response_rate_pct,
        ROUND(100.0 * AVG(CASE WHEN r.rating = 5 THEN 1.0 ELSE 0 END), 1)
            AS five_star_pct,
        MAX(CASE WHEN b.host_cancelled THEN 1 ELSE 0 END)
            AS has_host_cancellation
    FROM hosts h
    LEFT JOIN bookings  b ON b.host_id  = h.host_id
    LEFT JOIN messages  m ON m.host_id  = h.host_id
    LEFT JOIN reviews   r ON r.host_id  = h.host_id
    WHERE COALESCE(b.created_at, m.sent_at, r.created_at)
          >= CURRENT_DATE - INTERVAL '1 year'
    GROUP BY h.host_id
)
SELECT
    host_id,
    completed_stays,
    response_rate_pct,
    five_star_pct,
    CASE
        WHEN completed_stays       >= 10
         AND response_rate_pct     >= 90
         AND five_star_pct         >= 80
         AND has_host_cancellation  = 0
        THEN 'Eligible'
        ELSE 'Not Eligible'
    END AS superhost_status
FROM host_stats
ORDER BY completed_stays DESC;

WITH listing_revenue AS (
    SELECT
        l.city,
        b.listing_id,
        SUM(b.total_price) AS total_revenue,
        DENSE_RANK() OVER (
            PARTITION BY l.city
            ORDER BY SUM(b.total_price) DESC
        ) AS rev_rank
    FROM bookings b
    JOIN listings l ON b.listing_id = l.listing_id
    WHERE b.status = 'confirmed'
    GROUP BY l.city, b.listing_id
)
SELECT city, listing_id, total_revenue
FROM listing_revenue
WHERE rev_rank <= 3;

WITH first_bookings AS (
    SELECT
        l.host_id,
        l.listing_type,
        l.created_at                       AS listed_at,
        MIN(b.booked_at)                   AS first_booked_at
    FROM listings l
    LEFT JOIN bookings b
        ON  b.listing_id = l.listing_id
        AND b.status     = 'confirmed'
    GROUP BY l.host_id, l.listing_type, l.created_at
)
SELECT
    listing_type,
    COUNT(*)                                         AS total_hosts,
    COUNT(first_booked_at)                           AS hosts_with_booking,
    ROUND(AVG(
        DATEDIFF('day', listed_at, first_booked_at)
    ), 1)                                            AS avg_days_to_first_booking
FROM first_bookings
WHERE first_booked_at IS NOT NULL
GROUP BY listing_type
ORDER BY avg_days_to_first_booking;

WITH guest_booking_gaps AS (
    SELECT
        guest_id,
        listing_id,
        checkin_date,
        LAG(checkin_date) OVER (
            PARTITION BY guest_id, listing_id
            ORDER BY checkin_date
        ) AS prev_checkin
    FROM bookings
    WHERE status = 'confirmed'
)
SELECT DISTINCT
    guest_id,
    listing_id,
    COUNT(*) OVER (PARTITION BY guest_id, listing_id) AS booking_count
FROM guest_booking_gaps
WHERE DATEDIFF('day', prev_checkin, checkin_date) <= 30
ORDER BY booking_count DESC;

import pandas as pd
from statsmodels.tsa.seasonal import seasonal_decompose
import matplotlib.pyplot as plt

# Weekly bookings indexed by date
df = df.set_index("week_start").sort_index()
ts = df["booking_count"]

# Decompose — multiplicative handles growing seasonal amplitude
result = seasonal_decompose(ts, model="multiplicative", period=52)

# Summary stats per component
print("Trend variance (pct of total):",
      round(result.trend.var() / ts.var() * 100, 1), "%")
print("Seasonal variance (pct of total):",
      round(result.seasonal.var() / ts.var() * 100, 1), "%")

# Peak season weeks
seasonal_idx = pd.Series(result.seasonal.values, index=ts.index)
peak_weeks = seasonal_idx.nlargest(5)
print("Peak demand weeks:\n", peak_weeks)

from statsmodels.stats.proportion import proportions_ztest
import numpy as np

n = 50_000
conv_ctrl = 0.028
conv_var  = 0.031

counts = np.array([conv_var * n, conv_ctrl * n])
nobs   = np.array([n, n])

z, p = proportions_ztest(counts, nobs, alternative='larger')
print(f"Z-stat  : {z:.3f}")
print(f"P-value : {p:.4f}")
print(f"Result  : {'Significant ✓' if p < 0.05 else 'Not Significant ✗'}")

# Relative lift
lift = (conv_var - conv_ctrl) / conv_ctrl * 100
print(f"Relative lift: {lift:.1f}%")

# Practical significance check
# At Airbnb scale: 1% lift on 100M searches = ~$50M ARR
print(f"Business impact: {lift:.1f}% lift at Airbnb scale is material")

import pandas as pd
import numpy as np
from sklearn.linear_model import LinearRegression

# Log-log regression: ln(demand) ~ ln(price) + controls
df["log_bookings"] = np.log(df["monthly_bookings"] + 1)
df["log_price"]    = np.log(df["price_per_night"])

features = ["log_price", "num_reviews", "superhost_flag",
            "room_type_encoded", "distance_to_centre_km"]

X = df[features]
y = df["log_bookings"]

model = LinearRegression().fit(X, y)

elasticity = model.coef_[0]  # coefficient on log_price
print(f"Price elasticity: {elasticity:.3f}")
print(f"A 10% price increase → {elasticity * 10:.1f}% change in bookings")
print(f"Demand is {'elastic' if abs(elasticity) > 1 else 'inelastic'}")

stats = (
    df.groupby("host_tier")["response_hours"]
      .agg(
          median  = lambda x: x.quantile(0.50),
          p75     = lambda x: x.quantile(0.75),
          p95     = lambda x: x.quantile(0.95),
          slow_pct= lambda x: (x > 24).mean() * 100
      )
      .round(1)
      .reset_index()
)
print(stats)

-- Composite Host Quality Score (0–100)
WITH host_signals AS (
    SELECT
        h.host_id,
        -- Normalise each signal to 0-1
        LEAST(completed_stays / 50.0, 1.0)          AS experience_score,   -- caps at 50 stays
        response_rate_pct / 100.0                    AS response_score,
        five_star_pct / 100.0                        AS review_score,
        CASE WHEN host_cancelled_count = 0 THEN 1.0
             ELSE GREATEST(0, 1 - host_cancelled_count * 0.2) END
                                                     AS reliability_score,
        LEAST(DATEDIFF('day', joined_at, CURRENT_DATE) / 365.0, 3) / 3.0
                                                     AS tenure_score       -- caps at 3 years
    FROM host_stats h
)
SELECT
    host_id,
    ROUND(
        100 * (
            0.30 * review_score       +  -- guest satisfaction
            0.25 * response_score     +  -- responsiveness
            0.20 * reliability_score  +  -- no cancellations
            0.15 * experience_score   +  -- track record
            0.10 * tenure_score          -- platform trust
        ), 1
    ) AS quality_score
FROM host_signals
ORDER BY quality_score DESC;