Guide #6Data, AI & Emerging Tech 25 min read Updated: September 2026

Data Analyst Interview Preparation: SQL, Excel, Statistics, BI & Case Questions

Q: What is the difference between WHERE and HAVING in SQL?

`WHERE` filters individual rows before any aggregation takes place. `HAVING` filters aggregated groups after the `GROUP BY` clause has been evaluated.

Q: What is Cohort Analysis and how is it used?

Cohort Analysis groups users based on a shared characteristic (usually acquisition date or first purchase month) and tracks their behavior over time. It is the gold standard for measuring product retention, churn, and the true impact of feature updates.

Q: What is the difference between Star Schema and Snowflake Schema in Data Warehousing?

In a Star Schema, dimension tables are completely denormalized and directly connected to a central fact table, resulting in faster query performance with simpler joins. In a Snowflake Schema, dimension tables are normalized into sub-dimension tables, saving storage space but requiring more complex multi-table joins.

Q: What is Simpson’s Paradox in data analysis?

Simpson’s Paradox occurs when a statistical trend or correlation appears in different groups of data, but disappears or reverses when the groups are combined. It highlights the danger of aggregating data without controlling for confounding variables.

Q: How do you handle missing or NULL data in analytics?

First investigate whether missingness is Missing Completely at Random (MCAR), Missing at Random (MAR), or Missing Not at Random (MNAR). Options include: dropping rows (if minimal), imputing with median/mode, creating an explicit "Unknown" category, or using forward-fill for time-series data.

Q: What is the difference between Power BI and Tableau?

Power BI integrates seamlessly with Microsoft ecosystems, uses DAX for data modeling, and is cost-effective for enterprise reporting. Tableau excels at advanced custom visual exploration, complex calculations, and handles massive multi-source datasets with refined design aesthetics.

Q: What is an A/B test and when can you NOT run an A/B test?

An A/B test splits users into control and treatment groups to measure the causal impact of a feature. You cannot run an A/B test when traffic volume is too low to achieve statistical power, when network effects cause interference between groups (like Uber rider/driver markets), or when testing pricing that could trigger user backlash.

Q: What projects should I include in a Data Analyst portfolio?

Include 2 or 3 in-depth end-to-end projects: 1. Business performance / cohort retention dashboard. 2. Exploratory data analysis (EDA) answering a real commercial question. 3. A root cause analysis case with documented findings, SQL code, and executive recommendations.

A comprehensive preparation playbook for aspiring and transitioning data analysts: Window functions, statistical significance, cohort retention, executive dashboard storytelling, and live business analytics cases.

In 2026, hiring managers look beyond basic SQL syntax and pivot tables; they evaluate structured problem-solving, root cause analysis, statistical rigor, and business acumen. This end-to-end roadmap provides an exhaustive guide to mastering advanced SQL window functions, spreadsheet modeling, executive dashboard design, metrics decomposition, and live case interviews.

Target Audience: Aspiring data analysts, business intelligence engineers, product analysts, career switchers, and fresh graduates preparing for analytics interviews.

Pillar: Data Science & AI Career Track →

What You'll Learn in This Masterclass

Advanced SQL Mastery: Window functions (ROW_NUMBER, DENSE_RANK, LEAD/LAG), CTEs, self-joins, and query performance.

Spreadsheet & Modeling Acumen: XLOOKUP, INDEX/MATCH, dynamic arrays, sensitivity tables, and financial/business models.

Statistics for Analytics: Probability distributions, hypothesis testing (p-values, z-test, t-test), and sampling bias.

Business Metrics Frameworks: CAC, LTV, Churn rate, Cohort Retention, GMV, and North Star metric decomposition.

Dashboard Storytelling: Designing executive-ready Power BI and Tableau dashboards with actionable UX hierarchies.

The 5-Step Root Cause Analysis (RCA) Framework for solving live product drop-off and revenue drop case questions.

Data Analyst Core Competencies & Interview Evaluation Weights

Evaluation Round	Technical & Business Competencies	Tool Stack	Weightage	Common Passing Benchmark
Live SQL Screening	Complex aggregations, windowing, subqueries, NULL handling	PostgreSQL / Snowflake / BigQuery	35%	Writing bug-free multi-table joins and window functions without syntax assistance
Business Analytics Case	Root cause diagnosis, metric tree decomposition, trade-offs	Whiteboard / Structured discussion	30%	Isolating factors systematically rather than guessing random possibilities
Spreadsheets & Statistics	A/B test evaluation, confidence intervals, financial modeling	Excel / Google Sheets / Python stats	20%	Correctly explaining p-values and avoiding Type I / Type II errors
Portfolio & Behavioral	Project storytelling, stakeholder management, conflicting KPIs	Tableau / Power BI dashboards	15%	Defending data cleaning steps and translating insights into revenue/savings

Visual Architecture & Process Diagrams

The Analytics Value Chain: From Raw Data to Executive DecisionArchitecture Blueprint

The four stages of business analytics maturity evaluated during technical and case study interviews.

1. Advanced SQL Mastery: Window Functions & Analytics Queries

Standard SQL queries with GROUP BY collapse rows. In contrast, Window Functions compute aggregations across a defined set of rows (the "window") while retaining individual row identities.

High-Frequency Window Functions:

`ROW_NUMBER()`: Generates a strict sequential integer starting at 1 for each row within a partition. Ideal for deduplication.
`RANK()` vs `DENSE_RANK()`: Both assign identical ranks to duplicate values. However, RANK() skips ranks after ties (1, 2, 2, 4), while DENSE_RANK() produces consecutive ranks without gaps (1, 2, 2, 3). Essential for "find top N salaries per department" questions.
`LEAD()` and `LAG()`: Accesses subsequent or preceding rows without a self-join. Ideal for calculating day-over-day growth rates or session inactivity thresholds.

2. The 5-Step Root Cause Analysis (RCA) Framework for Case Questions

When an interviewer asks: "Weekly active users on our app dropped by 12% last week. How would you investigate?", never blurt out isolated hunches ("Maybe there was a holiday"). Follow this 5-step structured diagnostic approach:

1
Clarify Scope & Verify Data Integrity:

Is the drop real, or did an upstream telemetry data pipeline fail or drop tracking events?
Was the drop sudden (pointing to an app crash, bad release, or server outage) or gradual (pointing to seasonality, macro trends, or competitor activity)?

1
Segment the Metric (Slicing & Dicing):

Platform: iOS vs Android vs Web.
Geography: Specific country, state, or language locale.
User Cohort: New users vs Power users vs Dormant returning users.
App Version: Specific to version 4.2.1 release?

1
Trace the User Funnel:

Break down the user journey: App Launch -> Login -> Search -> Checkout. Where is the drop-off concentrated?

1
Evaluate External Factors:

National holidays, network ISP outages, app store policy changes, competitor marketing blitzes.

1
Formulate Hypotheses & Actionable Solutions:

Identify root driver, quantify lost revenue impact, and outline engineering or product remediation.

High-Frequency Interview Questions & Frameworks

Write a SQL query to find the 3rd highest salary in each department. If two employees share the same salary, they should share the same rank without skipping subsequent ranks.

What the Interviewer is Testing:Proficiency with DENSE_RANK() over RANK(), PARTITION BY clauses, and Common Table Expressions (CTEs) or derived subqueries.

Strategic Approach:Explain why DENSE_RANK() is required instead of RANK(). Use a CTE to compute ranks partitioned by department_id ordered by salary descending. Filter the outer query where rank = 3.

Answer Framework:Explain logic -> Write CTE -> Apply DENSE_RANK() -> Filter rank = 3 in outer query -> Complexity and edge cases (less than 3 employees).

Sample Model Answer:

WITH RankedSalaries AS (SELECT employee_id, employee_name, department_id, salary, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS sal_rank FROM Employees) SELECT employee_id, employee_name, department_id, salary FROM RankedSalaries WHERE sal_rank = 3;

Common Mistake: Using RANK() which skips ranks after ties, or using LIMIT/OFFSET which cannot handle multiple departments in a single query.

How to Personalize: Mention how you optimize such analytic queries in data warehouses like Snowflake by selecting appropriate clustering keys.

Our e-commerce portal ran an A/B test on the checkout button color. Variant B increased conversion from 3.2% to 3.5% with a p-value of 0.03 over a 1-week test. Would you recommend rolling out Variant B to 100% of traffic?

What the Interviewer is Testing:Statistical inference, sample size adequacy, day-of-week seasonality, novelty effects, and business significance vs pure statistical significance.

Strategic Approach:Acknowledge that p = 0.03 is below the standard alpha threshold of 0.05. However, challenge the methodology: Was the sample size pre-determined via power analysis? Did the test run for full business cycles (minimum 2 weeks to capture weekend shopping patterns)? Is the lift caused by a novelty effect? What is the practical business uplift versus implementation costs?

Answer Framework:Evaluate Statistical Significance -> Check Test Duration & Seasonality -> Novelty Effect Risk -> Business/Financial Impact -> Final Recommendation.

Sample Model Answer:

While p = 0.03 is statistically significant at the 95% confidence level, I would not immediately deploy to 100% without verifying test hygiene. First, 1 week is too short; consumer purchasing patterns differ between weekdays and weekends, requiring at least two full business cycles (14 days) to eliminate seasonality. Second, minor UI changes often exhibit a temporary "novelty effect" where existing users click out of curiosity. Third, I would check sample size adequacy via minimum detectable effect (MDE) calculations. If the lift persists steadily through Week 2 and cohort retention remains healthy, I would recommend a staggered rollout (20% -> 50% -> 100%).

Common Mistake: Blindly saying "Yes, because p < 0.05" without questioning statistical power, sample size, or seasonality.

How to Personalize: Describe an actual experimentation or testing workflow you managed in your analytics work.

Costly Mistakes to Avoid

✕
Confusing correlation with causation in business case studies.
✕
Writing massive nested subqueries instead of readable, modular Common Table Expressions (CTEs).
✕
Building complex dashboards with 20 charts that overwhelm decision-makers instead of focusing on 3 actionable metrics.
✕
Misinterpreting p-values as "the probability that the hypothesis is true".
✕
Failing to validate data types and handling NULL values before running mathematical calculations.

Key Takeaways

•Great data analysts do not just report numbers; they tell actionable business stories.
•Window functions are the single most tested technical SQL skill in analytics interviews.
•Always verify data pipeline integrity before diagnosing complex business problems.
•Statistical significance means nothing if the effect size does not drive business impact.

Final Interview Checklist

Master all SQL joins (INNER, LEFT, RIGHT, FULL OUTER, CROSS) and window functions.
Be able to calculate Customer Lifetime Value (LTV), Churn, CAC, and Net Retention on a whiteboard.
Understand cohort analysis and retention heatmaps.
Know the difference between Type I error (false positive) and Type II error (false negative).
Prepare 2 complete portfolio projects with clean business problem framing, SQL transformations, and interactive BI dashboards.
Practice 5 root-cause-analysis business cases out loud using structured metric decomposition.

Frequently Asked Questions (FAQs)

What is the difference between WHERE and HAVING in SQL?

WHERE filters individual rows before any aggregation takes place. HAVING filters aggregated groups after the GROUP BY clause has been evaluated.

What is Cohort Analysis and how is it used?

Cohort Analysis groups users based on a shared characteristic (usually acquisition date or first purchase month) and tracks their behavior over time. It is the gold standard for measuring product retention, churn, and the true impact of feature updates.

What is the difference between Star Schema and Snowflake Schema in Data Warehousing?

In a Star Schema, dimension tables are completely denormalized and directly connected to a central fact table, resulting in faster query performance with simpler joins. In a Snowflake Schema, dimension tables are normalized into sub-dimension tables, saving storage space but requiring more complex multi-table joins.

What is Simpson’s Paradox in data analysis?

Simpson’s Paradox occurs when a statistical trend or correlation appears in different groups of data, but disappears or reverses when the groups are combined. It highlights the danger of aggregating data without controlling for confounding variables.

How do you handle missing or NULL data in analytics?

First investigate whether missingness is Missing Completely at Random (MCAR), Missing at Random (MAR), or Missing Not at Random (MNAR). Options include: dropping rows (if minimal), imputing with median/mode, creating an explicit "Unknown" category, or using forward-fill for time-series data.

What is the difference between Power BI and Tableau?

Power BI integrates seamlessly with Microsoft ecosystems, uses DAX for data modeling, and is cost-effective for enterprise reporting. Tableau excels at advanced custom visual exploration, complex calculations, and handles massive multi-source datasets with refined design aesthetics.

What is an A/B test and when can you NOT run an A/B test?

An A/B test splits users into control and treatment groups to measure the causal impact of a feature. You cannot run an A/B test when traffic volume is too low to achieve statistical power, when network effects cause interference between groups (like Uber rider/driver markets), or when testing pricing that could trigger user backlash.

What projects should I include in a Data Analyst portfolio?