Audit and onboard unfamiliar datasets safely

You receive hotel search data that is new to you and has little documentation. Give a specific, step-by-step checklist to: a) find tables and columns and check primary and foreign keys; b) examine distributions, missing data, and timezone/locale/currency problems; c) spot duplicates and many-to-many join expansion across searches, impressions, clicks, bookings, and cancellations; d) verify event ordering (search → impression → click → booking → cancellation) using watermarking and late-arrival windows; e) calculate metrics accurately (for example, bookings per search within 7 days; margin = price − cost; GMV versus contribution margin); f) create data quality tests/data contracts (not-null, uniqueness, referential integrity, numeric ranges). Add at least three pitfalls that are particular to travel data (such as multi-room bookings, partial cancellations/modifications, rebookings, cross-currency FX at booking vs. stay date, children vs. adults counts) and how you would detect each.

Overview: This question tests a data scientist's skills in discovering datasets, verifying schemas and keys, profiling distributions/missingness/timezones/currencies, detecting duplicates and join inflation, validating event sequences, computing metrics accurately, and designing data-quality tests/data contracts in SQL/Python workflows.

Search-to-booking funnel metrics: GMV and margin

You receive 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 – count of distinct hotels displayed (impressions) for that search.
2. clicks_count – count of distinct hotels clicked for that search.
3. bookings_within_7d – number of bookings where booking_timestamp falls between search_timestamp and search_timestamp + 7 days (inclusive), and original_search_id equals search_id.
4. gmv_within_7d – total GMV (sum of booking price) for the bookings included in bookings_within_7d.
5. contribution_margin_within_7d – total contribution margin (sum of price − cost) for the bookings counted in bookings_within_7d.

Prevent 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

• Aggregate impressions and clicks per search in separate CTEs beforehand to prevent many-to-many join inflation.
• For bookings within 7 days, join bookings to searches on original_search_id and filter using booking_timestamp BETWEEN search_timestamp AND search_timestamp + INTERVAL '7 days'.

Check event sequencing and late-arriving bookings

With the same hotel funnel tables, produce a SQL query that outputs one row per booking with the following boolean flags:

1. has_search – TRUE when the booking's original_search_id is not null and matches a row in searches.
2. has_click_before_booking – TRUE when at least one click exists for the same search_id and hotel_id with click_timestamp ≤ booking_timestamp.
3. has_impression_before_click – TRUE when at least one impression exists for the same search_id and hotel_id with impression_timestamp ≤ the earliest click_timestamp.
4. events_in_chronological_order – TRUE when the observed sequence follows search_timestamp ≤ impression_timestamp ≤ click_timestamp ≤ booking_timestamp ≤ cancellation_timestamp (if a cancellation exists). If any prerequisite event is absent, consider the missing event as breaking the full ordered-chain condition.
5. is_late_arrival – TRUE when event_ingested_at > booking_timestamp + 2 days (meaning the booking event arrived more than 2 days late).

Return all bookings with these flags, sorted 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

• Use MIN() in a CTE to aggregate to the earliest impression, click, and cancellation per booking.
• Compute the flags by comparing the ordered timestamps and checking event_ingested_at against booking_timestamp + INTERVAL '2 days'.

Identify travel-specific booking pitfalls with data quality flags

With the same dataset, produce a SQL query that outputs one row per booking with boolean flags for at least three travel-specific pitfalls. The output must include:

1. is_multi_room – TRUE when rooms > 1 (multi-room bookings can alter how stays are counted versus room nights).
2. is_partial_cancellation – TRUE when the booking has cancellations where cancelled_rooms is between 1 and rooms − 1 (indicating a partial cancellation).
3. is_rebooking – TRUE when another booking exists for the same user_id, hotel_id, and checkin_date (indicating rebookings or modifications).
4. has_children_more_than_adults – TRUE when children > adults (suspicious guest-count distribution).
5. has_fx_mismatch – TRUE when either currency <> property_currency or the FX rate at booking_date versus 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, sorted 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

• Detect rebookings using a window function (COUNT OVER PARTITION BY user_id, hotel_id, checkin_date).
• Aggregate cancellations by booking_id and join FX rates at booking and check-in dates to compute partial cancellation and FX mismatch flags.