Data Analyst interview questions

These three roles are related but have distinct focuses and skill sets. Data Analyst: - Analyzes existing data to answer business questions and support decisions - Typical work: dashboards, reports, ad-hoc queries, trend analysis - Core tools: SQL, Excel, Tableau, Power BI, Google Analytics - Background: business understanding + data skills - Question answered: "What happened? Why did it happen? What should we do?" Data Scientist: - Builds predictive/statistical models; applies machine learning - Typical work: churn prediction, recommendation engines, A/B testing analysis, forecasting - Core tools: Python (pandas, sklearn, PyTorch), R, Jupyter, Spark - Background: statistics + ML + programming - Question answered: "What will happen? What's the best action to take?" Data Engineer: - Builds and maintains the infrastructure that makes data available - Typical work: ETL/ELT pipelines, data warehouses, data lakes, streaming pipelines - Core tools: Spark, Kafka, Airflow, dbt, Snowflake, BigQuery, SQL - Background: software engineering + distributed systems - Question answered: "How do we get clean, reliable data into the hands of analysts and scientists?" How they interact: Data Engineer → builds pipelines → Data Analyst and Data Scientist consume the data In small companies, one person often wears all three hats. In large companies, each role is specialized. For a DA interview, be clear: your primary value is turning data into actionable business insights, not building ML models or data infrastructure.

SELECT c.customer_id, c.customer_name, SUM(o.order_amount) AS total_revenue FROM customers c JOIN orders o ON c.customer_id = o.customer_id WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days' GROUP BY c.customer_id, c.customer_name ORDER BY total_revenue DESC LIMIT 5; Key concepts used: - JOIN: Links customers table to orders table via customer_id - WHERE with date filter: CURRENT_DATE - INTERVAL '30 days' (PostgreSQL/standard SQL); use DATEADD(day, -30, GETDATE()) in SQL Server or DATE_SUB(CURDATE(), INTERVAL 30 DAY) in MySQL - GROUP BY: Aggregates rows by customer - SUM(): Adds up all order amounts per customer - ORDER BY DESC: Highest revenue first - LIMIT 5: Returns only top 5 Common interview variations: - Include customers with zero orders (use LEFT JOIN instead of JOIN) - Break ties: add a secondary sort (ORDER BY total_revenue DESC, c.customer_name ASC) - Use a subquery or CTE for readability: WITH recent_orders AS ( SELECT customer_id, SUM(order_amount) AS total_revenue FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '30 days' GROUP BY customer_id ) SELECT c.customer_name, r.total_revenue FROM recent_orders r JOIN customers c ON r.customer_id = c.customer_id ORDER BY r.total_revenue DESC LIMIT 5;

A JOIN combines rows from two or more tables based on a related column. Types: 1. INNER JOIN - Returns only rows that have matching values in BOTH tables - Most common join - Example: Orders with matching customers only (orphaned orders excluded) SELECT * FROM orders o INNER JOIN customers c ON o.customer_id = c.customer_id; 2. LEFT JOIN (LEFT OUTER JOIN) - Returns ALL rows from the left table + matching rows from the right table - Non-matching right side = NULL - Use case: Find all customers including those who haven't placed any orders SELECT c.name, o.order_id FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id; 3. RIGHT JOIN (RIGHT OUTER JOIN) - Returns ALL rows from the right table + matching rows from the left table - Rarely used; can always be rewritten as a LEFT JOIN by swapping tables 4. FULL OUTER JOIN - Returns ALL rows from BOTH tables; NULL where no match - Use case: Find all customers and all orders, including unmatched records on either side 5. CROSS JOIN - Returns every combination of rows from both tables (Cartesian product) - No ON condition - Use case: Generate all possible pairs (e.g., all products × all stores for a matrix report) 6. SELF JOIN - A table joined to itself - Use case: Find employees and their managers in the same employees table SELECT e.name AS employee, m.name AS manager FROM employees e JOIN employees m ON e.manager_id = m.employee_id; Key interview tip: INNER JOIN is the default when you just write JOIN. Always specify your intent clearly.

Window functions perform calculations across a set of rows that are related to the current row — without collapsing the result like GROUP BY does. Syntax: function_name() OVER ( PARTITION BY column1 ORDER BY column2 ROWS/RANGE BETWEEN ... ) Common window functions: 1. ROW_NUMBER() — unique sequential number per partition SELECT customer_id, order_date, order_amount, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_num FROM orders; 2. RANK() / DENSE_RANK() - RANK: gaps in ranking when ties exist (1, 1, 3) - DENSE_RANK: no gaps (1, 1, 2) SELECT product_name, sales, RANK() OVER (ORDER BY sales DESC) AS sales_rank FROM products; 3. SUM() / AVG() as window functions (running totals) SELECT order_date, order_amount, SUM(order_amount) OVER (ORDER BY order_date) AS running_total FROM orders; 4. LAG() / LEAD() — access previous/next row values SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue, revenue - LAG(revenue) OVER (ORDER BY month) AS month_over_month_change FROM monthly_revenue; Real use case — Find top 3 orders per customer: SELECT * FROM ( SELECT customer_id, order_id, order_amount, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_amount DESC) AS rn FROM orders ) ranked WHERE rn <= 3; Key difference from GROUP BY: Window functions return a value for each row; GROUP BY returns one row per group.

A pivot table is an Excel feature that allows you to summarize, analyze, and explore large datasets interactively by aggregating and reorganizing data. When to use: - Summarizing thousands of rows into a compact report - Cross-tabulating data (rows vs. columns) - Finding totals, averages, counts, or other aggregates quickly - Slicing data by different dimensions (by region, by month, by product) Anatomy of a pivot table: - Rows: The grouping variable (e.g., product category, region, sales rep) - Columns: Optional cross-tabulation (e.g., month, year, quarter) - Values: What you're aggregating (sum of revenue, count of orders, average score) - Filters: Global filter applied to the entire pivot table (e.g., filter to only Q1) Example workflow: Raw data: 50,000 rows of sales transactions (Date, Region, Product, Salesperson, Amount) Goal: "Show me total sales by region and product category for Q3" Pivot table setup: - Rows: Region - Columns: Product Category - Values: Sum of Amount - Filter: Quarter = Q3 Result: A compact table showing Q3 revenue at the intersection of every region + category — instantly, with no formulas. Key Excel pivot table features: - Slicers: Visual filter buttons for interactive dashboards - Calculated fields: Create new metrics from existing ones (e.g., margin = (revenue - cost) / revenue) - Grouping dates: Automatically group by Month/Quarter/Year - Refresh: When source data updates, right-click → Refresh to update the pivot Interview tip: Know how to create one from scratch and how to answer "what would you use to analyze this dataset?" with a pivot table when appropriate.

Both are lookup functions in Excel, but INDEX-MATCH is more powerful and flexible. VLOOKUP: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) Limitations: 1. Can only look to the RIGHT — the lookup column must be the leftmost column in the table 2. References columns by number (col_index_num) — breaks if you insert a column 3. Performance degrades on very large datasets 4. Can't handle duplicate lookup values elegantly INDEX-MATCH: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)) How it works: - MATCH finds the row position of the lookup value in the lookup range - INDEX returns the value at that position from the return range Advantages over VLOOKUP: 1. Can look LEFT — return column can be anywhere relative to the lookup column 2. References actual ranges, not column numbers — doesn't break when columns are inserted 3. Faster on large datasets (MATCH returns a number once; INDEX is fast) 4. More flexible for two-way lookups, approximate matches Example — Both doing the same thing: VLOOKUP: =VLOOKUP(A2, D:F, 2, 0) -- returns column E value INDEX-MATCH: =INDEX(E:E, MATCH(A2, D:D, 0)) -- same result Left lookup example (VLOOKUP can't do this): Return column B value when lookup column is C: =INDEX(B:B, MATCH(A2, C:C, 0)) -- ✓ works =VLOOKUP(A2, B:C, -1, 0) -- ✗ impossible Modern Excel (2021+): XLOOKUP replaces both: =XLOOKUP(lookup_value, lookup_array, return_array) - Looks in any direction; returns any column; handles errors natively; supports wildcards

Data cleaning (or data wrangling) is the process of detecting and correcting errors, inconsistencies, and missing values in a dataset before analysis. "Garbage in, garbage out" — even the best analysis produces wrong answers on dirty data. Common data quality issues: 1. Missing values (nulls/blanks) - Some fields were never filled in, or data was lost - Options: drop rows (if rare), impute (fill with mean/median/mode), or flag as unknown - Example: 15% of customer income fields are blank 2. Duplicate records - Same record entered multiple times, often from merging data sources - Fix: identify by unique key + business logic; deduplicate - Example: Same customer appears 3 times with slightly different email spellings 3. Inconsistent formatting - Dates stored as "12/31/2025", "2025-12-31", "31-Dec-25" in the same column - Names as "RAVIN SINGH", "Ravin Singh", "ravin singh" - Fix: standardize to a single format (ISO 8601 for dates; UPPER/LOWER for strings) 4. Outliers - Extreme values that may be errors or genuine anomalies - Example: Age = 999, transaction amount = ₹0 - Fix: validate against business rules; remove or cap if clearly erroneous 5. Wrong data types - Numeric values stored as text ("$1,234" instead of 1234) - Dates stored as strings - Fix: type conversion; regex cleaning 6. Referential integrity violations - Orders referencing customer IDs that don't exist - Fix: investigate source; flag orphaned records 7. Inaccurate data - Data that's correctly formatted but factually wrong - Hardest to detect; requires domain knowledge or cross-validation Data cleaning tools: - Excel: Remove Duplicates, Text to Columns, TRIM(), CLEAN(), IFERROR() - Python: pandas (dropna, fillna, astype, str.strip, duplicated) - SQL: COALESCE, CAST, DISTINCT, TRIM, CASE statements - Power Query (Excel/Power BI): visual data transformation interface

Tableau is a leading data visualization tool that enables analysts to create interactive dashboards from various data sources without writing code. Core Tableau concepts: - Dimensions: Categorical data (Region, Product Category, Customer Name) - Measures: Numeric data (Revenue, Profit, Quantity) - Marks: The visual elements (bars, lines, circles, colors, sizes) - Workbook: the file; contains sheets, dashboards, and stories - Sheet: a single visualization; Dashboard: multiple sheets combined Building a Sales Dashboard — step by step: 1. Connect to data - Connect Tableau to your data source (Excel, SQL Server, Google Sheets, Snowflake, etc.) - Preview and verify field types; rename if needed 2. Create individual visualizations (sheets) a) Total Revenue KPI - Drag [Order Amount] to Text → SUM(Order Amount) displayed as a number - Format as currency (₹) b) Monthly Revenue Trend (line chart) - Columns: MONTH([Order Date]); Rows: SUM([Order Amount]) - Tableau auto-creates a line chart c) Revenue by Region (bar chart) - Rows: [Region]; Columns: SUM([Order Amount]) - Sort descending; color by region d) Product Category Breakdown (treemap or pie) - Drag [Category] to Color, SUM([Order Amount]) to Size 3. Assemble the dashboard - New Dashboard → drag sheets from left panel onto the canvas - Arrange KPI at top, trend chart in center, region bar chart on right - Add filters (Date Range, Region) — set filter actions to affect all sheets 4. Add interactivity - Click on a region bar → other charts filter to that region - Add a date range filter for dynamic time selection 5. Publish - Publish to Tableau Server or Tableau Public for sharing Key chart types a DA should know: bar, line, scatter, map, treemap, heatmap, waterfall, funnel

Power BI is Microsoft's business intelligence platform for creating interactive reports and dashboards. It's the closest competitor to Tableau in the BI market. Power BI components: - Power BI Desktop: Free Windows application for building reports - Power BI Service: Cloud platform (powerbi.com) for publishing, sharing, and collaboration - Power BI Mobile: iOS/Android app for consuming reports - Power Query: Data transformation/cleaning tool (M language) - DAX: Data Analysis Expressions — Power BI's formula language for calculated columns and measures Power BI vs. Tableau comparison: | Dimension | Power BI | Tableau | |---|---|---| | Cost | Free desktop; Pro ₹700/user/mo | Higher cost; Tableau Creator ~₹5,000/mo | | Ecosystem | Deep Microsoft integration (Excel, Azure, Teams, SharePoint) | More data source connectors | | Learning curve | Easier for Excel users | Steeper; more visual options | | Language | DAX for calculations; M for Power Query | Calculated fields with simpler syntax | | Visualization | Good; improving | Better; more chart customization | | Performance | Strong on Microsoft stack | Better for very large datasets | | Sharing | Requires Power BI Service license | Tableau Server or Tableau Online | DAX example (Total Revenue measure): Total Revenue = SUM(Orders[Order Amount]) YoY Growth % measure: YoY Growth % = DIVIDE([This Year Sales] - [Last Year Sales], [Last Year Sales]) When to recommend which: - Organization uses Microsoft 365/Azure → Power BI (natural fit, lower cost) - Data team uses diverse sources, needs advanced viz → Tableau - Small business or startup → Power BI (free tier for individuals) Most Indian companies use Power BI due to cost and Microsoft familiarity.

A data analyst doesn't need PhD-level statistics, but these core concepts are essential. 1. Descriptive Statistics - Mean: average (sensitive to outliers) - Median: middle value (robust to outliers) — "average salary" should use median! - Mode: most frequent value - Standard deviation / variance: measure of spread - Min, max, quartiles (25th, 50th, 75th percentile), IQR (interquartile range) 2. Distributions - Normal (bell curve): symmetric; mean = median = mode - Skewed: positively skewed (tail to right — income data), negatively skewed - Understanding skew helps choose the right summary statistic (mean vs. median) 3. Correlation vs. Causation - Correlation: two variables move together (ice cream sales and drowning rates both peak in summer) - Causation: one variable CAUSES the other - "Correlation does not imply causation" — one of the most important data analyst mantras 4. Probability Basics - Understanding probability distributions helps with forecasting and risk analysis - P(A and B) = P(A) × P(B) for independent events 5. Hypothesis Testing (basics) - Null hypothesis (H0): "There is no effect / no difference" - Alternative hypothesis (H1): "There is an effect" - p-value: probability of seeing this result if H0 is true; p < 0.05 → reject H0 (statistically significant) - Common tests: t-test (compare means), chi-square (compare categories) 6. A/B Testing - Random assignment of users to Control (A) and Treatment (B) groups - Measure metric difference; use statistical test to confirm it's not random - Used heavily in product analytics, marketing, and UX 7. Percentiles and percentile rank - "90th percentile response time = 850ms" means 90% of requests are faster than 850ms 8. Outlier detection - Z-score: how many standard deviations from the mean (|z| > 3 = outlier) - IQR method: below Q1 - 1.5×IQR or above Q3 + 1.5×IQR

Cohort analysis groups users who share a common characteristic (usually the time they first performed an action) and tracks their behavior over time. Most common type: Acquisition cohort - Group users by the month/week they signed up - Track what % remain active after 1, 2, 3, 6, 12 months - Reveals retention trends Why it's powerful: - Averages can hide important trends. "We have 100,000 monthly active users" sounds good — but if retention is 5%, those users are new every month and the product has a serious problem. - Cohort analysis shows if retention is improving or worsening over time (cohorts getting better = product is improving) Cohort retention table (example): | Signup Month | Month 1 | Month 2 | Month 3 | Month 6 | |---|---|---|---|---| | Jan 2026 | 100% | 45% | 32% | 18% | | Feb 2026 | 100% | 50% | 38% | 22% | | Mar 2026 | 100% | 55% | 42% | 27% | Reading this: Each row's numbers improve → product changes are improving retention. The Feb cohort retained better at Month 2 (50%) vs Jan (45%). Other types of cohort analysis: - Behavioral cohort: group by action taken (users who used feature X vs. didn't) - Revenue cohort: group by initial purchase size; track lifetime value How to build one in SQL: WITH first_purchase AS ( SELECT user_id, MIN(order_date) AS cohort_date FROM orders GROUP BY user_id ), cohort_activity AS ( SELECT f.user_id, DATE_TRUNC('month', f.cohort_date) AS cohort_month, DATE_TRUNC('month', o.order_date) AS activity_month FROM first_purchase f JOIN orders o ON f.user_id = o.user_id ) -- then count users per cohort_month × activity_month and divide by cohort size Tools: SQL + Excel/Tableau, Mixpanel, Amplitude, Google Analytics

OLTP (Online Transaction Processing) and OLAP (Online Analytical Processing) are designed for fundamentally different use cases. OLTP: - Purpose: Supports daily business operations — recording transactions as they happen - Operations: High volume of short CRUD operations (INSERT, UPDATE, DELETE, SELECT by primary key) - Schema: Highly normalized (3NF+) — minimizes data redundancy, maximizes write efficiency - Data: Current, up-to-date operational data - Performance: Optimized for fast individual writes; many concurrent users - Examples: MySQL, PostgreSQL, SQL Server, Oracle — your e-commerce order system, bank transaction system - Typical query: "SELECT * FROM orders WHERE order_id = 12345" OLAP: - Purpose: Supports analytical queries — historical reporting and business intelligence - Operations: Complex queries aggregating large volumes of data; read-heavy, rare writes - Schema: Denormalized — star schema or snowflake schema; optimized for read performance - Data: Historical, often loaded in batches from OLTP systems via ETL/ELT - Performance: Optimized for fast aggregations across millions of rows - Examples: Snowflake, BigQuery, Redshift, Azure Synapse, Databricks, SQL Server Analysis Services - Typical query: "Show me total revenue by region and product category for the last 12 months" Data warehouse pipeline: OLTP systems → ETL/ELT → Data Warehouse (OLAP) → BI tools (Tableau, Power BI) Star schema (OLAP): - Fact table: measurements/transactions (sales_fact: date_id, product_id, customer_id, amount) - Dimension tables: descriptive attributes (dim_product, dim_customer, dim_date) Why a DA needs to know this: You'll be writing queries against OLAP/data warehouse systems most of the time. Understanding the schema design helps you write efficient queries and find the right tables.