OYO’s Senior Data Analyst role sits at the intersection of hospitality, pricing intelligence, and growth analytics. You are expected to analyse booking funnels, occupancy metrics, and revenue-per-available-room (RevPAR) patterns β and translate them into product and business decisions. This guide covers all four interview rounds with real questions from 2025β2026 interviews. Click “Show Answer” on any question to reveal the full answer.
Python
Revenue Analytics
A/B Testing
Tableau / Metabase
Funnel Analysis
Booking funnel queries
Occupancy & RevPAR
Cancellation analysis
Cohort retention
Booking conversion metrics
Drop-off diagnosis
North Star metrics
Feature impact analysis
Dynamic pricing strategy
Demand forecasting
Experiment design
Revenue attribution
Market expansion analysis
Host supply strategy
Customer segmentation
Go-to-market metrics
Round 1 β SQL & Data Manipulation
| Questions | 4 questions |
| Duration | 60 minutes |
| Difficulty | Medium to Hard |
| Key Topics | Window FunctionsRevPAR / ADRBooking CohortsCancellation RatesDate Arithmetic |
The revenue team needs a daily report showing occupancy rate and RevPAR (Revenue Per Available Room) for each property. A room is “occupied” if its booking status is confirmed and the stay date falls between check-in and check-out.
Tables:
- properties(property_id, city, total_rooms, category)
- bookings(booking_id, property_id, checkin_date, checkout_date, status, revenue_inr)
generate_series or a date spine to expand multi-night stays into daily rows. Many candidates write the join on just checkin_date and miss all intermediate nights. RevPAR = Total Revenue / Total Available Rooms (not occupied rooms). The denominator is always the full inventory.WITH date_spine AS (
SELECT generate_series(
MIN(checkin_date),
MAX(checkout_date),
'1 day'::interval
)::date AS stay_date
FROM bookings
),
daily_occupancy AS (
SELECT
ds.stay_date,
b.property_id,
COUNT(b.booking_id) AS rooms_occupied,
SUM(b.revenue_inr / NULLIF(b.checkout_date - b.checkin_date, 0)) AS daily_revenue
FROM date_spine ds
JOIN bookings b
ON ds.stay_date >= b.checkin_date
AND ds.stay_date < b.checkout_date
AND b.status = 'confirmed'
GROUP BY ds.stay_date, b.property_id
)
SELECT
do.stay_date,
p.property_id,
p.city,
p.total_rooms,
do.rooms_occupied,
ROUND(100.0 * do.rooms_occupied / p.total_rooms, 1) AS occupancy_pct,
ROUND(do.daily_revenue / p.total_rooms, 0) AS revpar
FROM daily_occupancy do
JOIN properties p USING (property_id)
ORDER BY do.stay_date, p.property_id;- How would you compute a 7-day rolling average occupancy rate?
- How do you handle a booking where checkout_date equals checkin_date (same-day stay)?
- The revenue team wants to compare RevPAR vs ADR (Average Daily Rate). How do they differ?
Operations want to understand the cancellation rate trend month-over-month and whether bookings made further in advance (higher lead time) have higher or lower cancellation rates.
Tables:
- bookings(booking_id, property_id, booking_date, checkin_date, status, city)
checkin_date - booking_date. The interviewer wants to see if you bucket lead time into bins (0β3 days, 4β7 days, 8β30 days, 30+ days) rather than treating it as a continuous variable β a sign of business thinking over raw SQL output.WITH booking_enriched AS (
SELECT
booking_id,
DATE_TRUNC('month', booking_date)::date AS booking_month,
(checkin_date - booking_date) AS lead_days,
status,
CASE
WHEN (checkin_date - booking_date) BETWEEN 0 AND 3 THEN '0-3 days'
WHEN (checkin_date - booking_date) BETWEEN 4 AND 7 THEN '4-7 days'
WHEN (checkin_date - booking_date) BETWEEN 8 AND 30 THEN '8-30 days'
ELSE '30+ days'
END AS lead_bucket
FROM bookings
)
SELECT
booking_month,
lead_bucket,
COUNT(*) AS total_bookings,
SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancellations,
ROUND(100.0 * SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) / COUNT(*), 1) AS cancel_rate_pct
FROM booking_enriched
GROUP BY booking_month, lead_bucket
ORDER BY booking_month, lead_bucket;- How would you adjust for the fact that future bookings haven’t yet reached their stay date and may still cancel?
- What if the business wants to see cancellation rate by city and lead bucket simultaneously?
- Can you identify properties with consistently above-average cancellation rates in the last 3 months?
Top Interview Prep Resources
Used by 12,000+ candidates who cleared DA interviews at product companies.
- 2,200+ questions across all topics
- 23 analytics topics covered end-to-end
- 220+ questions, no fluff
- SQL advanced queries, data models, DBMS most asked topics
Build a cohort retention table showing what percentage of guests who made their first booking in each month went on to book again in each of the next 3 months.
Tables:
- bookings(booking_id, user_id, booking_date, status)
MIN(booking_date) globally instead of per user. Candidates who use a window function (ROW_NUMBER() partitioned by user) rather than a subquery are preferred β it shows SQL fluency.WITH first_booking AS (
SELECT
user_id,
DATE_TRUNC('month', MIN(booking_date))::date AS cohort_month
FROM bookings
WHERE status != 'cancelled'
GROUP BY user_id
),
user_activity AS (
SELECT DISTINCT
b.user_id,
DATE_TRUNC('month', b.booking_date)::date AS activity_month
FROM bookings b
WHERE b.status != 'cancelled'
),
cohort_data AS (
SELECT
f.cohort_month,
EXTRACT(YEAR FROM AGE(ua.activity_month, f.cohort_month)) * 12
+ EXTRACT(MONTH FROM AGE(ua.activity_month, f.cohort_month)) AS months_since_first,
COUNT(DISTINCT f.user_id) AS retained_users
FROM first_booking f
JOIN user_activity ua USING (user_id)
WHERE ua.activity_month >= f.cohort_month
AND ua.activity_month <= f.cohort_month + INTERVAL '3 months'
GROUP BY f.cohort_month, months_since_first
),
cohort_size AS (
SELECT cohort_month, COUNT(DISTINCT user_id) AS total_users
FROM first_booking
GROUP BY cohort_month
)
SELECT
cd.cohort_month,
cs.total_users,
cd.months_since_first AS period,
cd.retained_users,
ROUND(100.0 * cd.retained_users / cs.total_users, 1) AS retention_pct
FROM cohort_data cd
JOIN cohort_size cs USING (cohort_month)
ORDER BY cohort_month, period;- How would you visualise this cohort table as a heat map?
- What if we want to separate leisure vs business travellers in the cohort?
- How do you handle users who were acquired through a discount campaign β should they be in a separate cohort?
The revenue team wants to identify the top 10 properties whose revenue declined the most (in absolute βΉ terms) comparing the last 30 days vs the previous 30 days.
Tables:
- bookings(booking_id, property_id, checkin_date, revenue_inr, status)
- properties(property_id, property_name, city)
WITH periods AS (
SELECT
property_id,
SUM(CASE WHEN checkin_date >= CURRENT_DATE - 30 AND checkin_date < CURRENT_DATE
THEN revenue_inr ELSE 0 END) AS last_30d,
SUM(CASE WHEN checkin_date >= CURRENT_DATE - 60 AND checkin_date < CURRENT_DATE - 30
THEN revenue_inr ELSE 0 END) AS prev_30d
FROM bookings
WHERE status = 'confirmed'
AND checkin_date >= CURRENT_DATE - 60
GROUP BY property_id
)
SELECT
p.property_name,
p.city,
periods.prev_30d,
periods.last_30d,
periods.last_30d - periods.prev_30d AS revenue_change,
ROUND(100.0 * (periods.last_30d - periods.prev_30d) / NULLIF(periods.prev_30d, 0), 1) AS pct_change
FROM periods
JOIN properties p USING (property_id)
WHERE periods.prev_30d > 0
ORDER BY revenue_change ASC
LIMIT 10;WHERE prev_30d > 0 to avoid surfacing new properties with zero prior revenue showing as β100% decline. The interviewer will probe whether you thought about edge cases β new properties, inactive properties, and seasonality (a resort that does low business in off-season should not be flagged as “declining”).- How would you adjust for seasonal effects (e.g., last year same period comparison)?
- Would you rank by absolute decline or percentage decline? When does each matter?
- How do you handle a property that was onboarded 15 days ago β only 15 days of data in each window?
- β 2200 Questions eBook
- β Ace Any SQL Interview eBook
- β 1 Mock Interview Session
- β Resume Review
- β All 23 eBooks
- β 3 Mock Interviews
- β Resume Review
- β LinkedIn Review
- β Everything in Complete
- β 6 Mock Interviews
- β Referral Support
- β 90-day WhatsApp Access
Round 2 β Product & Funnel Analytics
| Questions | 3 questions |
| Duration | 45 minutes |
| Difficulty | Medium to Hard |
| Key Topics | Funnel AnalysisNorth Star MetricMetric DiagnosisConversion Rate |
Define and justify OYO’s North Star Metric. How does it connect supply-side (property performance) and demand-side (guest experience) health?
OYO’s North Star Metric is Confirmed Booked Nights (also called Room Nights Sold) β the total number of nights successfully checked into across all properties in a given period.
This metric captures both supply quality (available inventory) and demand health (guest bookings completing). It is better than GMV alone because:
- Revenue can be inflated by price hikes while volume falls β room nights sold shows true platform usage.
- It captures both new guest acquisition and repeat stays in one number.
- It is equally meaningful for the hotel partner (occupancy) and the guest (completed stays).
Supporting metrics: Occupancy Rate, Net Revenue per Room Night, Guest NPS, Cancellation Rate, Host Churn Rate.
- If Room Nights Sold is up but Revenue Per Room Night is down, is that good or bad?
- How would you build a metric tree from Room Nights Sold down to actionable operational metrics?
- What is the risk of optimising only for Room Nights Sold?
OYO’s overall booking conversion rate (Search β Confirmed Booking) dropped 15% last week compared to the prior week. Walk through your diagnosis framework.
Step 1 β Validate the metric. Confirm the definition is unchanged. Is it SearchβClickβCheckoutβPaymentβConfirmed? Check if any tracking or attribution code changed. Confirm the denominator (all searches vs. searches with β₯1 result).
Step 2 β Narrow the scope (MECE slicing):
- By funnel step: Which step broke β Search-to-Click, Click-to-Checkout, Checkout-to-Payment, or Payment-to-Confirm?
- By platform: iOS, Android, Web β is it platform-specific?
- By city / property type: Is it concentrated in specific markets?
- By user segment: New vs. returning users, organic vs. paid acquisition.
Step 3 β Hypotheses by funnel stage:
- SearchβClick drop: Inventory quality fell, fewer photos, lower ratings shown in search.
- ClickβCheckout drop: Price increase, slower load times, layout change pushed price above fold.
- CheckoutβPayment drop: Payment gateway failure, new friction added (OTP, UPI step), coupon code not working.
- PaymentβConfirm drop: Backend booking confirmation delay, overbooking rejection spike.
Step 4 β External factors: Holiday / event spike driving unusual search patterns, competitor promotion, news event affecting travel sentiment.
- You find the drop is isolated to iOS. The Android conversion is flat. What are your next steps?
- How would you decide whether to roll back a recent product change vs. wait and observe?
- If payment failure rate spiked, how do you quantify the revenue impact?
OYO wants to test an “Instant Book” feature where guests can confirm bookings without waiting for host approval (similar to Airbnb’s instant book). Design the A/B test.
Hypothesis: Removing the host-approval step will increase booking conversion by reducing friction in the checkout flow.
Unit of randomisation: Property-level (not user-level) β because Instant Book is a property-level setting. If a user sees the same property in both variants, the experience is inconsistent. Randomise properties into control (request-based) and treatment (instant book enabled).
Primary metric: Booking conversion rate (SearchβConfirm) for properties in each arm.
Guardrail metrics: Host cancellation rate (instant-book hosts may cancel more), guest NPS post-stay, average rating of instant-book stays, property availability rate (hosts may delist if overwhelmed by unscreened bookings).
Sample size & duration: Run for minimum 2 weeks to capture weekly seasonality. Compute required sample based on baseline conversion (~8%), expected lift (2%), Ξ±=0.05, power=0.8. With ~10K property-nights per week, 2 weeks gives sufficient power.
Risks: Network effects β guests who book Instant Book may leave fewer requests on non-instant properties. Use a holdout group of users to check for cross-contamination.
- Why not randomise at the user level instead of property level?
- What if the test runs for 3 weeks and conversion is up 3% but host cancellations are also up 1.5%? Do you ship?
- How do you handle novelty effect β guests preferring instant-book just because it is new?
Round 3 β Revenue & Pricing Analytics
| Questions | 3 questions |
| Duration | 45 minutes |
| Difficulty | Hard |
| Key Topics | Dynamic PricingDemand ElasticityRevenue AttributionForecasting |
OYO wants to move from fixed nightly prices to a dynamic pricing system. As a Senior DA, walk through how you would design the data framework and inputs for this model.
Core objective: Maximise RevPAR β not just occupancy (filling all rooms at low rates) and not just ADR (high rates with low occupancy).
Input signals for the pricing model:
- Demand signals: Current search volume for this city/date, historical booking pace for this lead window, upcoming local events (concerts, conferences), holiday calendar.
- Supply signals: Remaining available rooms at this property, competitor pricing (scraping OTA data), nearby property availability.
- Property signals: Historical occupancy curve for this day-of-week, average rating, cancellation history, room category.
- Guest signals: Booking channel (OYO app vs. third-party OTA), guest loyalty tier, device type (mobile users may be more price-sensitive).
Pricing mechanics: Set a floor price (minimum acceptable rate to cover host payout + OYO margin) and a ceiling price (maximum willingness-to-pay based on comp set). Use demand elasticity estimates from historical data to adjust the recommended price within the corridor.
Guardrails: Never drop below floor price, cap price increases during crisis events (regulatory risk), flag properties with very low ratings for manual review before premium pricing.
- How do you measure the success of the dynamic pricing model after launch?
- What is price elasticity and how would you estimate it from OYO’s historical data?
- If dynamic pricing increases RevPAR but host satisfaction drops, how do you balance?
OYO acquires bookings through its own app, website, Google Hotels, MakeMyTrip, and Goibibo. Write a query to compute channel-wise revenue share and commission cost for the last quarter, and identify the most profitable channel (net revenue after commissions).
Tables:
- bookings(booking_id, channel, booking_date, revenue_inr, commission_pct, status)
WITH channel_revenue AS (
SELECT
channel,
COUNT(*) AS total_bookings,
SUM(revenue_inr) AS gross_revenue,
SUM(revenue_inr * commission_pct / 100.0) AS total_commission,
SUM(revenue_inr * (1 - commission_pct / 100.0)) AS net_revenue
FROM bookings
WHERE status = 'confirmed'
AND booking_date >= DATE_TRUNC('quarter', CURRENT_DATE - INTERVAL '3 months')
AND booking_date < DATE_TRUNC('quarter', CURRENT_DATE)
GROUP BY channel
),
totals AS (
SELECT SUM(gross_revenue) AS total_gross FROM channel_revenue
)
SELECT
cr.channel,
cr.total_bookings,
cr.gross_revenue,
cr.total_commission,
cr.net_revenue,
ROUND(100.0 * cr.gross_revenue / t.total_gross, 1) AS revenue_share_pct,
ROUND(cr.net_revenue / NULLIF(cr.total_bookings, 0), 0) AS net_rev_per_booking
FROM channel_revenue cr
CROSS JOIN totals t
ORDER BY cr.net_revenue DESC;- MakeMyTrip drives 30% of bookings but only 18% of net revenue. What actions does this suggest?
- How would you factor in customer acquisition cost (CAC) for the OYO app vs. OTA channels?
- What is the lifetime value implication of acquiring a guest through OYO’s own app vs. a third-party OTA?
Round 4 β Business Case Study
| Questions | 1 case study |
| Duration | 40 minutes |
| Difficulty | Hard |
| Key Topics | Market SizingSupply StrategyLaunch AnalysisCross-Product Trade-offs |
OYO is considering launching a premium sub-brand “OYO Select” (βΉ2,500ββΉ5,000/night) in Tier-2 cities like Jaipur, Coimbatore, Indore, and Visakhapatnam. Evaluate the opportunity and recommend whether to proceed, a pilot strategy, and the metrics to track.
Framework β 4-part evaluation:
1. Market Opportunity: India’s Tier-2 business travel market is growing at ~18% YoY driven by IT corridor expansion (Indore, Coimbatore), MICE events, and domestic leisure. The βΉ2,500ββΉ5,000 price band is largely served by standalone 3-star hotels with no standardisation β OYO’s operational model is a direct advantage here. Estimate: ~5 Cr room nights annually in this segment across top-10 Tier-2 cities.
2. Supply Feasibility: OYO needs anchor supply β 50+ hotels per city meeting quality baseline. Current OYO inventory in Tier-2 is predominantly budget (sub-βΉ1,500). Premium supply requires partner upgrade investment or new hotel partnerships. Risk: host churn if OYO mandates quality upgrades.
3. Competitive Landscape: Lemon Tree, Treebo, and Fab Hotels already occupy this tier. Differentiation must come from OYO’s tech stack (dynamic pricing, guaranteed hygiene standards, 24/7 support) and brand trust built from budget segment.
4. Launch Recommendation: Pilot in 2 cities β Jaipur (leisure + MICE) and Coimbatore (IT business travel) β with 20 certified properties each. Run for 6 months before scaling.
Success Metrics:
- Occupancy Rate in pilot cities (target: β₯60% within 3 months)
- ADR achieved vs. target (βΉ3,200+)
- Guest NPS for OYO Select vs. standard OYO properties
- Host retention rate (are premium partners staying after 6 months?)
- Repeat booking rate from premium segment guests
- How do you determine the right Tier-2 cities to pilot in? What data signals would you use?
- If the pilot occupancy is only 45% at Month 3, do you kill the project or adjust?
- What is the risk of OYO Select cannibalising budget OYO bookings in the same cities?
4-Week Plan to Crack Any Product Company Interview
This plan works for OYO, Airbnb, Meta, Amazon β any product-based company. It covers ~950 questions across two eBooks and includes three mock interviews. No shortcuts, no cramming.
Foundation
Build your base β SQL, Python, case study frameworks
Master window functions, CTEs, cohort logic. Revise pandas basics, hypothesis testing. Learn the MECE framework for case studies. Read The Data Monk’s company-specific interview pattern posts to understand what is being asked before you prepare.
Question Bank
2200 Most Asked Analytics Interview Questions β deep dive
Cover the SQL, Python, and case study sections of the 2200 questions eBook. Focus on company-specific question sets. The best way to prepare for an interview is to see exactly what gets asked β this eBook gives you that edge.
End
SQL Mastery
Ace Any SQL Interview β end to end, no excuses
Pick up the Ace Any SQL Interview eBook and work through every question. Hard window function problems, optimisation, and real company SQL patterns. A second mock interview at the end of this week.
End
Final Polish
Revise first 5 chapters of the 2200 questions eBook + timed practice
Revisit the first five chapters of the main eBook under timed conditions β 15 minutes per SQL question, 20 minutes per case study. Simulate real interview pressure. A final mock interview to close out your preparation.
End