Microsoft Data Analyst Interview Journey – A 20 LPA Experience!

One of my friends recently interviewed for a Data Analyst role at Microsoft, and the process was truly intense — it focused heavily on data while also testing how well candidates could think strategically and tell a compelling story through their analysis!

---

  Round 1 – SQL + Case-Based Analytics

Q 1. How to Write a SQL Query to Find Top 3 Regions by Monthly Sales Performance?

1. ANS- Use SUM(sales) to aggregate monthly totals.
2. Apply PARTITION BY month and ORDER BY sales DESC inside RANK() or DENSE_RANK().
3. Filter where rank ≤ 3 to get top performers.

WITH RankedSales AS (
  SELECT region, MONTH(sale_date) AS month,
         SUM(sales) AS total_sales,
         RANK() OVER (PARTITION BY MONTH(sale_date) ORDER BY SUM(sales) DESC) AS rank
  FROM sales_data
  GROUP BY region, MONTH(sale_date)
)
SELECT * FROM RankedSales WHERE rank <= 3;

Use Case: Monthly leaderboard for regional sales teams.

---

Q 2. SQL Challenge: Identifying Customers with Consecutive Monthly Transactions?

1. ANS- Extract YYYY-MM from purchase dates.
2. Use LEAD() to compare current and next months.
3. Filter where next month = current + 1.

WITH MonthlyPurchases AS (
  SELECT customer_id, TO_CHAR(purchase_date, 'YYYY-MM') AS month
  FROM purchases
  GROUP BY customer_id, TO_CHAR(purchase_date, 'YYYY-MM')
),
Consecutive AS (
  SELECT customer_id, month,
         LEAD(month) OVER (PARTITION BY customer_id ORDER BY month) AS next_month
  FROM MonthlyPurchases
)
SELECT * FROM Consecutive
WHERE TO_DATE(next_month, 'YYYY-MM') = ADD_MONTHS(TO_DATE(month, 'YYYY-MM'), 1);

Use Case: Loyalty analysis and churn prevention.

---

Q 3. Detecting Churned Users from Activity Logs Using SQL?

1. Define churn threshold (e.g., 30 days of inactivity).
2. Use MAX(activity_date) per user.
3. Compare with current date.

SELECT user_id
FROM user_logs
GROUP BY user_id
HAVING MAX(activity_date) < CURRENT_DATE - INTERVAL '30 days';

Use Case: Retention campaigns and re-engagement triggers.

---

Q 4. Product Analytics Case Study: Measuring the Success of a New Feature Launch?

ANS- Define goals: adoption, engagement, retention.

1. Track metrics: DAU/MAU, feature usage rate, NPS.
2. Compare pre- and post-launch cohorts.

Example KPIs:

• Feature Adoption Rate = Users who used feature / Total active users
• Retention Uplift = Post-launch retention – Pre-launch retention

Use Case: Product roadmap validation.

---

 Round 2 – Data Interpretation + Excel + Business Logic

Q 5. How to Analyze Sales vs. Marketing Spend in Excel for Actionable Business Insights?

1. ANS-Clean and align time-series data.
2. Use pivot tables to compare spend vs. revenue.
3. Create scatter plots and trendlines.
4. Calculate ROI = (Sales – Spend) / Spend

Use Case: Campaign performance and budget allocation.

---

Q 6. Top KPIs Every Data Analyst Should Create to Track Product Growth Over Time?

1. DAU/MAU Ratio – Engagement consistency.
2. Retention Rate – Stickiness.
3. Feature Adoption – Usage depth.
4. CLV – Long-term value.
5. NPS – User satisfaction.

Use Case: Growth dashboards and investor reporting.

---

Q 7. Retention Rate Estimation: Solving Drop-Off and Return Scenarios in Customer Analytics?

1. ANS- Define initial cohort size.
2. Subtract drop-offs.
3. Add returning users.
4. Apply formula:

Retention Rate = (Remaining Users + Returned Users) / Initial Users

Example:

Initial: 1000
Drop: 300
Return: 200
Retention = (700 + 200) / 1000 = 90%

Use Case: Lifecycle marketing and churn modeling.

---

 Round 3 – Python + Visualization

Q 8. Python Coding Task: Calculating Correlation Between Multiple Variables in a Dataset?

1. ANS- Load dataset with pandas.
2. Use df.corr() to compute correlation.
3. Visualize with seaborn.heatmap().

import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt

df = pd.read_csv('data.csv')
corr = df.corr()
sns.heatmap(corr, annot=True)
plt.show()

Use Case: Feature selection and EDA.

---

Q 9. How to Build a Regional Sales vs. Target Dashboard Using Power BI or Tableau?

ANS-

Load sales and target data.

1. Create calculated fields: % Achievement = Sales / Target.
2. Use bar charts or combo charts.
3. Add slicers for region and time.

Use Case:

Sales performance tracking.

---

Q 10. Automating Weekly Report Refresh in Power BI with Python – Step-by-Step Guide?

1. ANS- Use Power BI REST API or pywinauto for desktop automation.
2. Schedule Python script with schedule or cron.
3. Ensure gateway setup for cloud refresh.

import schedule
import time
import os

def refresh_report():
    os.system("start powerbi_report.pbix")

schedule.every().monday.at("08:00").do(refresh_report)

while True:
    schedule.run_pending()
    time.sleep(1)

Use Case: Hands-free reporting for stakeholders.

Key Tip: Microsoft loves structured problem-solving and business context—focus on why the analysis matters, not just how to do it.