A Data Scientist at Make My Trip operates at the intersection of massive scale and high-velocity consumer decision-making. As a leader in the travel technology space, Make My Trip relies on data to personalize user journeys, optimize pricing models, and refine the search-to-booking funnel. You will be tasked with transforming raw behavioral data into actionable product features that directly impact millions of users across flights, hotels, and holiday packages.
This role is inherently product-focused. You will not just be building models; you will be identifying business bottlenecks, designing experiments to validate hypotheses, and ensuring that every algorithm deployed improves the bottom line. The complexity here lies in the travel domain—where seasonality, inventory constraints, and user intent require sophisticated statistical rigor and creative engineering. You will collaborate closely with product managers and engineers to build systems that define the future of online travel.
Screening Round
reportedRounds outside the standard loop often open with something deliberately under-specified: a loose business problem, an open question about a product area, a dataset described in one sentence. The common failure is surveying, listing six plausible approaches and committing to none of them. The thing that separates a strong answer is scoping out loud. State what you are treating as the goal, name the metric you would move, say what you are choosing not to do and why, then take one path through to an actual answer. An interviewer can follow you down a narrow path. Nobody can grade a menu.
What to demonstrate
- Whether you turn an ambiguous prompt into a stated question with a measurable outcome before doing any work
- The judgement visible in what you cut, and whether you say why you cut it rather than silently dropping it
- Whether you land on a concrete recommendation with its caveat attached, rather than an unranked set of options
How to prepare
- Take three vague prompts, such as 'is this feature working', 'why did retention drop', and 'should we expand into a new segment'. For each, write one sentence of goal, one primary metric with its window, and two things you are explicitly not doing.
- Practise giving the recommendation first and the reasoning second, in five minutes. Loosely defined rounds are usually time-boxed, and an answer that arrives last often does not arrive.
- Keep a running assumption list as you talk, on paper or in the shared doc, so the interviewer can challenge one assumption instead of your whole answer.
Technical Assessments
reportedThis round decides whether someone can hand you a schema and a question and trust the number that comes back. Correctness under a clock is the bar, not clever syntax. The habit that separates strong from weak answers is checking the grain: after every join, know how many rows you expect and whether the count moved. Most wrong answers in this format are not wrong logic, they are a fan-out from a key that turned out not to be unique, or a filter applied before an aggregate when it belonged after. Say what you expect before you run it.
What to demonstrate
- Whether your row counts survive each join, and whether you notice on your own when they do not
- Deliberate handling of rows that fail to match, including whether the question needs an inner join or a left join with the non-matches kept and counted
- Whether NULLs are treated on purpose, given that a NULL compares equal to nothing and that COUNT of a column skips it
- Reaching a defensible answer inside the window instead of a refined one after it
How to prepare
- Take a two-table schema, write a join that fans out on purpose, then fix it by collapsing the many-side to one row per key before joining. Repeat until the fix is reflex rather than recall.
- Write a funnel as one query and print the distinct user count at each stage, then confirm each stage is a subset of the one above it rather than assuming it
- Do a few timed runs in a plain text box with no autocomplete and no formatter, since assessment editors often have neither
Deep-Dive Discussions
reportedAn added round often puts you in front of someone outside the core hiring team: a partner engineer, a product owner, a domain expert, sometimes a more senior manager. The question they are really asking is not whether you can do the work but whether they would trust a number that came from you. That changes what a good answer looks like. Lead with what the decision cost and what it changed, keep the method available but not central, and be plain about the limits of your evidence. Overstating a result is the fastest way to lose this round.
What to demonstrate
- Whether you can explain a technical choice to someone who will never read your code, without either flattening it into nothing or hiding inside jargon
- Honesty about evidence strength: what the analysis establishes, what it only suggests, and what it cannot say at all
- How you take disagreement, specifically whether you update on a good objection, hold your position with reasons, or fold on contact
How to prepare
- Write the two-sentence version of your most technical project for a non-specialist, then check that neither sentence needs a method name to make sense.
- For one result you are proud of, write the strongest objection someone could raise and a response that concedes the part of it that is correct.
- Prepare one decision that turned out to be wrong: how you found out, what it cost, and what you changed afterwards. A senior cross-functional interviewer asks for this more often than a technical one does.
PracHub editorial advice for the preparation topics above.
Using the search as the demand unit when the trip is the demand unit
One trip intent generates dozens of searches over days, across devices, mostly while logged out, and the number of searches per intent is a property of the interface rather than of demand. Any product change that encourages comparison or re-sorting inflates the denominator, so look-to-book falls while the product improves, and a change that reduces re-searching raises the metric while nothing about demand moved. The logged-out majority also means traveller_id is NULL for most early-funnel rows, so joining searches to bookings on traveller_id silently drops the part of the funnel you were trying to measure. Collapse on trip_intent_key first, then count, and report searches per intent separately as a diagnostic rather than letting it sit inside a conversion rate.
Attributing a blended rate change to behaviour when the mix moved underneath it
Occupancy, average daily rate, take rate and conversion are all ratios computed over a heterogeneous pool, and their blended values move whenever the composition of markets, property classes, lead times, party sizes or contract types changes, with nothing changing inside any segment. This is Simpson's paradox with a seasonal engine driving it: a shift toward short-lead leisure demand lowers blended ADR and raises blended conversion at the same time, and both movements are mix. Year-over-year comparison does not rescue you by itself, because the calendar does not repeat: day-of-week alignment drifts and moving holidays shift by weeks, so the same ISO week is not the same demand week. Decompose every rate change into a within-segment component and a mix component before writing a sentence about cause, and align year-over-year comparisons on the local market calendar using market_holiday_flag rather than on the date.
Analysing at a different unit than the one randomised
Say out loud what was randomised (user, device, account, cluster) and make the analysis unit match, or account for the clustering with cluster-robust standard errors, the delta method, or aggregation up to the randomised unit. Randomising users and then running a test over sessions understates variance and inflates the false-positive rate.
Extrapolating a first-week lift inflated by novelty effects
Plot the treatment effect by days since first exposure instead of quoting one pooled average. A lift that decays toward zero across the test window is behaviour that will not persist, and annualising it produces a forecast that misses by an order of magnitude.
Choose a category, try a prompt, then open its approach, worked solution or follow-up when you need it.
Implement occupancy, ADR and revenue per sellable night
Two DataFrames: nights (booking_id, stay_date, supply_unit_id, night_status in booked/cancelled/no_show/stayed, unit_revenue_usd, taxes_and_fees_usd) and snap (supply_unit_id, stay_date, snapshot_date, units_available, units_sold_to_date, units_blocked). Implement, per stay_date: occupancy, average daily rate and net revenue per sellable night. Sellable nights are units_sold_to_date + units_available taken from the latest snapshot_date on or before that stay_date, per unit, excluding units_blocked. ADR excludes taxes. Return one frame indexed by stay_date and assert revenue-per-sellable-night equals ADR times occupancy.
Approach
- State the denominator convention out loud before coding: sellable nights exclude units_blocked, physical capacity includes them, and the two diverge sharply for individually-managed supply. Put the chosen convention in a column name or a returned attribute so a reader cannot mistake which one they are looking at.
- Do the as-of pick per (supply_unit_id, stay_date), not globally: filter snap to snapshot_date <= stay_date, sort by snapshot_date, then groupby(['supply_unit_id','stay_date']).tail(1). Taking the single latest snapshot_date across the whole table silently uses one date's view for every stay date and quietly changes the metric.
- Take the numerator only from
nightswith night_status == 'stayed'. units_sold_to_date at the final snapshot exceeds the stayed count by whatever cancels or no-shows afterwards, so reading the numerator off the snapshot overstates occupancy by exactly the late-cancellation rate. - Aggregate as a ratio of sums: sum the numerator and the denominator to stay_date grain and divide once. Averaging per-property occupancies or per-property ADRs is a mean of ratios and breaks the identity, weighting a four-unit property the same as a four-hundred-unit one.
- Assert the identity with np.isclose on relative tolerance: revenue/sellable equals (revenue/stayed) times (stayed/sellable) is exact arithmetic when all three are computed over the same unit-date set, so a failure means the sets differ, usually a unit present in
nightsbut missing from the snapshot.
Follow-up
- Occupancy comes out above 1.0 on three stay dates. Give the two most likely causes and how you would tell them apart.
- Rewrite the denominator on the physical-capacity convention. Which direction does occupancy move, and for which supplier type does the gap get large?
- Now split by property_class. Why can every class's occupancy rise while the blended figure falls?
Expand bookings into stay nights without losing cents
You are given a pandas DataFrame bookings with columns booking_id, check_in_date, check_out_date, nights, unit_revenue_usd (the booking total, two decimal places), about 200,000 rows. Write a function returning stay_nights at one row per booking per stay date, with columns booking_id, stay_date, night_index (1-based) and unit_revenue_usd allocated across the nights so the per-booking sum equals the original to the cent. No per-row Python loop and no apply over rows. State your rule for the departure date.
Approach
- Confirm the grain rule first: a stay night is the arrival date through the night before check_out_date, so nights = (check_out_date - check_in_date).days and the departure date is never a row. Assert this against the stored
nightscolumn and report mismatches rather than trusting either side. - Build the expansion arithmetically instead of materialising date lists: counts = nights.to_numpy(); starts = np.repeat(np.cumsum(counts) - counts, counts); offsets = np.arange(counts.sum()) - starts. That gives a 0-based night offset per output row in one pass, and night_index = offsets + 1.
- Compute stay_date as np.repeat(check_in_date.to_numpy(), counts) + offsets.astype('timedelta64[D]'). This is O(total nights) with no grouping and no Python-level iteration.
- Allocate revenue in integer cents, not floats: cents = np.rint(unit_revenue_usd * 100); base = cents // counts; remainder = cents - base * counts; give the first
remaindernights of each booking one extra cent. Dividing floats by nights leaves sub-cent drift that re-sums to 99.99 or 100.01 on a large fraction of bookings. - Verify by grouping the output back to booking_id and comparing integer cents to the input, then state which downstream numbers depend on this reconciliation (net revenue per sellable night, take rate, contribution margin per stayed night all read the night grain).
Follow-up
- The booking is later amended from three nights to five. What has to happen to the already-written night rows, and which of them may not be rewritten?
- Nights of one booking fall in two calendar months and two tax jurisdictions. Does your equal-split allocation still defend the monthly revenue number, and when would you weight nights by the nightly listed price instead?
Write invariant checks for a rate availability snapshot
The DataFrame snap holds one row per supply_unit_id per forward stay_date per snapshot_date, with units_available, units_sold_to_date, units_blocked, listed_price_usd (nullable), is_closed_to_arrival, min_length_of_stay and lead_time_days. Write check(snap) returning a tidy failure frame with columns check_name, supply_unit_id, stay_date, snapshot_date, detail, plus a per-check summary count. Cover at least: primary-key uniqueness, lead_time_days equal to (stay_date - snapshot_date) in days, listed_price_usd null exactly when units_available is 0, and non-negative counts. It must return the right empty frame on empty input.
Approach
- Write each check as a boolean mask over the whole frame, not a row-wise function, and append a small frame carrying check_name plus the three key columns and a
detailstring built from the offending values. Concatenate at the end with a typed empty frame in the list so an all-clean or empty input still returns the declared columns and dtypes. Declare the check names once as a module-level tuple, CHECK_NAMES, and drive both the run loop and the summary off that tuple rather than off whatever happened to fail. - Test nullity with .isna(); listed_price_usd is a float column so a NULL is NaN, and NaN == NaN is False, which means any
== Noneor== np.nancomparison returns an all-False mask and the check passes vacuously on every row. - Check the price rule in both directions: a non-null price where units_available is 0 means someone imputed the last known price onto a sold-out unit-date, which is the single most damaging corruption here because it turns censored observations into apparently-observed ones for any elasticity work.
- Separate hard invariants from business expectations and label them with a severity column. Uniqueness of (supply_unit_id, stay_date, snapshot_date), the lead-time identity and non-negativity are invariants. Monotonicity of units_sold_to_date across snapshot_date is not: cancellations legitimately reduce cumulative pickup, so it belongs at warn severity with a threshold on the size of the drop.
- Build the summary by grouping the failure frame on check_name and then reindexing against CHECK_NAMES with fill_value=0. A bare groupby emits no row for a check that found nothing, so a clean input returns a summary listing only the broken checks and an empty input returns no summary at all — which on a dashboard is indistinguishable from the suite never having run, the one state a summary exists to rule out. Return counts per stay_date alongside it so a caller can see whether failures are uniform or concentrated on a single partition, which is what distinguishes a code bug from one bad ingest day.
Worked solution 20 min
- Declare CHECK_NAMES as a fixed tuple of the four check names, then define a helper that takes a mask and a check_name and returns the key columns of the failing rows plus a detail string; collect results in a list.
- Check 1: snap.duplicated(subset=['supply_unit_id','stay_date','snapshot_date'], keep=False) flags every member of a duplicate key group, not just the later ones.
- Check 2: (snap['stay_date'] - snap['snapshot_date']).dt.days != snap['lead_time_days'].
- Check 3: two masks, (units_available == 0) & listed_price_usd.notna(), and (units_available > 0) & listed_price_usd.isna().
- Check 4: any of units_available, units_sold_to_date, units_blocked below zero; and min_length_of_stay below 1.
- Concatenate with an explicit empty typed frame, then build the summary as failures.groupby('check_name').size().reindex(CHECK_NAMES, fill_value=0), so a check that found nothing reports 0 instead of vanishing and a clean or empty input still returns one row per declared check.
Follow-up
- Which of your checks would you make a hard pipeline failure and which a warning, and what is the cost of getting that wrong in each direction?
- units_sold_to_date drops by 4 between two consecutive snapshots for one unit-date. Give two innocent explanations and one that should page someone.
- How would you run these on a table too large to hold in memory, keeping the same failure frame?
How would you use SQL to identify users who dropped off at the payment…
How would you use SQL to identify users who dropped off at the payment stage?
Approach
- State the window function and its partition and ordering out loud before writing it.
- Say which table is the grain you start from, and join outward from it.
- Compute rates by summing numerator and denominator separately, never by averaging rates.
Follow-up
- What breaks if events arrive late or out of order?
- How does the query change if the join becomes one-to-many?
Given a table of user transactions, how do you calculate the cumulativ…
Given a table of user transactions, how do you calculate the cumulative sum of revenue per user?
Approach
- Check whether any join is one-to-many before aggregating, or the sums inflate.
- Say which table is the grain you start from, and join outward from it.
- State the window function and its partition and ordering out loud before writing it.
Follow-up
- What breaks if events arrive late or out of order?
- How would you verify this result without re-running the same query?
Write a SQL query using window functions to calculate the moving avera…
Write a SQL query using window functions to calculate the moving average of daily bookings over the last 7 days.
Approach
- Check whether any join is one-to-many before aggregating, or the sums inflate.
- Say which table is the grain you start from, and join outward from it.
- Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.
Follow-up
- How does the query change if the join becomes one-to-many?
- How would you verify this result without re-running the same query?
Pick the last availability snapshot before each stay date
fct_rate_availability_snapshot is keyed on (supply_unit_id, stay_date, snapshot_date) and carries units_available, units_sold_to_date, units_blocked, listed_price_usd, is_closed_to_arrival and min_length_of_stay. For every supply_unit_id and every stay_date in one target month, return exactly one row: the values from the latest snapshot_date that is on or before that stay_date. Output supply_unit_id, stay_date, snapshot_date, units_available, units_sold_to_date and listed_price_usd. A single global maximum snapshot_date is not acceptable.
Approach
- Restrict to the target stay_date range and to snapshot_date <= stay_date inside the same CTE; that predicate is evaluated per row, which is what makes this an as-of pick rather than a latest-snapshot pick.
- Rank with ROW_NUMBER() OVER (PARTITION BY supply_unit_id, stay_date ORDER BY snapshot_date DESC) and keep rank 1. RANK() would return two rows if the table ever holds a duplicate snapshot_date; ROW_NUMBER guarantees one.
- Filter on the rank in an outer query, or use DISTINCT ON / QUALIFY where the engine supports it. A window function cannot be referenced in the WHERE clause of the SELECT that computes it.
- Leave unit-dates with no qualifying snapshot out of the result and say so explicitly; if the deliverable needs them present, LEFT JOIN from a unit-by-date spine so the absence surfaces as a NULL row instead of a missing one.
- Keep snapshot_date in the output, because every downstream consumer needs to know how stale the as-of value is before acting on it.
Worked solution 20 min
- Count distinct (supply_unit_id, stay_date) pairs in the window; that is the row count the final query must return, less any unit-dates with no snapshot on or before arrival.
- Write the ranked CTE, then select rank 1 from an outer query.
- Assert across the result that no snapshot_date exceeds its stay_date.
- Spot-check one unit-date against its raw snapshot rows to confirm the chosen row really is the last capture before arrival.
Follow-up
- Now pick the snapshot as at exactly 30 days before arrival instead. What changes in the query, and which of the two do you use for a pace report?
- The table is partitioned on snapshot_date. How do you keep partition pruning while still ranking within each unit-date?
- You find two rows sharing a snapshot_date for one unit-date. What does that mean about the capture pipeline, and how do you break the tie deterministically?
How would you design a metric to measure the success of a new personal…
How would you design a metric to measure the success of a new personalized hotel recommendation feature?
Approach
- Decompose the metric into the rates that drive it, and say which one you would check first.
- Name one primary metric, then the guardrail that stops it being gamed.
- Fix the population and the time window before naming any metric.
Follow-up
- How would you detect that the metric is being gamed rather than genuinely improving?
- What would you do if the primary metric and the guardrail moved in opposite directions?
If the conversion rate on the flight booking page drops by 5% overnigh…
If the conversion rate on the flight booking page drops by 5% overnight, how would you go about diagnosing the root cause?
Approach
- State what result would change your recommendation, so the answer is falsifiable.
- Fix the population and the time window before naming any metric.
- Name one primary metric, then the guardrail that stops it being gamed.
Follow-up
- How would you detect that the metric is being gamed rather than genuinely improving?
- Which segment would you cut first, and what would that rule out?
How do you define "success" for an A/B test on the search results page…
How do you define "success" for an A/B test on the search results page?
Approach
- Name the guardrails that would stop a launch even on a positive primary result.
- Decide the analysis before seeing data, including how long it runs and when you look.
- State the primary metric and the minimum effect worth shipping, then size the test.
Follow-up
- What would you do if you could not randomise at all?
- What would you conclude if the result is positive but the test is underpowered?
Explain the concept of statistical significance and how it relates to …
Explain the concept of statistical significance and how it relates to false positives.
Approach
- State the primary metric and the minimum effect worth shipping, then size the test.
- Name the randomisation unit first; it decides the variance and what the test can detect.
- Decide the analysis before seeing data, including how long it runs and when you look.
Follow-up
- What would you do if you could not randomise at all?
- How would you handle interference between treated and control units?
Estimate a market-level policy rollout without randomisation
A mandatory change to displayed cancellation terms takes effect in six destination markets on dates set outside the business; it cannot be randomised or withheld. You have 104 weeks of market-by-week history for 186 markets on completed stay-nights and net revenue per sellable night. Four markets adopt on one date and two adopt three months later. Specify the estimator, how you test the identifying assumption, how you obtain standard errors from six treated units, and which date axis the outcome sits on.
Approach
- Settle the date axis first. The policy acts on the booking date, but completed stay-nights are consumed later, so the stay-axis effect arrives smeared across the lead-time distribution. Run a booking-axis outcome for the sharp read and a stay-axis outcome for the true one, and treat a knife-edge break on the stay axis as evidence of a data problem rather than of the policy.
- Reject plain two-way fixed effects because adoption is staggered: already-treated markets serve as controls for the later adopters and receive negative weights, which can reverse the sign when effects are dynamic. Use a group-time ATT estimator (Callaway-Sant'Anna or Sun-Abraham) with never-treated markets as the comparison group, aggregated into an event-time path.
- Test parallel trends with an event study on leads from week -12 to week -1 and run the joint test on those coefficients rather than inspecting the plot. Add a sensitivity analysis that reports how large a post-treatment violation of parallel trends would have to be to overturn the conclusion, since passing a pre-trend test is weak evidence.
- Fix the inference for six treated clusters. Cluster-robust standard errors are badly anti-conservative well above six clusters, so use randomisation inference: reassign the treatment labels with their adoption dates across the 186 markets many times and place the observed estimate in that null distribution, reporting the achievable p-value granularity alongside it.
- Cross-check with synthetic control. Build a donor-weighted synthetic counterpart for each treated market, require a small pre-period RMSPE, and evaluate significance with the post-to-pre RMSPE ratio against the placebo distribution over donors. With six treated units, synthetic difference-in-differences is the natural pooled version.
- Verify the mechanism rather than only the headline: the effect should show up in rate_plan mix and in the traveller-initiated cancellation rate by lead-time bucket, and an effect that appears only in the aggregate is a sign of a confounded comparison.
Worked solution 45 min
- Build the market-by-week panel with both outcomes and both date axes, flagging each market's adoption week and the never-treated set.
- Estimate group-time ATTs for the two adoption cohorts separately, then aggregate into an event-time path from week -12 to week +12.
- Run the joint pre-trend test on the lead coefficients and record the p-value as a stated assumption check, not as proof.
- Run randomisation inference by permuting the six treatment assignments and their dates across the 186 markets, and report the observed estimate's rank in that distribution.
- Fit the synthetic control per treated market, report pre-period RMSPE and the post-to-pre ratio against donor placebos, and compare the pooled synthetic DiD estimate with the group-time aggregate.
Follow-up
- One of the six markets is far larger than the others. What does that do to the estimator and to the randomisation-inference null?
- Suppose all six had adopted on the same date. What design would you use instead, and what do you lose?
- The ops team offers to delay rollout in two more markets by a month. What exactly would you ask them to delay, and why is that request worth making?
Blended daily rate fell while every segment's rate rose
Blended ADR for last month, computed from fct_stay_night as SUM(unit_revenue_usd) / COUNT(*) over night_status = 'stayed' at a fixed reference FX rate, fell 4.2% year over year. Cut by destination_market_id, by property_class and by rate_plan, every segment's ADR rose. Commercial leadership is drafting a note about discounting. Using fct_stay_night joined to dim_supply_unit (property_class, destination_market_id) and fct_booking (rate_plan, lead_time_days), quantify how much of the -4.2% is mix and how much is within-segment, and show that your segmentation choice is not doing the work.
Approach
- Write the identity before computing anything: blended ADR equals the sum over segments of w_s times ADR_s, where w_s is segment s's share of stayed nights. The change then decomposes into a mix term, sum of (w_s1 - w_s0) times ADR_s0; a within-segment term, sum of w_s0 times (ADR_s1 - ADR_s0); and an interaction term. Report the interaction rather than folding it silently into one side.
- Choose the segmentation from the mechanism rather than from convenience: market by property_class by rate_plan by lead-time bucket. A coarse cut leaves mix hiding inside segments, which makes the decomposition understate exactly the term you are trying to size.
- Compute w_s on stayed nights, the same denominator the metric uses. Weighting by bookings or by revenue produces terms that do not re-aggregate to the published blended ADR, and the reconciliation failure is how you will find out.
- Test that the answer is not an artefact of the cut. Rerun at two or three segmentation depths and report the spread in the mix share; if it swings wildly, the finest cut is fitting thin cells and the cell counts belong next to the numbers.
- Only now ask what moved the weights, and look for a cause with a date attached: a market that grew supply, a channel that brought shorter lead times, a seasonal shift in party size or length of stay.
Follow-up
- You have the decomposition. What do you actually recommend, given within-segment rate is up?
- The mix moved because one low-ADR market grew 40% year over year. Is that a problem to fix or the plan working as intended?
- How do you keep this decomposition honest when a segment exists this year that did not exist last year, so w_s0 is zero for it?
For someone who has spent the last year in notebooks, dashboards or modelling work and has not written raw SQL under time pressure. The first four days rebuild query fluency against a fixture you control and can verify by hand; the last three attach that fluency to the rest of the loop.
Prepare, practise & reflect
One practical outcome each day. Spend longer where you need it.
0 / 7 done01Build a fixture you can check answers against
- Create a local Postgres or SQLite database with four tables (users, sessions, events, orders) holding roughly 200 rows you generated yourself, so you know the contents well enough to predict every result.
- Deliberately seed the cases that break queries: a user with no sessions, a session with no events, two orders sharing a timestamp, a NULL in one join key, and one duplicated user row.
- Before writing any SQL, hand-compute five answers on paper (how many users placed at least one order, median orders per ordering user, and three others) and save them as the ground truth for the week.
Deliverable: A one-command seed script plus a text file of five hand-computed answers to grade every later query against.
Practice prompt ↗Practice prompt ↗Practice prompt ↗Worked solution ↗02Joins, filters and NULL semantics
- Answer "which users have no orders" three ways (LEFT JOIN with IS NULL, NOT EXISTS, NOT IN) and confirm that the NOT IN version returns zero rows once the subquery contains a NULL, because the comparison is never TRUE.
- Reproduce the LEFT JOIN that silently collapses to an inner join by putting a right-table predicate in WHERE, then fix it by moving the predicate into the ON clause, and record both row counts.
- Create a fan-out bug on purpose by joining orders to order_items and summing the order total, then correct it with a pre-aggregated subquery and explain in one line which table changed the grain.
Deliverable: One annotated .sql file holding the three join traps, each with the wrong result and the corrected result side by side.
Practice prompt ↗Practice prompt ↗Practice prompt ↗03Window functions and frames
- Write three window queries against the fixture: a running order total per user, the rank of each order within its user by value, and the day gap to that user's previous order, then check each against the day-one ground truth.
- Run ROW_NUMBER, RANK and DENSE_RANK over a column containing ties, print all three side by side, and write one sentence on when each is the correct choice.
- Switch one query from the default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which is what you get when ORDER BY is present and no frame is written) to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and explain why the output differs only when the ORDER BY column has duplicates.
Deliverable: Three verified window queries plus a short note explaining the RANGE versus ROWS difference in your own words.
Practice prompt ↗Practice prompt ↗04The four analytical query patterns
- Write a monthly retention grid: first order month per user, then months-since-first as the column, and verify that month zero equals the cohort size exactly.
- Sessionize the events table under a 30-minute inactivity rule using LAG plus a cumulative sum over a new-session flag.
- Build a four-step funnel that counts distinct users rather than events at each step, and state the rule you applied to a user who reaches step three without ever logging step two.
Deliverable: One file with the retention, sessionization and funnel patterns, each carrying a one-line note on the assumption it bakes in.
Practice prompt ↗Practice prompt ↗Worked solution ↗05Write SQL the way you will have to write it live
- Set a 12-minute timer and solve three medium prompts in a plain editor with no execution and no autocomplete, then run them and tally syntax errors separately from logic errors.
- Narrate one solution aloud while writing it, stating the grain of each intermediate result (one row per user, one row per user-day) before you type its body.
- Rewrite your slowest solution as a CTE chain where every CTE name states its grain, and time yourself re-solving it from blank.
Deliverable: A recording of one narrated solution plus an error tally that separates syntax from logic.
Practice prompt ↗Practice prompt ↗06One day for everything that is not SQL
- Write the preconditions of the two-sample t-test from memory, then check them: independent observations, and a difference in means whose sampling distribution is approximately normal, which at large sample sizes follows from the central limit theorem rather than from normality of the raw values.
- Write the difference between an odds ratio from logistic regression and a relative risk, and state the condition under which the two are close (low outcome prevalence).
- Prepare a 90-second answer to "how would you know this model is any good" that names the metric, the baseline you would beat, and the cost of the errors you care about.
Deliverable: One page of notes covering test preconditions, the odds-ratio caveat and the model-quality answer.
Practice prompt ↗Practice prompt ↗07Full loop rehearsal
- Run a 45-minute mock with someone willing to interrupt: 20 minutes of SQL, 15 minutes defining a metric, 10 minutes on a past project.
- Re-solve from blank the two queries you were slowest on this week and compare the times against day five.
- Write a five-line answer to "walk me through a project" that puts a number in the first sentence and names the decision the work changed.
Deliverable: Mock feedback notes plus a timed project narrative you can deliver without reading it.
Practice prompt ↗Practice prompt ↗Worked solution ↗Expand any day for tasks and deliverables. Your progress is saved on this device.
Sometimes the honest read is that the initiative did not work, and the person who commissioned the analysis was hoping otherwise. Interviewers want to know whether you softened it. Prepare the case where you delivered an unwelcome result, how you presented the uncertainty without hiding behind it, and what the team did next.
Tell me about a time you had to explain a complex model to a non-techn…
Tell me about a time you had to explain a complex model to a non-technical stakeholder.
Approach
- Close with what you would do differently, concretely.
- State the situation in two sentences and spend the rest on your reasoning.
- Name the disagreement or constraint, and how you resolved it with evidence.
Follow-up
- How did you know the outcome was caused by your change?
- What did you decide not to do, and why?
Rank three urgent requests with one analyst-week
You have one week. The pricing team wants a price elasticity for a constrained market to set next quarter's strategy. The supplier team wants a list of suppliers at risk of delisting, for outreach starting Monday. Finance needs a closed stay-month restated after a batch of late cancellations changed platform_net_revenue_usd on fct_stay_night, ahead of a reporting deadline on Thursday. All three escalated to your manager. Produce your order, the reason for each position, and the message you send to whoever is deferred.
Approach
- Sort by deadline irreversibility rather than by who escalated hardest. The finance restatement has an external reporting date, so a miss cannot be recovered later; the other two can start a week late at a cost.
- Check each request for a cheap partial that unblocks the requester without the full job: the supplier team needs a ranked outreach list to start Monday, not a churn model, so a ranked cut on rating_avg, listing_status and recent availability withdrawal may be a few hours rather than days.
- Push back on the elasticity on substance, not capacity. On constrained inventory, price is set from a demand forecast and sold-out unit-dates record capacity rather than demand, so a fast estimate would be biased toward zero and could come out positive; say what you would deliver instead and what a clean estimate would cost.
- Write the deferral message with a date, a reason the requester can verify, and a concrete alternative, because a deferral without a date gets re-escalated within 48 hours.
- Take the ordering to your manager as a decision already made with a named tradeoff, not as a question, and include what you will drop if a fourth request arrives.
Follow-up
- The pricing team says a rough elasticity is better than nothing. What do you give them?
- Two days in, finance's restatement turns out to be twice the size you scoped. What gives?
- How would you stop all three landing in the same week next quarter?
Scope a one-line request that conversion is down
A director messages: 'Conversion is down, can you look into it?' There is no date range, no market, no definition. In this product, conversion can mean searches to bookings from fct_search, distinct trip_intent_key values to bookings, bookings to completed stays on fct_stay_night, or any of these on the booking axis or the stay axis. Before you run a single query, send back a scoping note. Prepare the clarifying questions in priority order and the note itself, including the metric written as numerator, denominator, window and date axis.
Approach
- Ask the decision question before the metric question: what will change depending on the answer. A conversion drop that triggers a pricing response and one that triggers a supply response need different numbers, and the answer reorders everything below.
- Resolve the denominator explicitly, because it is the largest source of disagreement here. Searches inflate with any interface change that encourages re-sorting, so the defensible demand unit is DISTINCT trip_intent_key, with is_bot_flagged = FALSE and results_returned_count > 0 excluded from the denominator so that supply coverage is measured separately.
- Resolve the date axis next and say it out loud: a booking-axis conversion drop and a stay-axis revenue drop can both be real in the same week and disagree by tens of percent, so naming the axis is not pedantry, it is the difference between two analyses.
- Pin the window and the maturity rule: whether cancellations are in or out, and if completed stays are the numerator, that the recent cohorts cannot be read yet and you will report at a fixed cohort age instead.
- Return a note with one stated definition, the first cut you will run (market, device_type, lead-time bucket), a time-box, and one explicit assumption the director can correct in a single reply rather than a meeting.
Follow-up
- The director replies 'just the normal one'. What do you do next?
- Your first cut shows the drop is entirely in mobile_web in two markets. Does the scope change?
- How do you keep this from recurring for the next ten ad-hoc requests?
- 01
Tell me about a time you had to explain a complex model to a non-technical stakeholder.
- 02
You have one week. The pricing team wants a price elasticity for a constrained market to set next quarter's strategy. The supplier team wants a list of suppliers at risk of delisting, for outreach starting Monday. Finance needs a closed stay-month restated after a batch of late cancellations changed platform_net_revenue_usd on fct_stay_night, ahead of a reporting deadline on Thursday. All three escalated to your manager. Produce your order, the reason for each position, and the message you send to whoever is deferred.
- 03
A director messages: 'Conversion is down, can you look into it?' There is no date range, no market, no definition. In this product, conversion can mean searches to bookings from fct_search, distinct trip_intent_key values to bookings, bookings to completed stays on fct_stay_night, or any of these on the booking axis or the stay axis. Before you run a single query, send back a scoping note. Prepare the clarifying questions in priority order and the note itself, including the metric written as numerator, denominator, window and date axis.
Is this an official Make My Trip interview guide?
No. It is PracHub's own research and practice material for the Data Scientist role at Make My Trip. Rounds and questions reflect what candidates have reported, not a process Make My Trip has published, and they change over time. Confirm the current format and scope with your recruiter.
PracHub interview research ↗How difficult are the coding rounds?
The coding rounds are generally medium to difficult. They focus on common data structures and algorithms, as well as practical SQL challenges. You should be comfortable with tree traversal, stack/queue operations, and complex SQL joins.
PracHub interview research ↗What is the best way to prepare for the case study round?
Focus on structure. Start by clarifying the goal, defining your success metrics, and then breaking down the problem into smaller, manageable components. Always consider the business impact.
PracHub interview research ↗Does the company value theoretical knowledge or practical experience more?
Make My Trip values both. You will be tested on the theory behind your models, but you must also be able to explain how you would apply those models in a real-world, high-traffic production environment.
PracHub interview research ↗How long does the entire process usually take?
The process can move relatively quickly, but it is thorough. Expect a few weeks from the initial screen to the final round, depending on scheduling availability.
PracHub interview research ↗Sources & methodology 3 sources ↗
No official company page is cited. Rounds and questions come from candidate reports and PracHub editorial material; each source shows the date it was read.
- 01PracHub interview research ↗
PracHub editorial research into this company and role, maintained with this guide. Candidate-reported, not an employer publication.
PracHub page · Accessed 2026-09-22 - 02PracHub Data Scientist practice ↗
Cross-company practice questions for this role.
PracHub page · Accessed 2026-09-22 - 03PracHub interview preparation framework ↗
The framework the preparation plan follows.
PracHub page · Accessed 2026-09-22
Explore related engineering environments and compare the preparation focus.
Booking.com Data Scientist Interview Questions & Guide 2026
A prep plan for Booking.com data scientist candidates built on spaced review: write a full answer before opening any solution, keep a running list of the assumptions you failed to state, and repeat every missed exercise three days later ins
The Travelers Companies Data Scientist Interview Questions & Guide 2026
A prep plan for The Travelers Companies data scientist candidates built on spaced review: write a full answer before opening any solution, keep a running list of the assumptions you failed to state, and repeat every missed exercise three da
United Airlines Data Scientist Interview Questions & Guide 2026
Statistics practice aimed at United Airlines candidates: conditional probability, sampling distributions, confidence intervals people routinely misread, and saying out loud which assumption a test depends on.
Universal Orlando Resort Data Scientist Interview Questions & Guide 2026
Prepare for the data scientist interview at Universal Orlando Resort with original practice on experiment design, SQL, and metric reasoning, each exercise paired with a worked solution and the reasoning behind it.