1000+ Data Analyst Interview Questions and Answers

Code to calculate number of people in a room at EOD given X enter and Y leave throughout the day.

• Create a variable to keep track of the current number of people in the room

• Increment the variable by X every time someone enters the room

• Decrement the variable by Y every time someone leaves the room

• Return the final value of the variable at the end of the day

• Example: If 10 people enter and 5 leave, there will be 5 people in the room at EOD

We can use statistical analysis to prove that students in higher classes are taller than those in lower classes.

• Collect height data of students from different classes

• Use statistical measures like mean, median, and mode to compare the heights of students in different classes

• Perform hypothesis testing to determine if the difference in height between classes is statistically significant

• Visualize the data using graphs and charts to make it easier for the client to understand

• Provi...read more

To find the minimum number from a set of numbers, compare each number with the others and select the smallest.

• Compare each number with the others in the set

• Select the smallest number as the minimum

Financial statements, cost sheet, debt to equity ratio, stock option, stock split, lease financing, profitability ratios.

• Financial statements are reports that show the financial performance of a company.

• Cost sheet of a bank includes interest expenses and income, while cost sheet of a manufacturing company includes direct and indirect costs.

• Debt to equity ratio is a financial ratio that shows the proportion of debt and equity used to finance a company's assets.

• Stock option is ...read more

Different approaches for data cleaning include removing duplicates, handling missing values, correcting inconsistent data, and standardizing formats.

• Remove duplicates

• Handle missing values

• Correct inconsistent data

• Standardize formats

• Use statistical methods to identify outliers

• Check for data accuracy and completeness

• Normalize data

• Transform data types

• Apply data validation rules

To work with dynamic data, remove duplicates and fix errors, use data cleaning techniques.

• Use software tools like OpenRefine or Excel to clean data

• Identify and remove duplicate data using unique identifiers

• Fix errors by standardizing data formats and using regular expressions

• Use data validation to ensure accuracy and completeness

• Create a data cleaning plan and document all changes made

• Test the cleaned data to ensure it meets the desired quality standards

The financial statements (BS, IS, CF) have common elements such as assets, liabilities, equity, revenue, expenses, and cash flows.

• Assets: resources owned by the company

• Liabilities: obligations owed by the company

• Equity: residual interest in the assets of the company

• Revenue: income generated by the company

• Expenses: costs incurred by the company

• Cash flows: inflows and outflows of cash

Questions related to data analysis techniques and methods.

• Bais and variance trade-off: balancing model complexity and accuracy

• Handling imbalanced data: resampling techniques, adjusting class weights, using different evaluation metrics

• Multicollinearity: when predictor variables are highly correlated, can be handled by feature selection or regularization

• Lasso & Ridge: regularization techniques to prevent overfitting by adding penalty terms to the loss function

• Bagging vs Boostin...read more

Data is collected from various sources including databases, APIs, and user input.

• We have access to multiple databases where we can extract relevant data

• We use APIs to gather data from external sources such as social media platforms

• Users can input data through forms or surveys

• We also collect data through web scraping techniques

The sequence of execution of SQL codes is Select-From-Where-Group By-Having-Order By.

• Select: choose the columns to display

• From: specify the table(s) to retrieve data from

• Where: filter the data based on conditions

• Group By: group the data based on a column

• Having: filter the grouped data based on conditions

• Order By: sort the data based on a column

Brownian motion is the random motion of particles in a fluid due to collisions with other particles.

• Brownian motion was first observed by Robert Brown in 1827.

• It is named after the botanist Robert Brown.

• The motion is caused by the random movement of fluid molecules.

• Brownian motion is a continuous-time stochastic process.

• It is often used to model various phenomena in physics, finance, and biology.

• Properties of Brownian motion include random walk, Gaussian distribution, and sel...read more

Code to describe database and columns from a table

• Use SQL SELECT statement to retrieve column names and data types

• Use DESC command to get table structure

• Use INFORMATION_SCHEMA.COLUMNS to get detailed information about columns

• Use SHOW CREATE TABLE to get table creation statement

A Martingale is a mathematical concept used in probability theory and statistics. Markov Processes are stochastic processes that satisfy the Markov property.

• A Martingale is a sequence of random variables where the expected value of the next variable, given the current and past variables, is equal to the current variable.

• Markov Processes are stochastic processes where the future state depends only on the current state and not on the past states.

• A Martingale is not necessarily ...read more

Questions related to finance and accounting

• IPO stands for Initial Public Offering, which is the first time a company's stock is offered to the public

• Balance sheet has two sides - assets and liabilities & equity

• Depreciation is the decrease in value of an asset over time due to wear and tear or obsolescence

• Financial statements include income statement, balance sheet, and cash flow statement

Query to retrieve all customer names starting with A.

• Use the SELECT statement to retrieve customer names.

• Use the LIKE operator with the pattern 'A%' to match names starting with A.

• Specify the table and column name where the customer names are stored.

• Example: SELECT customer_name FROM customers WHERE customer_name LIKE 'A%';

Excel functions are pre-built formulas that perform calculations or manipulate data in a spreadsheet.

• Sum: adds up a range of numbers

• Sum if: adds up a range of numbers based on a specified condition

• Count: counts the number of cells in a range that contain numbers

• CountA: counts the number of cells in a range that are not empty

• Count Blanks: counts the number of empty cells in a range

HAVING clause is used with GROUP BY to filter the results based on aggregate functions, while WHERE clause is used to filter individual rows.

• HAVING clause is used after GROUP BY clause.

• HAVING clause is used to filter the results based on aggregate functions like SUM, COUNT, AVG, etc.

• WHERE clause is used before GROUP BY clause.

• WHERE clause is used to filter individual rows based on conditions.

• HAVING clause cannot be used without GROUP BY clause.

• WHERE clause can be used without...read more

SQL is a programming language used for managing relational databases. Joins combine data from multiple tables. Union combines results from multiple queries without duplicates, while Union All includes duplicates.

• SQL is a programming language used for managing relational databases.

• Types of joins include inner join, left join, right join, and full outer join.

• Union combines results from multiple queries without duplicates.

• Union All includes duplicates in the combined result set.

Different types of Joins in SQL and the outputs when joining two tables with nulls.

• Types of joins in SQL include inner join, left join, right join, and full outer join.

• When joining two tables with nulls, the output depends on the type of join used.

• In an inner join, null values are excluded from the result.

• In a left join, all rows from the left table and matching rows from the right table are included, with nulls for non-matching rows.

• In a right join, all rows from the right t...read more

Yes, we can use the Fill Down feature in Google Sheets to fill information in all selected blank spaces in just one click.

• Select the cell with the information you want to fill

• Hover over the bottom right corner of the cell until the cursor changes to a small blue square

• Click and drag the blue square down to the last cell where you want the information to be filled

A stock split increases the number of shares outstanding and decreases the price per share, but does not affect the market capitalisation.

• Stock split does not affect the total value of the company

• Market capitalisation remains the same after a stock split

• Stock split increases the number of shares outstanding and decreases the price per share

• For example, if a company has 1 million shares outstanding and the stock splits 2-for-1, the company will have 2 million shares outstandin...read more

Yes, we can add extra row and pivot table in excel.

• To add a row, select the row below where you want to add the new row and right-click, then select 'Insert'.

• To create a pivot table, select the data range and go to 'Insert' tab, click on 'PivotTable' and follow the prompts.

• To add a new pivot table to an existing one, select a cell in the existing pivot table and go to 'Analyze' tab, click on 'PivotTable' and follow the prompts.

Create a pivot table and sort the data in ascending order.

• To create a pivot table, select the data range and go to the 'Insert' tab in Excel.

• Choose 'PivotTable' and select the location for the pivot table.

• Drag the desired fields to the 'Rows' and 'Values' areas.

• To sort the data in ascending order, click on the drop-down arrow next to the field name in the pivot table and select 'Sort A to Z'.

Aadhaar card is a unique identification card issued by the Indian government to residents of India.

• Aadhaar card is a 12-digit unique identification number issued by the Unique Identification Authority of India (UIDAI).

• It serves as proof of identity and address for Indian residents.

• Aadhaar card contains biometric and demographic information of the cardholder, including fingerprints and iris scans.

• It is used for various purposes such as opening bank accounts, getting a SIM card...read more

Permutation and combination are mathematical concepts used to count the number of possible outcomes in a given scenario.

• Permutation is the arrangement of objects in a specific order while combination is the selection of objects without considering the order.

• Permutation formula: nPr = n!/(n-r)! where n is the total number of objects and r is the number of objects selected.

• Combination formula: nCr = n!/r!(n-r)! where n is the total number of objects and r is the number of objec...read more

Find the dictionary with the highest count of keys in a list of dictionaries.

• Iterate through the list of dictionaries and keep track of the dictionary with the highest count of keys.

• Use a loop to count the keys in each dictionary and compare it with the current highest count.

• Return the dictionary with the highest count of keys.

CSV files are plain text files that store tabular data, while Excel files are binary files that can contain multiple sheets and complex formatting.

• CSV files are simpler and more lightweight compared to Excel files.

• CSV files can be easily opened and edited using a text editor, while Excel files require specific software like Microsoft Excel.

• CSV files do not support formulas, macros, or formatting options like colors and fonts, while Excel files do.

• CSV files have a smaller file...read more

Reconciliation assignment involves comparing and matching data from different sources.

• Identify sources of data to be reconciled

• Compare data from different sources

• Match data based on common identifiers

• Resolve discrepancies and errors

• Ensure accuracy and completeness of reconciled data

I have handled large amounts of data in my previous roles.

• I have experience handling terabytes of data in my previous role as a data analyst at XYZ company.

• I have worked with data from various sources such as databases, spreadsheets, and APIs.

• I have also used tools like SQL, Python, and Excel to manipulate and analyze data.

• I am comfortable working with both structured and unstructured data.

• I have experience cleaning and transforming data to make it usable for analysis.

I would query the relevant stakeholders involved in the data collection process.

• Identify the stakeholders involved in the data collection process

• Reach out to them to understand the data collection process and identify any potential issues

• Collaborate with them to resolve the TP discrepant data