Master data engineering and AI like a big tech engineer

Real Time Formula 1 Analytics

Big Bag Data

Chess.com Analytics

Trading Strategy for Crypto Currency - Analytics Engineering Capstone Submission

BetFlow - Real Time Sports Betting App

Showcase of student projects

Capstone Projects

Our students have gone on to work at companies like Meta, Airbnb and Amazon. As well as achieve 100% raises!

Fast Track Your Career

Immediate free cloud access to Databricks, AWS, Snowflake, Astronomer, and more!

Free Cloud Access with tons of hands on exercises

Weekly Guest Speaker Sessions

Why Choose DataExpert.io Academy?

A themed six-round mock interview simulating a real Airbnb Analytics Engineer loop. Covers marketplace SQL analytics (funnels, cohorts, occupancy), dimensional modeling for a two-sided marketplace, experimentation platform architecture, behavioral questions around Airbnb's "Belong Anywhere" culture, and a Python problem themed around search ranking data processing.

Airbnb Analytics Engineer Interview

Airbnb tracks the guest booking funnel in a table called `funnel_events`:

  funnel_events (
    event_id BIGINT PRIMARY KEY,
    user_id BIGINT,
    session_id VARCHAR(64),
    event_type VARCHAR(30),   -- 'search', 'view_listing', 'booking_request', 'booking_confirmed'
    market VARCHAR(50),       -- e.g. 'San Francisco', 'Paris', 'Tokyo'
    listing_id BIGINT NULL,
    event_timestamp TIMESTAMP
  )

Write a SQL query that computes the weekly funnel conversion rates for each market over the last 12 weeks:
- search → view_listing rate
- view_listing → booking_request rate
- booking_request → booking_confirmed rate

Output: week_start, market, searches, views, requests, confirmations, search_to_view_rate, view_to_request_rate, request_to_confirm_rate

A session counts toward a stage if it has at least one event of that type. Order by week_start DESC, market ASC.

**Expected Output:**

| Column | Type |
|--------|------|
| week_start | TEXT (YYYY-MM-DD, Monday) |
| market | TEXT |
| searches | INTEGER |
| views | INTEGER |
| requests | INTEGER |
| confirmations | INTEGER |
| search_to_view_rate | REAL (0.0 to 1.0, 4 decimal places) |
| view_to_request_rate | REAL (0.0 to 1.0, 4 decimal places) |
| request_to_confirm_rate | REAL (0.0 to 1.0, 4 decimal places) |

Order by `week_start` DESC, `market` ASC.

Use 0.0000 when the denominator is zero (not NULL).

Guest Booking Funnel Conversion by Market — Airbnb

Airbnb's Superhost program evaluates hosts quarterly. The qualification criteria are:

1. At least 10 completed stays OR 100 reservation nights in the trailing 12 months
2. Overall review rating ≥ 4.8 (across all reviews in the trailing 12 months)
3. Response rate ≥ 90% (responded to inquiries within 24 hours)
4. Cancellation rate < 1% (host-initiated cancellations / total reservations)

Tables:

  reservations (reservation_id, host_id, guest_id, listing_id, check_in DATE, check_out DATE, status VARCHAR(20), cancelled_by VARCHAR(10) NULL, created_at TIMESTAMP)
  -- status: 'completed', 'cancelled', 'upcoming'
  -- cancelled_by: 'host', 'guest', NULL

  reviews (review_id, reservation_id, host_id, rating DECIMAL(2,1), created_at TIMESTAMP)

  inquiries (inquiry_id, host_id, guest_id, sent_at TIMESTAMP, first_response_at TIMESTAMP NULL)

Write a SQL query that returns ALL hosts with columns:
  host_id, completed_stays, reservation_nights, avg_rating, response_rate_pct, cancellation_rate_pct, qualifies_superhost (BOOLEAN)

Evaluation window: trailing 12 months from CURRENT_DATE.

**Expected Output:**

| Column | Type |
|--------|------|
| host_id | INTEGER |
| completed_stays | INTEGER |
| reservation_nights | INTEGER |
| avg_rating | REAL (2 decimal places, NULL if no reviews) |
| response_rate_pct | REAL (percentage 0-100, 2 decimal places, NULL if no inquiries) |
| cancellation_rate_pct | REAL (percentage 0-100, 2 decimal places) |
| qualifies_superhost | INTEGER (1 = yes, 0 = no) |

Return ALL hosts (even those who don't qualify). Order by `host_id` ASC.

A host with no reviews has NULL avg_rating and does NOT qualify.
A host with no inquiries has NULL response_rate_pct and is treated as meeting the 90% threshold.

Host Superhost Qualification Logic — Airbnb

Fact tables at different grains (fact_booking at reservation level, fact_availability at listing-day level, fact_search at session level), conformed dimensions (dim_listing, dim_guest, dim_host, dim_date, dim_market), clear grain labels, experiment assignment as a mini-dimension or outrigger, bridge table for listing amenities.

Airbnb Marketplace Metrics Dimensional Model

Event collection (Kafka), assignment store (low-latency KV like DynamoDB), metric computation layer (Spark/Airflow reading Minerva definitions), pre-aggregation into experiment-variant-metric cubes, statistical engine (Python/R service), overlap detection via user-set intersection, versioned metric store for backfill, dashboard layer (internal tool or Looker/Tableau).

Airbnb Experimentation Platform Data Architecture

Concrete example with metric specifics, evidence of thinking about both sides of a marketplace, use of data to support the argument (not just intuition), demonstrates long-term thinking, shows stakeholder management skills.

Airbnb Culture: Championing the Mission Under Pressure

Proactive exploration (not just answering a ticket), shows deep curiosity, non-trivial insight (not just "we found a bug"), effective storytelling to PMs/leadership, concrete product change that resulted, demonstrates the Airbnb value of being a "cereal entrepreneur" — going above and beyond role definition.

Airbnb Culture: Data Insight That Changed a Product Decision

Airbnb's search ranking uses a feature called "host_response_probability" — 
the estimated likelihood that a host will accept a booking request, given
historical patterns.

You are given:
  inquiries: List[Dict] with keys:
    - host_id: str
    - guest_id: str
    - listing_id: str  
    - sent_at: int (unix timestamp)
    - responded: bool (True if host accepted/responded within 24h)
    - guest_reviews: int (number of reviews the guest had at inquiry time)
    - is_instant_book: bool

Implement:

```python
def compute_host_response_features(
    inquiries: List[Dict],
    target_date: int  # unix timestamp of the scoring date
) -> Dict[str, Dict[str, float]]:
    """
    For each host active in the last 90 days, compute:
    {
      host_id: {
        "overall_response_rate": float,       # lifetime acceptance rate
        "recent_response_rate": float,        # last 30 days only
        "new_guest_response_rate": float,     # rate for guests with 0 reviews
        "experienced_guest_response_rate": float,  # rate for guests with 3+ reviews
        "response_rate_trend": float,         # (last 30 days rate) - (31-90 days rate)
        "instant_book_ratio": float,          # % of their listings that are instant book
      }
    }
    
    Rules:
    - "Active" = at least 1 inquiry in the last 90 days from target_date
    - If a host has < 3 inquiries for a sub-metric, return -1.0 (insufficient data)
    - All rates are between 0.0 and 1.0
    """
```

Constraints:
- Up to 5 million inquiries
- Up to 500K unique hosts
- Optimize for single-pass computation where possible

Search Ranking Feature: Host Response Probability — Airbnb

Interval scheduling problem with constraints — greedy approach sorting by revenue density and checking capacity at each time point, or DP with capacity tracking. Must correctly model per-listing non-overlap AND global capacity constraint. Clean date parsing, sweep-line for overlap detection.

Airbnb Analytics Engineer Interview

In order. Round N+1 unlocks when Round N passes.