Data Analyst Top 50 Q&A

WHERE is used to filter rows before grouping, while HAVING is used to filter groups after the GROUP BY clause is applied.

Row_Number gives a unique sequential number to each row. Rank skips numbers if there are ties (e.g., 1, 2, 2, 4). Dense_Rank does not skip numbers (e.g., 1, 2, 2, 3).

Common Table Expressions (CTEs) are temporary result sets defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. They improve readability and are useful for recursive queries.

Using COALESCE(column, replacement), ISNULL(), or CASE statements. It's important to remember that NULL != NULL.

A self join is a regular join but the table is joined with itself. It's used when a table has a foreign key that references its own primary key (e.g., employee-manager hierarchy).

Atomicity, Consistency, Isolation, and Durability. These ensure that database transactions are processed reliably.

UNION removes duplicate records from the combined result set, while UNION ALL includes all duplicates. UNION ALL is faster because it doesn't perform the distinct check.

Using a subquery: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees). Or using OFFSET: SELECT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1.

Window functions perform calculations across a set of table rows that are somehow related to the current row. Examples: SUM() OVER(), AVG() OVER(), LEAD(), LAG().

A subquery that uses values from the outer query. It's executed once for every row processed by the outer query.

Lists are mutable (can be changed) and use square brackets []. Tuples are immutable (cannot be changed) and use parentheses (). Tuples are generally faster and safer for data that shouldn't change.

Decorators are a way to modify the behavior of a function or class without changing its source code. They use the @ symbol.

loc is label-based (uses index/column names), while iloc is integer-based (uses numeric positions).

Using df.dropna() to remove rows with missing values, or df.fillna(value) to replace them with a specific value or mean/median.

An anonymous, one-line function defined with the lambda keyword. Used for short operations, often with map(), filter(), or sort().

Merge is for SQL-style joins on specific columns. Join is for joining on indexes. Concat is for stacking dataframes vertically or horizontally.

A concise way to create lists. Example: [x*x for x in range(10) if x % 2 == 0].

Using vectorized operations instead of loops, specifying data types (e.g., int32 instead of int64), and using chunking for very large files.

The Global Interpreter Lock (GIL) is a mechanism that ensures only one thread executes Python bytecode at a time, making it thread-safe but limiting multi-core performance for CPU-bound tasks.

Generators are functions that return an iterator using the yield keyword. They are memory-efficient because they produce items one at a time on demand.

The CLT states that the distribution of sample means approaches a normal distribution as the sample size increases, regardless of the original population distribution.

A P-value is the probability of obtaining results at least as extreme as the observed results, assuming the null hypothesis is true. A low p-value (< 0.05) suggests rejecting the null hypothesis.

Type I error (False Positive) is rejecting a true null hypothesis. Type II error (False Negative) is failing to reject a false null hypothesis.

Correlation is a relationship between two variables. Causation means one variable directly causes a change in the other. Correlation does not imply causation.

As the number of trials increases, the actual result will get closer and closer to the expected value or average.

A bell-shaped curve where data is symmetrical around the mean. Mean, median, and mode are all equal.

Mean is the average. Median is the middle value. Mode is the most frequent value. Median is more robust to outliers.

Standard deviation measures the amount of variation or dispersion in a set of values. Low SD means data is close to the mean.

A statistical experiment where two versions (A and B) are compared to see which performs better based on a specific metric.

An outlier is a data point significantly different from the others. You can handle them by removing them (if erroneous), capping them, or using robust statistical methods.

An Excel tool that allows you to summarize, analyze, and explore large datasets by dragging and dropping fields into different areas.

VLOOKUP searches for a value in the first column of a table. XLOOKUP is more flexible, can search in any direction, and handles missing values better.

An Excel/Power BI tool used for ETL (Extract, Transform, Load) operations. It allows you to clean and reshape data before analysis.

Data Analysis Expressions (DAX) is a formula language used in Power BI, Analysis Services, and Power Pivot in Excel to create custom calculations.

Bar charts for comparisons, Line charts for trends, Pie charts for parts of a whole (sparingly), and Scatter plots for relationships.

The process of organizing data to reduce redundancy and improve integrity, typically by dividing large tables into smaller ones.

Fact tables contain quantitative data (metrics). Dimension tables contain descriptive attributes (context) related to the metrics.

A data modeling technique where a central fact table is surrounded by several dimension tables, resembling a star.

By identifying duplicates, correcting errors, handling missing values, and standardizing formats.

The ability to communicate insights from data using a narrative that connects the dots for stakeholders, often using visualizations.

Use the STAR method. Describe the Situation, the Task you were given, the Action (analysis) you took, and the Result (insight/money saved).

I re-verify my analysis first. Then I sit with them to understand their domain knowledge and explain my methodology transparently.

Define the business problem -> Data collection -> Data cleaning -> Exploratory Data Analysis (EDA) -> Insights -> Reporting.

Following blogs like Towards Data Science, Kaggle competitions, and learning new tools like GenAI for data workflows.

Focus on a technical challenge (like inconsistent data sources) and how you overcame it using coding or logic.

Align your skills with their specific industry and mention their recent achievements or data culture.

Using analogies, avoiding jargon, and focusing on the business impact rather than the technical details.

Data Analysis focuses on past data to provide insights. Data Science uses past data to build predictive models for the future.

Using the Eisenhower Matrix (Urgent vs Important) and communicating early with stakeholders about timelines.

Ask about their data stack, their biggest current data challenge, or the team's workflow.