Microsoft Senior Data Analyst Interview Questions and Answers 2026

WITH daily_dau AS (
    SELECT
        activity_date,
        COUNT(DISTINCT user_id) AS dau
    FROM user_activity
    GROUP BY activity_date
)
SELECT
    activity_date,
    dau,
    AVG(dau) OVER (
        ORDER BY activity_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7d_avg
FROM daily_dau
ORDER BY activity_date;

SELECT
    e.name         AS employee_name,
    e.salary       AS employee_salary,
    m.name         AS manager_name,
    m.salary       AS manager_salary
FROM employees e
JOIN employees m
    ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

WITH monthly_rev AS (
    SELECT
        DATEADD(MONTH, DATEDIFF(MONTH, 0, order_date), 0) AS month_start,
        SUM(revenue) AS total_revenue
    FROM orders
    GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, order_date), 0)
)
SELECT
    month_start,
    total_revenue,
    LAG(total_revenue) OVER (ORDER BY month_start) AS prev_month_revenue,
    ROUND(
        100.0 * (total_revenue - LAG(total_revenue) OVER (ORDER BY month_start))
              / NULLIF(LAG(total_revenue) OVER (ORDER BY month_start), 0),
        1
    ) AS mom_growth_pct
FROM monthly_rev
ORDER BY month_start;

WITH date_range AS (
    SELECT MIN(metric_date) AS start_date,
           MAX(metric_date) AS end_date
    FROM daily_metrics
),
all_dates AS (
    -- Recursive CTE to generate every date in range
    SELECT start_date AS dt FROM date_range
    UNION ALL
    SELECT DATEADD(DAY, 1, dt)
    FROM all_dates
    WHERE dt < (SELECT end_date FROM date_range)
)
SELECT a.dt AS missing_date
FROM all_dates a
LEFT JOIN daily_metrics d ON a.dt = d.metric_date
WHERE d.metric_date IS NULL
OPTION (MAXRECURSION 3650);  -- allow up to 10 years

WITH user_totals AS (
    SELECT
        user_id,
        SUM(duration_seconds) AS total_seconds,
        NTILE(10) OVER (ORDER BY SUM(duration_seconds) DESC) AS decile
    FROM sessions
    WHERE session_date >= DATEADD(DAY, -30, GETDATE())
    GROUP BY user_id
)
SELECT
    user_id,
    total_seconds,
    ROUND(total_seconds / 3600.0, 1) AS total_hours
FROM user_totals
WHERE decile = 1
ORDER BY total_seconds DESC;

import pandas as pd
from datetime import datetime

snapshot_date = datetime(2025, 5, 1)

rfm = (
    df.groupby("customer_id").agg(
        recency   = ("order_date", lambda x: (snapshot_date - x.max()).days),
        frequency = ("order_id",   "nunique"),
        monetary  = ("revenue",    "sum")
    ).reset_index()
)

# Score each dimension 1-5 (5 = best)
rfm["r_score"] = pd.qcut(rfm["recency"],   5, labels=[5,4,3,2,1])
rfm["f_score"] = pd.qcut(rfm["frequency"].rank(method="first"), 5, labels=[1,2,3,4,5])
rfm["m_score"] = pd.qcut(rfm["monetary"],  5, labels=[1,2,3,4,5])

rfm["rfm_score"] = (
    rfm["r_score"].astype(int)
  + rfm["f_score"].astype(int)
  + rfm["m_score"].astype(int)
)

def segment(score):
    if score >= 13: return "Champion"
    if score >= 9:  return "Loyal"
    if score >= 6:  return "At Risk"
    return "Churned"

rfm["segment"] = rfm["rfm_score"].apply(segment)
print(rfm["segment"].value_counts())

from statsmodels.stats.proportion import proportions_ztest
import numpy as np

n_control  = 10_000
n_variant  = 10_000
conv_ctrl  = 0.048  # 4.8%
conv_var   = 0.052  # 5.2%

count = np.array([conv_var * n_variant, conv_ctrl * n_control])
nobs  = np.array([n_variant, n_control])

z_stat, p_value = proportions_ztest(count, nobs, alternative='larger')

print(f"Z-statistic : {z_stat:.3f}")
print(f"P-value     : {p_value:.4f}")
print(f"Significant : {'Yes ✓' if p_value < 0.05 else 'No ✗'}")

# Minimum detectable effect sanity check
lift = (conv_var - conv_ctrl) / conv_ctrl * 100
print(f"Relative lift: {lift:.1f}%")

import pandas as pd
import json

# Simulated Graph API response
raw = '''
{
  "value": [
    {"userId": "u1", "displayName": "Alice",
     "activities": [{"type": "login", "ts": "2025-05-01"},
                    {"type": "file_edit", "ts": "2025-05-02"}]},
    {"userId": "u2", "displayName": "Bob",
     "activities": [{"type": "login", "ts": "2025-05-01"}]}
  ]
}'''

data = json.loads(raw)

rows = []
for user in data["value"]:
    for activity in user.get("activities", []):
        rows.append({
            "user_id"    : user["userId"],
            "name"       : user["displayName"],
            "event_type" : activity["type"],
            "event_ts"   : activity["ts"],
        })

df = pd.DataFrame(rows)
df["event_ts"] = pd.to_datetime(df["event_ts"])
print(df)

funnel = df.groupby("funnel_step")["user_id"].nunique().reset_index()
funnel.columns = ["step", "users"]

# Order steps manually
step_order = ["Invited", "Clicked", "Activated", "Retained"]
funnel["step"] = pd.Categorical(funnel["step"], categories=step_order, ordered=True)
funnel = funnel.sort_values("step").reset_index(drop=True)

top = funnel.loc[0, "users"]
funnel["pct_of_top"]  = (funnel["users"] / top * 100).round(1)
funnel["step_dropoff"] = funnel["users"].pct_change().mul(100).round(1)

print(funnel[["step","users","pct_of_top","step_dropoff"]])

-- CALCULATED COLUMN (row context, stored in model)
-- Adds a physical column to the table — computed at refresh time
Revenue Per User Column =
    DIVIDE(Sales[revenue], Sales[user_count], 0)


-- CALCULATED MEASURE (filter context, computed at query time)
-- Evaluated dynamically based on slicer/visual filters
Revenue Per User =
    DIVIDE(
        SUM(Sales[revenue]),
        DISTINCTCOUNT(Sales[user_id]),
        0
    )

-- FACT TABLE (grain = one row per user per product per day)
fact_usage (
    usage_id      BIGINT         PRIMARY KEY,
    date_key      INT            FK → dim_date,
    user_key      INT            FK → dim_user,
    product_key   INT            FK → dim_product,
    tenant_key    INT            FK → dim_tenant,
    sessions      INT,
    active_minutes INT,
    files_created INT,
    files_shared  INT
)

-- DIMENSION: Date
dim_date (date_key, full_date, year, quarter, month, week, weekday, is_holiday)

-- DIMENSION: User (SCD Type 2 for role changes)
dim_user (user_key, user_id, email, department, role, country, is_current)

-- DIMENSION: Product
dim_product (product_key, product_name, product_family, tier)

-- DIMENSION: Tenant (customer organisation)
dim_tenant (tenant_key, tenant_id, industry, size_band, region, subscription_plan)