cswork / libraryOpen practice workspace

Coding questions · Expedia

Audit and onboard unfamiliar datasets safely

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.

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.

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.

Table searches

columntypeconstraints
search_idINTPRIMARY KEY
user_idINTNOT NULL
search_timestampTIMESTAMPNOT NULL
checkin_dateDATENOT NULL
checkout_dateDATENOT NULL
num_adultsINTNOT NULL
num_childrenINTNOT NULL
currencyVARCHAR(3)NOT NULL
localeVARCHAR(5)NOT NULL

Sample data:

checkin_datecheckout_datecurrencylocalenum_adultsnum_childrensearch_idsearch_timestampuser_id
2025-05-102025-05-12USDen-US2012025-05-01 10:00:00101
2025-05-202025-05-22EURde-DE2122025-05-02 09:00:00102
2025-05-252025-05-28GBPen-GB1232025-05-03 20:00:00101
2025-06-152025-06-18USDen-US2042025-05-01 08:00:00103

Table impressions

columntypeconstraints
impression_idINTPRIMARY KEY
search_idINTNOT NULL
hotel_idINTNOT NULL
impression_timestampTIMESTAMPNOT NULL
positionINTNOT NULL

Sample data:

hotel_idimpression_idimpression_timestamppositionsearch_id
1012025-05-01 10:01:0011
1122025-05-01 10:01:3021
2032025-05-02 09:01:0012
2142025-05-02 09:01:3022
2052025-05-02 09:02:0032
3062025-05-03 20:05:0013
4072025-05-01 08:01:0014
1082025-05-01 10:03:0031

Table clicks

columntypeconstraints
click_idINTPRIMARY KEY
search_idINTNOT NULL
hotel_idINTNOT NULL
click_timestampTIMESTAMPNOT NULL

Sample data:

click_idclick_timestamphotel_idsearch_id
12025-05-01 10:02:00101
22025-05-02 09:05:00202
32025-05-02 09:06:00202
42025-05-03 22:00:00303
52025-05-01 08:10:00404

Table bookings

columntypeconstraints
booking_idINTPRIMARY KEY
original_search_idINT
hotel_idINTNOT NULL
user_idINTNOT NULL
booking_timestampTIMESTAMPNOT NULL
checkin_dateDATENOT NULL
checkout_dateDATENOT NULL
roomsINTNOT NULL
adultsINTNOT NULL
childrenINTNOT NULL
currencyVARCHAR(3)NOT NULL
priceDECIMAL(10,2)NOT NULL
costDECIMAL(10,2)NOT NULL
property_currencyVARCHAR(3)NOT NULL
event_ingested_atTIMESTAMPNOT NULL

Sample data:

adultsbooking_idbooking_timestampcheckin_datecheckout_datechildrencostcurrencyevent_ingested_athotel_idoriginal_search_idpriceproperty_currencyroomsuser_id
250012025-05-01 10:05:002025-05-102025-05-120250USD2025-05-01 10:06:00101300USD1101
250022025-05-10 12:00:002025-05-202025-05-221480EUR2025-05-13 12:00:00202600USD2102
150032025-05-03 21:00:002025-05-252025-05-282300GBP2025-05-03 21:01:00303400GBP1101
250042025-05-01 08:20:002025-06-152025-06-180350USD2025-05-05 09:00:00404450USD1103
250052025-05-02 09:00:002025-06-152025-06-180360USD2025-05-02 09:01:00404460USD1103
250062025-05-05 12:00:002025-06-012025-06-030150USD2025-05-05 12:00:0050NULL200USD1104

Table cancellations

columntypeconstraints
cancellation_idINTPRIMARY KEY
booking_idINTNOT NULL
cancellation_timestampTIMESTAMPNOT NULL
cancelled_roomsINTNOT NULL
refund_amountDECIMAL(10,2)NOT NULL

Sample data:

booking_idcancellation_idcancellation_timestampcancelled_roomsrefund_amount
500290012025-05-15 10:00:001280
500490022025-05-02 08:00:001450

Table fx_rates

columntypeconstraints
currencyVARCHAR(3)NOT NULL
rate_dateDATENOT NULL
rate_to_usdDECIMAL(10,4)NOT NULL

Sample data:

currencyrate_daterate_to_usd
EUR2025-05-101.1
EUR2025-05-201.2
GBP2025-05-031.3
GBP2025-05-251.25
USD2025-05-011
USD2025-05-021
USD2025-05-051
USD2025-05-101
USD2025-06-011
USD2025-06-151

Expected output

One row per search with counts of distinct hotels seen and clicked, and booking/GMV/margin metrics for bookings within 7 days of the search.

bookings_within_7dclicks_countcontribution_margin_within_7dgmv_within_7dimpressions_countsearch_id
115030021
010022
1110040013
2120091014

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.

Table searches

columntypeconstraints
search_idINTPRIMARY KEY
user_idINTNOT NULL
search_timestampTIMESTAMPNOT NULL
checkin_dateDATENOT NULL
checkout_dateDATENOT NULL
num_adultsINTNOT NULL
num_childrenINTNOT NULL
currencyVARCHAR(3)NOT NULL
localeVARCHAR(5)NOT NULL

Sample data:

checkin_datecheckout_datecurrencylocalenum_adultsnum_childrensearch_idsearch_timestampuser_id
2025-05-102025-05-12USDen-US2012025-05-01 10:00:00101
2025-05-202025-05-22EURde-DE2122025-05-02 09:00:00102
2025-05-252025-05-28GBPen-GB1232025-05-03 20:00:00101
2025-06-152025-06-18USDen-US2042025-05-01 08:00:00103

Table impressions

columntypeconstraints
impression_idINTPRIMARY KEY
search_idINTNOT NULL
hotel_idINTNOT NULL
impression_timestampTIMESTAMPNOT NULL
positionINTNOT NULL

Sample data:

hotel_idimpression_idimpression_timestamppositionsearch_id
1012025-05-01 10:01:0011
1122025-05-01 10:01:3021
2032025-05-02 09:01:0012
2142025-05-02 09:01:3022
2052025-05-02 09:02:0032
3062025-05-03 20:05:0013
4072025-05-01 08:01:0014
1082025-05-01 10:03:0031

Table clicks

columntypeconstraints
click_idINTPRIMARY KEY
search_idINTNOT NULL
hotel_idINTNOT NULL
click_timestampTIMESTAMPNOT NULL

Sample data:

click_idclick_timestamphotel_idsearch_id
12025-05-01 10:02:00101
22025-05-02 09:05:00202
32025-05-02 09:06:00202
42025-05-03 22:00:00303
52025-05-01 08:10:00404

Table bookings

columntypeconstraints
booking_idINTPRIMARY KEY
original_search_idINT
hotel_idINTNOT NULL
user_idINTNOT NULL
booking_timestampTIMESTAMPNOT NULL
checkin_dateDATENOT NULL
checkout_dateDATENOT NULL
roomsINTNOT NULL
adultsINTNOT NULL
childrenINTNOT NULL
currencyVARCHAR(3)NOT NULL
priceDECIMAL(10,2)NOT NULL
costDECIMAL(10,2)NOT NULL
property_currencyVARCHAR(3)NOT NULL
event_ingested_atTIMESTAMPNOT NULL

Sample data:

adultsbooking_idbooking_timestampcheckin_datecheckout_datechildrencostcurrencyevent_ingested_athotel_idoriginal_search_idpriceproperty_currencyroomsuser_id
250012025-05-01 10:05:002025-05-102025-05-120250USD2025-05-01 10:06:00101300USD1101
250022025-05-10 12:00:002025-05-202025-05-221480EUR2025-05-13 12:00:00202600USD2102
150032025-05-03 21:00:002025-05-252025-05-282300GBP2025-05-03 21:01:00303400GBP1101
250042025-05-01 08:20:002025-06-152025-06-180350USD2025-05-05 09:00:00404450USD1103
250052025-05-02 09:00:002025-06-152025-06-180360USD2025-05-02 09:01:00404460USD1103
250062025-05-05 12:00:002025-06-012025-06-030150USD2025-05-05 12:00:0050NULL200USD1104

Table cancellations

columntypeconstraints
cancellation_idINTPRIMARY KEY
booking_idINTNOT NULL
cancellation_timestampTIMESTAMPNOT NULL
cancelled_roomsINTNOT NULL
refund_amountDECIMAL(10,2)NOT NULL

Sample data:

booking_idcancellation_idcancellation_timestampcancelled_roomsrefund_amount
500290012025-05-15 10:00:001280
500490022025-05-02 08:00:001450

Expected output

One row per booking with flags describing whether the search, impression, click, booking, and cancellation events are present in order, and whether the booking arrived more than 2 days late.

booking_idevents_in_chronological_orderhas_click_before_bookinghas_impression_before_clickhas_searchis_late_arrival
5001truetruetruetruefalse
5002truetruetruetruetrue
5003falsefalsetruetruefalse
5004truetruetruetruetrue
5005truetruetruetruefalse
5006falsefalsefalsefalsefalse

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.

Table bookings

columntypeconstraints
booking_idINTPRIMARY KEY
original_search_idINT
hotel_idINTNOT NULL
user_idINTNOT NULL
booking_timestampTIMESTAMPNOT NULL
checkin_dateDATENOT NULL
checkout_dateDATENOT NULL
roomsINTNOT NULL
adultsINTNOT NULL
childrenINTNOT NULL
currencyVARCHAR(3)NOT NULL
priceDECIMAL(10,2)NOT NULL
costDECIMAL(10,2)NOT NULL
property_currencyVARCHAR(3)NOT NULL
event_ingested_atTIMESTAMPNOT NULL

Sample data:

adultsbooking_idbooking_timestampcheckin_datecheckout_datechildrencostcurrencyevent_ingested_athotel_idoriginal_search_idpriceproperty_currencyroomsuser_id
250012025-05-01 10:05:002025-05-102025-05-120250USD2025-05-01 10:06:00101300USD1101
250022025-05-10 12:00:002025-05-202025-05-221480EUR2025-05-13 12:00:00202600USD2102
150032025-05-03 21:00:002025-05-252025-05-282300GBP2025-05-03 21:01:00303400GBP1101
250042025-05-01 08:20:002025-06-152025-06-180350USD2025-05-05 09:00:00404450USD1103
250052025-05-02 09:00:002025-06-152025-06-180360USD2025-05-02 09:01:00404460USD1103
250062025-05-05 12:00:002025-06-012025-06-030150USD2025-05-05 12:00:0050NULL200USD1104

Table cancellations

columntypeconstraints
cancellation_idINTPRIMARY KEY
booking_idINTNOT NULL
cancellation_timestampTIMESTAMPNOT NULL
cancelled_roomsINTNOT NULL
refund_amountDECIMAL(10,2)NOT NULL

Sample data:

booking_idcancellation_idcancellation_timestampcancelled_roomsrefund_amount
500290012025-05-15 10:00:001280
500490022025-05-02 08:00:001450

Table fx_rates

columntypeconstraints
currencyVARCHAR(3)NOT NULL
rate_dateDATENOT NULL
rate_to_usdDECIMAL(10,4)NOT NULL

Sample data:

currencyrate_daterate_to_usd
EUR2025-05-101.1
EUR2025-05-201.2
GBP2025-05-031.3
GBP2025-05-251.25
USD2025-05-011
USD2025-05-021
USD2025-05-051
USD2025-05-101
USD2025-06-011
USD2025-06-151

Expected output

One row per booking with flags for multi-room bookings, partial cancellations, rebookings, suspicious children/adults counts, and FX/currency mismatches.

booking_idhas_children_more_than_adultshas_fx_mismatchis_multi_roomis_partial_cancellationis_rebooking
5001falsefalsefalsefalsefalse
5002falsetruetruetruefalse
5003truefalsefalsefalsefalse
5004falsefalsefalsefalsetrue
5005falsefalsefalsefalsetrue
5006falsefalsefalsefalsefalse

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.