Data Analyst Interview Questions — SQL, Python, Power BI, Statistics & HR Rounds Fully Answered

SELECT
    customer_id,
    region,
    revenue,
    RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS revenue_rank,
    SUM(revenue) OVER (PARTITION BY region) AS region_total,
    revenue / SUM(revenue) OVER (PARTITION BY region) * 100 AS pct_of_region
FROM orders;

-- Find duplicate emails
SELECT email, COUNT(*) AS occurrences
FROM customers
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY occurrences DESC;

-- Find the actual duplicate rows (all columns)
SELECT *
FROM customers
WHERE email IN (
    SELECT email FROM customers
    GROUP BY email
    HAVING COUNT(*) > 1
);

WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(revenue) AS total_rev
    FROM orders
    GROUP BY 1
),
ranked AS (
    SELECT *,
        LAG(total_rev) OVER (ORDER BY month) AS prev_month_rev
    FROM monthly_revenue
)
SELECT
    month,
    total_rev,
    ROUND((total_rev - prev_month_rev) / prev_month_rev * 100, 1) AS mom_growth_pct
FROM ranked;

-- Method 1: subquery (works in all databases)
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Method 2: DENSE_RANK window function (preferred — handles ties correctly)
SELECT salary
FROM (
    SELECT salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) ranked
WHERE rnk = 2
LIMIT 1;

# Drop rows where the key identifier is missing
df = df.dropna(subset=['order_id', 'customer_id'])

# Fill with business-appropriate default
df['discount_pct'] = df['discount_pct'].fillna(0)
df['city'] = df['city'].fillna('Unknown')

# Fill numerical columns with median (more robust than mean for skewed data)
df['revenue'] = df['revenue'].fillna(df['revenue'].median())

# Check result
print(df.isnull().sum())

# Method 1: sort + groupby head
top3 = (
    df.sort_values('revenue', ascending=False)
    .groupby('category')
    .head(3)
    [['category', 'product_name', 'revenue']]
    .reset_index(drop=True)
)

# Method 2: rank within group
df['rank'] = df.groupby('category')['revenue'].rank(method='dense', ascending=False)
top3 = df[df['rank'] <= 3].sort_values(['category', 'rank'])

-- Revenue from Online channel only, regardless of page-level channel filter
Online Revenue =
    CALCULATE(
        SUM(Sales[Revenue]),
        Sales[Channel] = "Online"
    )

-- Revenue % of grand total (remove all filters with ALL())
Revenue % of Total =
    DIVIDE(
        SUM(Sales[Revenue]),
        CALCULATE(SUM(Sales[Revenue]), ALL(Sales)),
        0
    )

Revenue PY =
    CALCULATE(
        SUM(Sales[Revenue]),
        SAMEPERIODLASTYEAR('Date'[Date])
    )

Revenue YoY % =
    VAR cur = SUM(Sales[Revenue])
    VAR py  = [Revenue PY]
    RETURN DIVIDE(cur - py, py, 0)

Stakeholders and purpose: Start by asking who uses the dashboard. A sales rep needs their own numbers. A regional manager needs cross-rep comparison. A VP needs aggregate trends. Design for one primary audience.

KPIs for a regional sales dashboard: Total revenue vs target (MTD and YTD), conversion rate (leads → closed), average deal size, pipeline value (open deals), top 5 products by revenue, revenue by rep (bar chart), monthly trend with YoY line.

Filters: Date range (slicer with MTD/QTD/YTD presets), region, product category, rep name.

Design: KPI cards at the top (revenue, target %, conversion, deal count), trend chart in the middle, decomposition tables at the bottom. Dark/light theme respecting company branding. Mobile-friendly layout for reps checking on phone.