1500+ Data Analyst Interview Questions and Answers

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

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%';

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

To cut a cylindrical cake into 8 equal pieces with 3 straight cuts, make 2 cuts parallel to the base and 1 cut perpendicular to the base.

• Make the first cut horizontally through the middle of the cake to create two equal halves.

• Make the second cut vertically through the center of one of the halves to create two equal quarters.

• Make the third cut horizontally through the middle of one of the quarters to create two equal eighths.

I would consider the offer based on the overall compensation package and career growth opportunities.

• I would need more information on the overall compensation package to make a decision.

• I would also consider the potential career growth opportunities and learning experiences at the company.

• It would be important to understand the criteria and timing for receiving the variable pay.

• I would evaluate the offer based on my long-term career goals and personal financial situation.

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

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

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.

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

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.

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'.

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

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

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

ANOVA is used to compare means across multiple groups to determine if at least one group differs significantly.

• ANOVA is used when comparing three or more groups to see if their means are statistically different.

• Example: Testing the effectiveness of three different diets on weight loss.

• It helps in understanding the impact of categorical independent variables on a continuous dependent variable.

• Example: Analyzing test scores of students from different teaching methods.

• ANOVA can ...read more

Metrics selection based on campaign objectives, target audience, and key performance indicators.

• Identify campaign objectives and goals

• Consider target audience and their behavior

• Select key performance indicators (KPIs) relevant to the campaign

• Track metrics such as conversion rate, click-through rate, ROI, customer acquisition cost

• Analyze data to measure success and make data-driven decisions

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

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

Discussed machine learning topics in exploratory data analysis and deep learning during interview.

• Discussed the importance of exploratory data analysis in understanding the dataset before applying machine learning algorithms.

• Explained the basics of deep learning, including neural networks, activation functions, and backpropagation.

• Described a puzzle related to machine learning, such as predicting customer churn based on historical data.

Query to get number of matches played and won by each team from a table of match details.

• Use GROUP BY clause to group the data by team name.

• Use COUNT() function to get the number of matches played by each team.

• Use SUM() function with a condition to get the number of matches won by each team.

Use SQL query to count number of reportees for each manager and filter out those with atleast 5 reportees.

• Write a SQL query to count number of reportees for each manager using GROUP BY clause

• Add HAVING clause to filter out managers with atleast 5 reportees

• Example: SELECT managerId, COUNT(id) AS num_reportees FROM table_name GROUP BY managerId HAVING num_reportees >= 5

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

Financial market is a platform where buyers and sellers trade financial assets such as stocks, bonds, currencies, and commodities.

• Financial market facilitates the exchange of financial assets between buyers and sellers

• It includes stock markets, bond markets, currency markets, and commodity markets

• Financial market plays a crucial role in the economy by allocating capital to businesses and governments

• Examples of financial markets include NYSE, NASDAQ, London Stock Exchange, and...read more

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.

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

Outliers can be detected using statistical methods and treated by either removing them or replacing them with a more appropriate value.

• Use box plots, scatter plots, or histograms to visually identify outliers

• Calculate the z-score or interquartile range (IQR) to identify outliers statistically

• Remove outliers if they are due to data entry errors or measurement errors

• Replace outliers with a more appropriate value if they are valid data points but extreme

• Consider the impact of ou...read more

A list is mutable and can be modified, while a tuple is immutable and cannot be changed. List comprehension is a concise way to create lists.

• List: Mutable, can be modified. Tuple: Immutable, cannot be changed.

• List: Defined with square brackets []. Tuple: Defined with parentheses ().

• List comprehension: Concise way to create lists by iterating over an iterable and applying an expression.

• Example of list comprehension: squares = [x**2 for x in range(10)]