Airbnb Business Intelligence Analyst Interview Preparation Guide - Mid Level
Airbnb's Business Intelligence Analyst interview process for mid-level candidates consists of 6 rounds spanning 4-6 weeks. The process begins with recruiter screening, followed by a technical assessment, and culminates in a comprehensive onsite 'Insights Loop' with four focused interview rounds. Each stage rigorously evaluates technical proficiency in SQL and Python, BI tool expertise with Tableau, analytical thinking, data storytelling capabilities, and alignment with Airbnb's core values. The overall structure emphasizes both technical rigor and communication ability, reflecting the role's requirement to translate complex data into actionable business insights for cross-functional stakeholders.
Interview Rounds
Recruiter Screening
What to Expect
Your initial interaction with Airbnb combines resume review and a phone conversation with a technical recruiter. The recruiter will assess your technical background in SQL, Python, and BI tools like Tableau, Power BI, or Looker. They'll evaluate your portfolio of analytical projects, depth of experience with business metrics and KPIs, and your ability to articulate why you're passionate about joining Airbnb. This round also serves as a cultural fit assessment where the recruiter listens for examples of cross-functional collaboration, data storytelling ability, and alignment with Airbnb's mission of belonging anywhere. You should be prepared to discuss specific projects where you built dashboards, improved reporting processes, solved complex analytical problems, or derived insights that drove measurable business decisions. For mid-level candidates, emphasize your ability to own projects completely from conception to stakeholder presentation, any mentorship you've provided to junior analysts, and cross-functional influence you've had on product or business decisions.
Tips & Advice
Prepare a compelling 'Why Airbnb?' narrative that goes beyond generic admiration - reference specific strategic initiatives like sustainable travel growth, improving host onboarding experience, or optimizing the search and discovery experience. Quantify your past achievements with concrete metrics and business impact (e.g., 'improved dashboard performance by 40% and reduced report generation time from 4 hours to 30 minutes'). Have 2-3 specific project examples ready that demonstrate analytics ownership, stakeholder communication, problem-solving under ambiguity, and measurable outcomes. Research Airbnb's current market position, competitive threats from similar platforms, and their geographic expansion strategy. Dress professionally and treat this as seriously as onsite rounds - recruiter impressions significantly influence later interview feedback and your hiring committee's perspective. Practice your elevator pitch about why Airbnb's business model and culture resonate with you personally.
Focus Topics
Airbnb Values and Belonging Anywhere Mission
Examples demonstrating your alignment with Airbnb's core value of 'Belonging Anywhere' and how you embody this in your work. Include instances of fostering inclusivity, supporting diverse perspectives, and contributing to community-focused initiatives.
Practice Interview
Study Questions
Airbnb Business Model and Strategic Context
Knowledge of Airbnb's two-sided marketplace, revenue streams (host service fees, guest service fees), key performance metrics (occupancy rates, ADR, take rate, host and guest retention), competitive landscape, geographic expansion strategy, and current business challenges or opportunities.
Practice Interview
Study Questions
Cross-Functional Collaboration Examples
Specific stories demonstrating effective collaboration with product managers, engineers, operations leaders, marketing teams, and financial stakeholders. Examples showing how you translated ambiguous business needs into clear analytical solutions and drove decisions through data.
Practice Interview
Study Questions
Quantified Project Ownership Examples
Concrete examples of analytics projects you've owned end-to-end: project scope, challenges faced, analytical methodologies used, and quantified business impact (revenue, cost savings, efficiency gains, user satisfaction improvements). Include 1-2 examples where your analysis directly influenced business strategy or product decisions.
Practice Interview
Study Questions
Technical Skills Foundation
Demonstrate proficiency in SQL (querying, optimization, window functions), Python (pandas, numpy, scikit-learn), Tableau/Power BI/Looker (dashboard design, visualization), and statistical analysis. Recruiter will verify hands-on experience with data manipulation, complex query writing, and visualization best practices.
Practice Interview
Study Questions
Technical Assessment
What to Expect
This round combines a timed 30-minute HackerRank SQL assessment with a subsequent case study or dashboard critique exercise conducted via video conference. In the SQL portion, you'll solve 2-3 medium to hard complexity queries that mirror real Airbnb business problems - such as analyzing guest booking patterns, calculating key performance metrics across multiple time periods, identifying trends in host behavior, or segmenting customers by engagement patterns. You must write production-quality SQL that actually executes without errors (pseudocode is not accepted at Airbnb). After completing the SQL assessment, you'll receive either a pre-built Airbnb dashboard to critique within a 20-30 minute timeframe or a business case study with sample data requiring you to propose analytical solutions. For the case study approach, you might receive a business question like 'How should we price new listings in emerging markets?' and sample data, then propose how you'd structure the analysis, which metrics you'd track, what insights would be critical, and how you'd visualize findings. This round is designed to assess your ability to extract insights from complex datasets, apply statistical reasoning, write optimized queries, and communicate findings clearly.
Tips & Advice
Practice SQL extensively using DataLemur (which features real Airbnb interview problems), StrataScratch, and LeetCode - focus specifically on medium-hard Airbnb problems. Write clean, well-commented, production-ready code that demonstrates optimization awareness. For the case study portion, structure your response clearly: start with problem understanding and clarifying questions, propose specific metrics you'd calculate, outline visualizations you'd create, discuss trade-offs between approaches, and address data quality considerations. Time yourself rigorously during practice - aim to complete the SQL portion in 20-25 minutes to leave buffer time. If stuck on a SQL problem, pivot to the next one rather than wasting time - partial credit is better than getting stuck. For dashboard critiques, evaluate both design elements (Is the hierarchy clear? Are colors used effectively? Is it intuitive?) and analytical substance (Are the right metrics displayed? Is data actionable? Could stakeholders make decisions from this?). Practice with Airbnb's publicly available Kaggle review dataset - build exploratory analyses and simple dashboards to develop practical experience.
Focus Topics
Data Interpretation and Insight Extraction
Ability to analyze query results and extract meaningful, actionable insights. Recognizing patterns, anomalies, and trends. Avoiding over-interpretation of noisy data or missing important context. Connecting findings back to business implications.
Practice Interview
Study Questions
Case Study Analysis Framework
Structured approach to business problems: clarify the business question and success criteria, identify relevant data sources and metrics, propose multiple analytical approaches showing trade-offs, design compelling visualizations that highlight key insights, and discuss limitations or assumptions in your analysis.
Practice Interview
Study Questions
Query Optimization and Execution Planning
Understanding database indexing strategies, analyzing query execution plans, identifying performance bottlenecks, and refactoring queries to run efficiently on large datasets. Knowledge of when to use different JOIN types or aggregation strategies based on performance implications.
Practice Interview
Study Questions
Statistical Metrics and KPI Calculation
Correctly calculating business metrics including rates (booking rate, conversion rate, retention rate), averages with proper grouping, percentiles/quartiles, correlations, and trend calculations. Understanding appropriate aggregation methods for different business questions.
Practice Interview
Study Questions
Advanced SQL Query Construction
Complex queries involving multiple JOINs (INNER, LEFT, FULL OUTER), GROUP BY with multiple dimensions, subqueries, Common Table Expressions (CTEs), window functions, HAVING clauses, and multi-step aggregations. Ability to write clear, readable queries that solve business problems requiring data from 4+ related tables.
Practice Interview
Study Questions
Onsite Interview - SQL Deep-Dive
What to Expect
This is the first of four onsite interviews in Airbnb's 'Insights Loop' series. In this 60-90 minute session, you'll work collaboratively with a senior BI analyst or analytics engineer through complex, multi-step SQL problems that mirror real analytical challenges at Airbnb. Unlike the earlier technical assessment, this is a collaborative conversation where the interviewer will probe your thinking process, ask clarifying questions, and potentially adjust problem complexity based on your approach. Expect problems requiring sophisticated SQL techniques: CTEs for multi-step logic, window functions for ranking and running calculations, complex JOINs combining 4+ tables, and aggregations across multiple dimensions. The interviewer assesses not only your final answer but how you break down ambiguous problems, handle edge cases, optimize for performance, communicate your reasoning, and respond to feedback. For mid-level candidates, you should approach these problems with confidence and ownership, asking clarifying questions when the problem is ambiguous, explaining trade-offs in your approach (e.g., why a CTE is better than a subquery for this specific problem), and demonstrating you can optimize queries after the initial solution works.
Tips & Advice
Before jumping into coding, ask clarifying questions about the problem scope, expected data volume, performance constraints, and expected output format. Write pseudocode or outline your approach on the whiteboard before diving into SQL - this demonstrates structured thinking and gives the interviewer a chance to confirm you're on the right track. Explain your thinking out loud throughout - the interviewer is evaluating your problem-solving process as much as the final answer. If you hit a roadblock, talk through it rather than staying silent - the interviewer may provide hints or clarify ambiguous requirements. Write clean, well-commented code that's easy to follow. Test your logic mentally with different data scenarios before declaring you're done. If you make a mistake, catch and correct it yourself - that demonstrates debugging ability. Show understanding of why you made specific technical choices (e.g., 'I used a window function here because I need the running total alongside each row, which is cleaner than a self-join'). For mid-level candidates, demonstrate that you can optimize queries for performance after getting the correct answer - discuss index usage, explain why you chose certain JOIN orders, and suggest how you'd monitor performance in production.
Focus Topics
Time-Series and Temporal Analysis
Date calculations using DATE_TRUNC, INTERVAL, date arithmetic. Period-over-period analysis (comparing same period last year, year-to-date trends). Seasonality detection, handling timezone conversions, and cohort analysis based on booking or signup dates.
Practice Interview
Study Questions
Query Optimization and Performance Tuning
Reading and interpreting query execution plans. Identifying bottlenecks (full table scans, expensive joins). Using indexes strategically. Refactoring queries for better performance (denormalization considerations, appropriate aggregation approaches, avoiding redundant calculations).
Practice Interview
Study Questions
Window Functions Advanced Usage
Expert-level use of ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG(), aggregate window functions (SUM, AVG, COUNT OVER), and frame specifications. Understanding PARTITION BY, ORDER BY, and ROWS/RANGE clauses for complex time-series and ranking problems.
Practice Interview
Study Questions
Common Table Expressions and Query Composition
Strategic use of CTEs (WITH clauses) to break complex queries into logical steps. When to use recursive CTEs for hierarchical data. Understanding readability trade-offs between CTEs, subqueries, and derived tables. Performance implications of CTE materialization.
Practice Interview
Study Questions
Multi-Table Joins and Aggregation Logic
Complex queries correctly joining 4-5+ tables with various JOIN types (INNER, LEFT, FULL OUTER, CROSS JOIN). Proper GROUP BY logic with multiple dimensions, HAVING clauses, and aggregate functions. Understanding join order and performance implications.
Practice Interview
Study Questions
Onsite Interview - Analytics & Forecasting Exercise
What to Expect
In this 60-90 minute interview, you'll tackle a forecasting or predictive analytics problem that mirrors real challenges Airbnb's BI team faces. You might receive scenarios like 'Forecast Q4 booking volumes given current trends and external factors,' 'Predict which new host listings will achieve high quality ratings,' or 'Forecast demand in emerging markets to inform pricing strategy.' You'll be given sample data (typically from Airbnb's publicly available datasets) and must propose a complete analytical approach. This differs significantly from the SQL deep-dive - here you'll move beyond data extraction into statistical modeling, potentially using Python or R, and communicate both your methodology and confidence in your predictions. The interviewer assesses your ability to select appropriate statistical or machine learning techniques for the problem, handle data quality issues, validate your model, communicate assumptions, present results with uncertainty bounds, and discuss production considerations. For mid-level candidates, you should demonstrate understanding of both simple statistical methods (linear regression, time series smoothing) and more sophisticated techniques (ensemble methods, regularization), knowing when each is appropriate and why.
Tips & Advice
Start by understanding the business context, success criteria, and acceptable error levels. Ask clarifying questions about historical data availability, external factors that influence outcomes (seasonality, events, holidays, marketing campaigns), and how the forecast will be used. Propose multiple analytical approaches before settling on one, demonstrating you understand trade-offs between simplicity/interpretability and complexity/accuracy. Use Python with pandas for data manipulation, numpy for numerical operations, matplotlib/seaborn for visualization, and scikit-learn/statsmodels for modeling. Explain your feature engineering choices - which variables do you include and why? How do you handle temporal aspects? Do you create lag features or seasonal indicators? Discuss your validation strategy (train/test split, time series cross-validation, residual analysis). Calculate performance metrics appropriate for your problem (MAE/RMSE for regression, precision/recall/F1 for classification). Discuss model limitations, assumptions you've made, and when the model might fail. Walk through how you'd monitor performance in production and when you'd trigger retraining. For mid-level candidates, demonstrate comfort with time series decomposition, ARIMA/exponential smoothing, linear/logistic regression, and ensemble methods like random forests or gradient boosting.
Focus Topics
Feature Engineering and Data Preparation
Creating meaningful features from raw data (lag features, seasonal indicators, business-cycle variables, external factors). Handling missing values, outliers, and data quality issues. Normalization/scaling when appropriate. Understanding when to create interaction terms or polynomial features.
Practice Interview
Study Questions
Predictive Modeling Fundamentals
Linear and logistic regression, decision trees, random forests, gradient boosting, and ensemble methods. Understanding overfitting vs. underfitting, bias-variance trade-off, and model selection criteria. When to use regularization (Ridge, Lasso) and feature selection techniques.
Practice Interview
Study Questions
Model Validation, Testing, and Performance Metrics
Train/validation/test splits, time series cross-validation (avoiding data leakage), appropriate metrics for different problems (regression: MAE, RMSE, R²; classification: precision, recall, F1, ROC-AUC). Residual analysis and error understanding.
Practice Interview
Study Questions
Python for Statistical Analysis and Modeling
Proficiency with pandas for data manipulation and transformation, numpy for numerical operations, matplotlib/seaborn for exploratory visualization, scikit-learn for supervised learning, and statsmodels for statistical modeling. Writing clean, well-structured analytical code.
Practice Interview
Study Questions
Statistical Forecasting Methods
Time series analysis including ARIMA (AutoRegressive Integrated Moving Average), exponential smoothing methods, trend decomposition, and seasonality modeling. Understanding when each method is appropriate based on data characteristics (stationarity, presence of trend/seasonality). Prophet and other modern forecasting frameworks.
Practice Interview
Study Questions
Onsite Interview - Stakeholder Presentation
What to Expect
This 60-minute interview tests your ability to communicate complex analytical findings to non-technical audiences and influence business decisions through data storytelling. You'll prepare a 15-20 minute presentation on a pre-assigned analytics project (assigned 24 hours beforehand), then present to a cross-functional panel that might include product managers, operations leaders, financial analysts, and potentially executive stakeholders. Your presentation should tell a compelling data story: start with the business question and context so non-analysts understand why this analysis matters, explain your methodology at an accessible level (avoiding technical jargon without losing rigor), present key findings with compelling visualizations that highlight insights, discuss implications and specific recommendations, and address trade-offs, limitations, and what you'd need to do next. After your presentation, expect 20-30 minutes of detailed Q&A where stakeholders probe your thinking, challenge your conclusions, and ask 'what-if' scenarios. Interviewers assess not just content quality but your communication style, ability to simplify complex concepts for different audiences, responsiveness to audience needs and pushback, confidence in your findings, and ability to connect data insights to business strategy.
Tips & Advice
Create an executive-ready presentation that's clear, concise, and visually compelling - assume the audience is smart but lacks deep technical knowledge. Start with the business question and context, explaining why this analysis matters to Airbnb's strategy. Use simple, jargon-free language; when technical terms are necessary, explain them briefly. Build visualizations that tell the story - use dashboards strategically to highlight key insights without overwhelming the audience with raw data. Include a clear recommendations section that translates findings into specific, actionable business decisions. Quantify impact where possible (revenue implications, cost savings, user experience improvements). Practice presenting out loud multiple times until you're comfortable with pacing, tone, and natural transitions. Anticipate tough questions: 'What if we did X instead?' 'How confident are you in these findings?' 'What would it cost to implement this?' Have backup slides with technical details for the curious data scientists in the room. Remember that stakeholders primarily care about business impact, not methodological elegance - don't over-explain your ML algorithm if the business outcome is what matters. For mid-level candidates, position yourself as a strategic partner who understands business implications and can influence decisions, not just an analyst reporting numbers. Reference cross-functional collaboration in your narrative.
Focus Topics
Communication with Diverse Stakeholders
Translating analytical findings into business language appropriate for different audiences. Explaining methodology without overwhelming non-technical stakeholders. Handling skeptical or challenging questions gracefully. Adjusting communication depth and terminology based on audience expertise level.
Practice Interview
Study Questions
Tableau Dashboard Development
Building interactive, user-friendly dashboards that support decision-making. Dashboard layout and information hierarchy, drill-down capabilities, filters for exploring different dimensions, performance optimization. Designing for different user personas (executives want KPIs, analysts want exploration).
Practice Interview
Study Questions
Strategic Recommendations and Business Impact
Connecting data findings directly to business strategy and decisions. Quantifying impact of recommendations (revenue implications, cost savings, efficiency gains, user satisfaction). Discussing trade-offs, implementation considerations, and resource requirements. Explaining 'so what' to make recommendations actionable.
Practice Interview
Study Questions
Data Storytelling and Narrative Structure
Crafting compelling data narratives: clear problem statement and business context, logical hypothesis and analytical approach, findings that build sequentially, and actionable recommendations. Using the 'why this matters' framing to engage stakeholders. Connecting insights to business strategy.
Practice Interview
Study Questions
Data Visualization and Dashboard Design
Choosing appropriate chart types for different data types and audiences (bar charts for comparisons, line charts for trends, scatter plots for relationships, heatmaps for matrices). Color theory, accessibility considerations, avoiding misleading visualizations. Emphasizing key insights while maintaining data integrity.
Practice Interview
Study Questions
Onsite Interview - Core Values & Behavioral
What to Expect
This final 60-minute interview assesses your alignment with Airbnb's core values and cultural fit within the organization. You'll be asked behavioral questions focusing on collaboration, problem-solving, adaptability, learning mindset, and how you embody 'Belonging Anywhere.' Expected questions include: 'Tell me about a time you had a significant disagreement with a stakeholder about data interpretation and how you resolved it,' 'Describe a situation where data contradicted your initial hypothesis,' 'How have you actively contributed to creating an inclusive team environment,' 'Tell me about a time you mentored a junior colleague or helped someone grow their analytical skills,' and 'Share an example of how you failed and what you learned.' This isn't a relaxed chat - it's a structured evaluation of your working style, interpersonal effectiveness, resilience in facing setbacks, and genuine passion for Airbnb's mission. The interviewer is assessing your growth mindset, curiosity about business problems, ability to handle ambiguity, resilience when facing rejection or failed experiments, and authentic commitment to the values beyond surface-level platitudes. For mid-level candidates, expect deeper questions about project leadership, mentorship, cross-functional influence, and how you've contributed to your team's development.
Tips & Advice
Prepare 6-8 strong STAR method stories (Situation, Task, Action, Result) that showcase collaboration, problem-solving, mentorship, resilience, learning from failure, and driving decisions through data. Use specific examples with named projects and quantified outcomes whenever possible. Research Airbnb's core values thoroughly and think about how your experiences align authentically (not forcing contrived connections). Be genuine and vulnerable - interviewers can detect insincerity and preprinted responses. If you haven't formally mentored someone, discuss how you've helped junior colleagues learn, created learning opportunities, or elevated team capability. Have a thoughtful, specific answer to 'Why Airbnb?' that references particular initiatives, products, or cultural values that genuinely resonate with you - avoid generic statements about 'traveling the world' or 'disruption.' Listen carefully to questions and answer directly rather than reciting prepared speeches. If asked something unexpected, take a moment to think before responding - thoughtfulness is valued over quick reflexes. Include examples showing intellectual humility (times you were wrong and learned), collaborative problem-solving, and impact beyond your individual work. At mid-level, emphasize instances where you took ownership of complex projects, drove decisions through data despite ambiguity, mentored junior analysts, and elevated your team's capabilities. Show evidence of cross-functional influence and strategic thinking beyond just analytics execution.
Focus Topics
Learning from Failure and Growth Mindset
Honest examples of mistakes, failed projects, or predictions that were wrong. What you learned, how you adapted, and how you applied lessons going forward. Demonstrating resilience, humility, and genuine growth from setbacks.
Practice Interview
Study Questions
Ownership and Project Leadership
Examples of taking complete end-to-end ownership of analytics projects despite obstacles or uncertainty. Stories showing you drove initiatives forward, held yourself accountable for outcomes, and didn't blame external factors. For mid-level, examples of leading initiatives that impacted multiple teams or influenced strategic decisions.
Practice Interview
Study Questions
Data-Driven Decision Making and Intellectual Honesty
Stories where data informed difficult or unpopular decisions, where you challenged conclusions with evidence, where you stood by data despite intuitive pressure to do otherwise. Examples of handling situations where data contradicted your hypothesis or initial assumptions.
Practice Interview
Study Questions
Mentorship and Developing Others
Examples of mentoring junior analysts, helping colleagues develop new skills, creating learning opportunities for your team, or lifting team capability. Stories showing you're invested in others' growth, not just focused on individual achievement.
Practice Interview
Study Questions
Cross-Functional Collaboration and Teamwork
Specific examples of working effectively with people from different backgrounds, functions, expertise levels, and working styles. Stories about handling disagreements constructively, building consensus across disparate teams, and staying collaborative under pressure.
Practice Interview
Study Questions
Belonging Anywhere Value Alignment
Authentic examples demonstrating how you actively contribute to inclusive, welcoming environments. Stories showing you value diverse perspectives, actively seek input from quieter team members, champion underrepresented viewpoints, or create psychological safety. Examples of breaking down barriers to belonging.
Practice Interview
Study Questions
Frequently Asked Business Intelligence Analyst Interview Questions
Sample Answer
Sample Answer
SELECT
id,
ts,
val,
FIRST_VALUE(val) OVER (PARTITION BY id ORDER BY ts ASC NULLS LAST) AS first_val_including_nulls,
FIRST_VALUE(val) IGNORE NULLS OVER (PARTITION BY id ORDER BY ts ASC) AS first_nonnull_val
FROM events;WITH ranked AS (
SELECT
id,
ts,
val,
ROW_NUMBER() OVER (
PARTITION BY id
ORDER BY CASE WHEN val IS NULL THEN 1 ELSE 0 END, ts ASC
) AS rn
FROM events
)
SELECT id, ts, val
FROM ranked
WHERE rn = 1;WITH nonnulls AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY ts ASC) AS rn
FROM events
WHERE val IS NOT NULL
)
SELECT id, val AS first_nonnull_val
FROM nonnulls
WHERE rn = 1;Sample Answer
Sample Answer
Sample Answer
-- Step 1: user first event (cohort_month)
WITH user_first AS (
SELECT user_id,
DATE_TRUNC('month', MIN(event_date)) AS cohort_month
FROM events
GROUP BY user_id
),
-- Step 2: user-month activity
user_month AS (
SELECT e.user_id,
DATE_TRUNC('month', e.event_date) AS activity_month
FROM events e
JOIN user_first u ON e.user_id = u.user_id
GROUP BY e.user_id, activity_month
),
-- Step 3: join cohort to activity and compute month offset
cohort_activity AS (
SELECT u.cohort_month,
um.activity_month,
EXTRACT(MONTH FROM AGE(um.activity_month, u.cohort_month)) AS month_offset,
u.user_id
FROM user_first u
JOIN user_month um USING (user_id)
)
SELECT cohort_month,
COUNT(DISTINCT user_id) FILTER (WHERE month_offset = 0) AS cohort_size,
ROUND(100.0 * COUNT(DISTINCT user_id) FILTER (WHERE month_offset = 1) / NULLIF(COUNT(DISTINCT user_id) FILTER (WHERE month_offset = 0),0),2) AS retention_1m,
ROUND(100.0 * COUNT(DISTINCT user_id) FILTER (WHERE month_offset = 3) / NULLIF(COUNT(DISTINCT user_id) FILTER (WHERE month_offset = 0),0),2) AS retention_3m,
ROUND(100.0 * COUNT(DISTINCT user_id) FILTER (WHERE month_offset = 6) / NULLIF(COUNT(DISTINCT user_id) FILTER (WHERE month_offset = 0),0),2) AS retention_6m,
ROUND(100.0 * COUNT(DISTINCT user_id) FILTER (WHERE month_offset = 12) / NULLIF(COUNT(DISTINCT user_id) FILTER (WHERE month_offset = 0),0),2) AS retention_12m
FROM cohort_activity
GROUP BY cohort_month
ORDER BY cohort_month;Sample Answer
Sample Answer
CREATE TEMP TABLE events(id serial primary key, ts timestamptz, val int);
INSERT INTO events (ts, val) VALUES
('2025-01-01 00:00'::timestamptz, 10),
('2025-01-01 00:00'::timestamptz, 20), -- duplicate timestamp
('2025-01-02 00:00'::timestamptz, 5),
('2025-01-02 00:00'::timestamptz, 15); -- duplicate timestamp
-- RANGE using 1 day preceding (value-based)
SELECT id, ts, val,
SUM(val) OVER (ORDER BY ts
RANGE BETWEEN INTERVAL '1 day' PRECEDING AND CURRENT ROW) AS sum_range,
SUM(val) OVER (ORDER BY ts
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS sum_rows
FROM events
ORDER BY ts, id;Sample Answer
Sample Answer
WITH params AS (
SELECT
date_trunc('month', MIN(started_at))::date AS first_month,
date_trunc('month', CURRENT_DATE)::date AS last_month
FROM subscriptions
),
months AS (
SELECT generate_series(first_month, last_month, interval '1 month')::date AS month_start
FROM params
),
-- Expand subscriptions to intervals
subs AS (
SELECT
user_id,
started_at::date AS started_at,
COALESCE(cancelled_at::date, (CURRENT_DATE + interval '1 day')::date) AS cancelled_at
FROM subscriptions
),
-- users active in a given month if their interval overlaps [month_start, month_end)
active_by_month AS (
SELECT
m.month_start,
s.user_id
FROM months m
JOIN subs s
ON s.started_at < (m.month_start + interval '1 month') -- started before month end
AND s.cancelled_at >= m.month_start -- cancelled on/after month start
GROUP BY m.month_start, s.user_id
),
count_active AS (
SELECT
month_start,
COUNT(DISTINCT user_id) AS active_users
FROM active_by_month
GROUP BY month_start
),
churn_calc AS (
SELECT
curr.month_start AS month,
prev.active_users AS active_prev_month,
curr.active_users AS active_curr_month,
-- churned users count = users in prev month not in curr month
(SELECT COUNT(DISTINCT user_id)
FROM active_by_month p
LEFT JOIN active_by_month c
ON p.user_id = c.user_id AND c.month_start = curr.month_start
WHERE p.month_start = curr.month_start - interval '1 month'
AND c.user_id IS NULL
) AS churned_users
FROM count_active curr
LEFT JOIN count_active prev
ON prev.month_start = curr.month_start - interval '1 month'
)
SELECT
month,
active_prev_month,
active_curr_month,
churned_users,
CASE WHEN active_prev_month > 0 THEN ROUND(100.0 * churned_users / active_prev_month, 2) ELSE NULL END AS churn_rate_pct
FROM churn_calc
ORDER BY month;Sample Answer
WITH RECURSIVE bom_cte AS (
-- Base: start from root product
SELECT
b.bom_child_id AS component_id,
b.qty::numeric AS qty_needed,
ARRAY[b.bom_parent_id, b.bom_child_id] AS path -- track visited nodes
FROM bill_of_materials b
WHERE b.bom_parent_id = :root_id
UNION ALL
-- Recursive: expand children, multiply quantities, detect cycles
SELECT
child.bom_child_id AS component_id,
parent.qty_needed * child.qty::numeric AS qty_needed,
parent.path || child.bom_child_id
FROM bom_cte parent
JOIN bill_of_materials child
ON child.bom_parent_id = parent.component_id
WHERE NOT (child.bom_child_id = ANY(parent.path)) -- cycle detection
)
SELECT component_id,
SUM(qty_needed) AS total_qty_required
FROM bom_cte
GROUP BY component_id
ORDER BY component_id;Search Results
Airbnb Business Analyst Interview Guide – Process, Questions & Tips
1. Find the total salary of slacking employees · 2. Write a query to get the average commute time for each commuter in New York · 3. Write a query ...
Get a Job at Airbnb: Interview Process and Top Questions - Exponent
Why do you want to work at Airbnb? What does "belong anywhere" mean to you? Tell me about a time you were a good host. Describe a time when you ...
11 Airbnb SQL Interview Questions - Can You Solve Them?
Airbnb SQL interview questions include calculating average vacant days, analyzing monthly average ratings, and finding the most popular city ...
Airbnb Business Intelligence Interview Guide
3.5 Behavioral Questions ... 3.5.1 Tell me about a time you used data to make a decision that impacted business strategy. Focus on a specific ...
Airbnb Business Analyst Interview Question | Advanced SQL
Hi Learners! This is a complex and tricky interview question. This has been asked in the Airbnb Senior Business Analyst Interview.
This interview preparation guide was generated using AI-powered research from the sources listed above. While we strive for accuracy, we recommend verifying critical information from official company sources.
Want to create your own tailored preparation guide using our deep research?
Get Started for FreeInterview-Ready Courses
Visual-first, interactive, structured learning paths
Browse Business Intelligence Analyst jobs
AI-enriched listings across hundreds of company career pages
Explore Jobs