Quick Overview

This question evaluates a data scientist's competency in dataset discovery, schema and key verification, profiling for distributions/missingness/timezones/currencies, duplicate and join-inflation detection, event-sequence validation, accurate metric computation, and the design of data-quality tests/data contracts within SQL/Python workflows.

Audit and onboard unfamiliar datasets safely

Company: Expedia

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Onsite

You inherit unfamiliar hotel search data with sparse documentation. Provide a concrete, ordered checklist to: a) discover tables/columns and verify primary/foreign keys; b) profile distributions, missingness, and timezones/locale/currency issues; c) detect duplicates and many-to-many join inflation across searches, impressions, clicks, bookings, and cancellations; d) validate event sequencing (search → impression → click → booking → cancellation) with watermarking and late-arrival windows; e) compute metrics correctly (e.g., bookings per search within 7 days; margin = price − cost; GMV vs. contribution margin); f) write data quality tests/data contracts (not-null, uniqueness, referential integrity, numeric ranges). Include at least three pitfalls specific to travel data (e.g., multi-room bookings, partial cancellations/modifications, rebookings, cross-currency FX at booking vs. stay date, children vs. adults counts) and how you’d detect each.

Overview: This question evaluates a data scientist's competency in dataset discovery, schema and key verification, profiling for distributions/missingness/timezones/currencies, duplicate and join-inflation detection, event-sequence validation, accurate metric computation, and the design of data-quality tests/data contracts within SQL/Python workflows.

Read the full Expedia Data Scientist interview experience this question came from

Search-to-booking funnel metrics with GMV and margin

You inherit a hotel search dataset with separate event tables for searches, impressions, clicks, bookings, and cancellations. Using the schema and sample data below, write a SQL query that returns **one row per search** with the following metrics: 1. impressions_count – number of distinct hotels shown (impressions) for that search. 2. clicks_count – number of distinct hotels clicked for that search. 3. bookings_within_7d – number of bookings whose booking_timestamp is between the search_timestamp and search_timestamp + 7 days (inclusive), and whose original_search_id = search_id. 4. gmv_within_7d – total GMV (sum of booking price) for bookings counted in bookings_within_7d. 5. contribution_margin_within_7d – total contribution margin (sum of price − cost) for those bookings. Avoid many-to-many join inflation when combining impressions, clicks, and bookings. Return all searches, even if some metrics are zero.

Tables

searches(search_id INT, user_id INT, search_timestamp TIMESTAMP, checkin_date DATE, checkout_date DATE, num_adults INT, num_children INT, currency VARCHAR(3), locale VARCHAR(5))

impressions(impression_id INT, search_id INT, hotel_id INT, impression_timestamp TIMESTAMP, position INT)

clicks(click_id INT, search_id INT, hotel_id INT, click_timestamp TIMESTAMP)

bookings(booking_id INT, original_search_id INT, hotel_id INT, user_id INT, booking_timestamp TIMESTAMP, checkin_date DATE, checkout_date DATE, rooms INT, adults INT, children INT, currency VARCHAR(3), price DECIMAL(10,2), cost DECIMAL(10,2), property_currency VARCHAR(3), event_ingested_at TIMESTAMP)

cancellations(cancellation_id INT, booking_id INT, cancellation_timestamp TIMESTAMP, cancelled_rooms INT, refund_amount DECIMAL(10,2))

fx_rates(currency VARCHAR(3), rate_date DATE, rate_to_usd DECIMAL(10,4))

Hints

  1. Pre-aggregate impressions and clicks per search in separate CTEs to avoid many-to-many join inflation.
  2. To compute bookings within 7 days, join bookings to searches on original_search_id and filter by booking_timestamp BETWEEN search_timestamp AND search_timestamp + INTERVAL '7 days'.

Validate event sequencing and late-arriving bookings

Using the same hotel funnel tables, write a SQL query that returns **one row per booking** with the following boolean flags: 1. has_search – TRUE if the booking has a non-null original_search_id that matches a row in searches. 2. has_click_before_booking – TRUE if there is at least one click for the same search_id and hotel_id with click_timestamp ≤ booking_timestamp. 3. has_impression_before_click – TRUE if there is at least one impression for the same search_id and hotel_id with impression_timestamp ≤ the (earliest) click_timestamp. 4. events_in_chronological_order – TRUE if the observed sequence is ordered as search_timestamp ≤ impression_timestamp ≤ click_timestamp ≤ booking_timestamp ≤ cancellation_timestamp (if a cancellation exists). If any prerequisite event is missing, treat the missing event as breaking the full ordered-chain condition. 5. is_late_arrival – TRUE if event_ingested_at > booking_timestamp + 2 days (i.e., the booking event arrived more than 2 days late). Return all bookings with these flags, ordered by booking_id.

Tables

searches(search_id INT, user_id INT, search_timestamp TIMESTAMP, checkin_date DATE, checkout_date DATE, num_adults INT, num_children INT, currency VARCHAR(3), locale VARCHAR(5))

impressions(impression_id INT, search_id INT, hotel_id INT, impression_timestamp TIMESTAMP, position INT)

clicks(click_id INT, search_id INT, hotel_id INT, click_timestamp TIMESTAMP)

bookings(booking_id INT, original_search_id INT, hotel_id INT, user_id INT, booking_timestamp TIMESTAMP, checkin_date DATE, checkout_date DATE, rooms INT, adults INT, children INT, currency VARCHAR(3), price DECIMAL(10,2), cost DECIMAL(10,2), property_currency VARCHAR(3), event_ingested_at TIMESTAMP)

cancellations(cancellation_id INT, booking_id INT, cancellation_timestamp TIMESTAMP, cancelled_rooms INT, refund_amount DECIMAL(10,2))

Hints

  1. Aggregate to the earliest impression, click, and cancellation per booking using MIN() in a CTE.
  2. Derive the flags by comparing the ordered timestamps and checking event_ingested_at against booking_timestamp + INTERVAL '2 days'.

Detect travel-specific booking pitfalls with data quality flags

Using the same dataset, write a SQL query that returns **one row per booking** with boolean flags for at least three travel-specific pitfalls. Your output should include: 1. is_multi_room – TRUE if rooms > 1 (multi-room bookings can change how you count stays vs. room nights). 2. is_partial_cancellation – TRUE if the booking has cancellations with cancelled_rooms between 1 and rooms − 1 (indicating a partial cancellation). 3. is_rebooking – TRUE if there exists another booking for the same user_id, hotel_id, and checkin_date (indicating rebookings or modifications). 4. has_children_more_than_adults – TRUE if children > adults (suspicious guest-count distribution). 5. has_fx_mismatch – TRUE if either currency <> property_currency or the FX rate at booking_date vs. checkin_date implies more than a 5% change in the USD value of the booking (highlighting cross-currency/FX issues). Return booking_id and these five flags for all bookings, ordered by booking_id.

Tables

bookings(booking_id INT, original_search_id INT, hotel_id INT, user_id INT, booking_timestamp TIMESTAMP, checkin_date DATE, checkout_date DATE, rooms INT, adults INT, children INT, currency VARCHAR(3), price DECIMAL(10,2), cost DECIMAL(10,2), property_currency VARCHAR(3), event_ingested_at TIMESTAMP)

cancellations(cancellation_id INT, booking_id INT, cancellation_timestamp TIMESTAMP, cancelled_rooms INT, refund_amount DECIMAL(10,2))

fx_rates(currency VARCHAR(3), rate_date DATE, rate_to_usd DECIMAL(10,4))

Hints

  1. Use a window function (COUNT OVER PARTITION BY user_id, hotel_id, checkin_date) to detect rebookings.
  2. Aggregate cancellations by booking_id and join FX rates at booking and check-in dates to derive partial cancellation and FX mismatch flags.

Loading coding console...