Today we will preparing ourselves for Excel Interview Questions. Here are questions that we should be knowing as Fresher Data Analyst

1. What are the basic functionalities of Excel, and how are they used in data analysis?

2. Explain the difference between a worksheet and a workbook in Excel.

3. How do you perform basic arithmetic operations in Excel?

4. Discuss the significance of functions like SUM, AVERAGE, and COUNT in data analysis.

5. How do you filter and sort data in Excel?

6. Explain the importance of pivot tables in data summarization and analysis.

7. How do you create and format charts/graphs in Excel for data visualization?

8. Discuss the usage of VLOOKUP and HLOOKUP functions in Excel.

9. Explain the concept of conditional formatting and its application in Excel.

10. How do you handle missing or NaN values in Excel spreadsheets?

Hope this helps you 😊

Important Excel, Tableau, Statistics, SQL related Questions with answers

1. What are the common problems that data analysts encounter during analysis?

The common problems steps involved in any analytics project are:

Handling duplicate data
Collecting the meaningful right data at the right time
Handling data purging and storage problems
Making data secure and dealing with compliance issues

2. Explain the Type I and Type II errors in Statistics?

In Hypothesis testing, a Type I error occurs when the null hypothesis is rejected even if it is true. It is also known as a false positive.

A Type II error occurs when the null hypothesis is not rejected, even if it is false. It is also known as a false negative.

3. How do you make a dropdown list in MS Excel?

First, click on the Data tab that is present in the ribbon.
Under the Data Tools group, select Data Validation.
Then navigate to Settings > Allow > List.
Select the source you want to provide as a list array.

4. How do you subset or filter data in SQL?

To subset or filter data in SQL, we use WHERE and HAVING clauses which give us an option of including only the data matching certain conditions.

5. What is a Gantt Chart in Tableau?

A Gantt chart in Tableau depicts the progress of value over the period, i.e., it shows the duration of events. It consists of bars along with the time axis. The Gantt chart is mostly used as a project management tool where each bar is a measure of a task in the project

5 key Python Libraries/ Concepts that are particularly important for Data Analysts

1. Pandas: Pandas is a powerful library for data manipulation and analysis in Python. It provides data structures like DataFrames and Series that make it easy to work with structured data. Pandas offers functions for reading and writing data, cleaning and transforming data, and performing data analysis tasks like filtering, grouping, and aggregating.

2. NumPy: NumPy is a fundamental package for scientific computing in Python. It provides support for large, multi-dimensional arrays and matrices, along with a collection of mathematical functions to operate on these arrays efficiently. NumPy is often used in conjunction with Pandas for numerical computations and data manipulation.

3. Matplotlib and Seaborn: Matplotlib is a popular plotting library in Python that allows you to create a wide variety of static, interactive, and animated visualizations. Seaborn is built on top of Matplotlib and provides a higher-level interface for creating attractive and informative statistical graphics. These libraries are essential for data visualization in data analysis projects.

4. Scikit-learn: Scikit-learn is a machine learning library in Python that provides simple and efficient tools for data mining and data analysis tasks. It includes a wide range of algorithms for classification, regression, clustering, dimensionality reduction, and more. Scikit-learn also offers tools for model evaluation, hyperparameter tuning, and model selection.

5. Data Cleaning and Preprocessing: Data cleaning and preprocessing are crucial steps in any data analysis project. Python offers libraries like Pandas and NumPy for handling missing values, removing duplicates, standardizing data types, scaling numerical features, encoding categorical variables, and more. Understanding how to clean and preprocess data effectively is essential for accurate analysis and modeling.

By mastering these Python concepts and libraries, data analysts can efficiently manipulate and analyze data, create insightful visualizations, apply machine learning techniques, and derive valuable insights from their datasets.

Tableau Cheat Sheet ✅

This Tableau cheatsheet is designed to be your quick reference guide for data visualization and analysis using Tableau. Whether you’re a beginner learning the basics or an experienced user looking for a handy resource, this cheatsheet covers essential topics.

1. Connecting to Data
   - Use *Connect* pane to connect to various data sources (Excel, SQL Server, Text files, etc.).

2. Data Preparation
   - Data Interpreter: Clean data automatically using the Data Interpreter.
   - Join Data: Combine data from multiple tables using joins (Inner, Left, Right, Outer).
   - Union Data: Stack data from multiple tables with the same structure.

3. Creating Views
   - Drag & Drop: Drag fields from the Data pane onto Rows, Columns, or Marks to create visualizations.
   - Show Me: Use the *Show Me* panel to select different visualization types.

4. Types of Visualizations
   - Bar Chart: Compare values across categories.
   - Line Chart: Display trends over time.
   - Pie Chart: Show proportions of a whole (use sparingly).
   - Map: Visualize geographic data.
   - Scatter Plot: Show relationships between two variables.

5. Filters
   - Dimension Filters: Filter data based on categorical values.
   - Measure Filters: Filter data based on numerical values.
   - Context Filters: Set a context for other filters to improve performance.

6. Calculated Fields
   - Create calculated fields to derive new data:
     - Example: Sales Growth = SUM([Sales]) - SUM([Previous Sales])

7. Parameters
   - Use parameters to allow user input and control measures dynamically.

8. Formatting
   - Format fonts, colors, borders, and lines using the Format pane for better visual appeal.

9. Dashboards
   - Combine multiple sheets into a dashboard using the *Dashboard* tab.
   - Use dashboard actions (filter, highlight, URL) to create interactivity.

10. Story Points
    - Create a story to guide users through insights with narrative and visualizations.

11. Publishing & Sharing
    - Publish dashboards to Tableau Server or Tableau Online for sharing and collaboration.

12. Export Options
    - Export to PDF or image for offline use.

13. Keyboard Shortcuts
    - Show/Hide Sidebar: Ctrl+Alt+T
    - Duplicate Sheet: Ctrl + D
    - Undo: Ctrl + Z
    - Redo: Ctrl + Y

14. Performance Optimization
    - Use extracts instead of live connections for faster performance.
    - Optimize calculations and filters to improve dashboard loading times.

Essential questions related to Data Analytics 👇👇

Question 1: What is the first skill a fresher should learn for a Data Analytics job?
Answer: SQL. It’s the foundation for retrieving, manipulating, and analyzing data stored in databases.

Question 2: Which SQL database query should we learn - MySQL, PostgreSQL, PL-SQL, etc.?
Answer: Core SQL concepts are consistent across platforms. Focus on joins, aggregations, subqueries, and window functions.

Question 3: How much Python is required?
Answer: Learn basic syntax, loops, conditional statements, functions, and error handling. Then focus on Pandas and Numpy very well for data handling and analysis. Working Knowledge of Python + Good knowledge of Data Analysis Libraries is needed only.

Question 4: What other skills are required?
Answer: MS Excel for data cleaning and analysis, and a BI tool like Power BI or Tableau for creating dashboards.

Question 5: Is knowledge of Macros/VBA required?
Answer: No. Most Data Analyst roles don’t require it.

Question 6: When should I start applying for jobs?
Answer: Apply after acquiring 50% of the required skills and gaining practical experience through projects or internships.

Question 7: Are certifications required?
Answer: No. Projects and hands-on experience are more valuable.

Question 8: How important is data visualization in a Data Analyst role?
Answer: Very important. Use tools like Tableau or Power BI to present insights effectively.

Question 9: Is understanding statistics important for data analysis?
Answer: Yes. Learn descriptive statistics, hypothesis testing, and regression analysis for better insights.

Question 10: How much emphasis should be placed on machine learning?
Answer: A basic understanding is helpful but not essential for Data Analyst roles.

Question 11: What role does communication play in a Data Analyst's job?
Answer: It’s crucial. You need to present insights in a clear and actionable way for stakeholders.

Question 12: Is data cleaning a necessary skill?
Answer: Yes. Cleaning and preparing raw data is a major part of a Data Analyst’s job.

ENJOY LEARNING 👍👍

Amazon interview questions for Data Analyst in 2024 👇👇

1. How would you retrieve the second highest salary from a table called Employees without using LIMIT or TOP?
2. Write a query to display employees who joined in the same month but in different years from the Employees table.
3. Given two tables, Orders and Customers, write a query to find all customers who placed more than five orders in the last year.
4. How would you update the Department column in the Employees table based on a matching EmployeeID in another table called Departments?
5. Write a SQL query to find the total sales for each product category, but exclude categories with total sales less than a specific threshold (e.g., $10,000).
6. How would you create a Python function that reads a large CSV file in chunks and processes each chunk for data cleaning?
7. Write a Python script that takes a list of employee records, filters out records where the salary is below a certain value, and writes the filtered records to a new file.
8. How would you handle missing values in a dataset using pandas, and how would you decide which method to use (e.g., mean imputation vs. forward fill)?
9. Given a list of numbers, write a Python program to group the numbers into ranges (e.g., 1-10, 11-20) and count the number of elements in each range.
10. How would you connect to an SQL database using Python and fetch data into a pandas DataFrame for analysis?
11. How would you use VLOOKUP or INDEX-MATCH to find a value in one Excel sheet and return corresponding information from another sheet?
12. Write an Excel formula that calculates the weighted average of a set of numbers, where the weights are stored in another column.
13. How would you create a dynamic Excel dashboard that allows users to filter data by multiple criteria and display the results visually (e.g., via charts or pivot tables)?
14. Explain how you would use Excel Solver to optimize a product mix for maximizing profit under given constraints.
15. How can you create a measure that calculates the running total of sales over time, and how would you display it in a line chart?
16. How would you use Power Query to clean and transform a dataset, such as removing duplicates, splitting columns, and filtering rows based on conditions?
17. Describe a scenario where you would need to use Merge Queries in Power Query, and how would you do it?
18. How can you create a custom tooltip in Power BI to show additional information when a user hovers over a visual?

Goldman Sachs Data Analyst Interview Experience :


SQL:

1. Calculate the average salary for each department from the table.
2. Write a SQL query to display the employee’s name along with their manager’s name using a self-join on the ‘employees’ table, which contains ‘emp_id’, ‘name’, and ‘manager_id’ columns.
3. Find the most recent hire for each department (solved using LEAD/LAG functions).
4. Write a query to retrieve the nth highest salary from the Employees table, which has ‘EmployeeID’, ‘Name’, and ‘Salary’ columns.

Power BI:

1. What is meant by Filter context in DAX?
2. Explain the process of implementing Row-Level Security (RLS) in Power BI.
3. Describe the different types of filters available in Power BI.
4. What’s the difference between the ‘ALL’ and ‘ALLSELECTED’ functions in DAX?
5. How would you use DAX to calculate total sales for a specific product?

Python:

1. Create a dictionary, add elements, update a specific entry, and print the dictionary sorted by key in alphabetical order.
2. Identify unique values from a list of numbers and print how many times each value occurs.
3. Find and print the duplicate values in a list of numbers, along with their frequency.

Hope this helps you 😊

20 Must-Know Statistics Questions for Data Analyst and Business Analyst Role:



1️⃣ What is the difference between descriptive and inferential statistics?
2️⃣ Explain mean, median, and mode and when to use each.
3️⃣ What is standard deviation, and why is it important?
4️⃣ Define correlation vs. causation with examples.
5️⃣ What is a p-value, and how do you interpret it?
6️⃣ Explain the concept of confidence intervals.
7️⃣ What are outliers, and how can you handle them?
8️⃣ When would you use a t-test vs. a z-test?
9️⃣ What is the Central Limit Theorem (CLT), and why is it important?
🔟 Explain the difference between population and sample.
1️⃣1️⃣ What is regression analysis, and what are its key assumptions?
1️⃣2️⃣ How do you calculate probability, and why does it matter in analytics?
1️⃣3️⃣ Explain the concept of Bayes’ Theorem with a practical example.
1️⃣4️⃣ What is an ANOVA test, and when should it be used?
1️⃣5️⃣ Define skewness and kurtosis in a dataset.
1️⃣6️⃣ What is the difference between parametric and non-parametric tests?
1️⃣7️⃣ What are Type I and Type II errors in hypothesis testing?
1️⃣8️⃣ How do you handle missing data in a dataset?
1️⃣9️⃣ What is A/B testing, and how do you analyze the results?
2️⃣0️⃣ What is a Chi-square test, and when is it used?

Top 5 data analysis interview questions with answers 😄👇

Question 1: How would you approach a new data analysis project?

Ideal answer:
I would approach a new data analysis project by following these steps:
Understand the business goals. What is the purpose of the data analysis? What questions are we trying to answer?
Gather the data. This may involve collecting data from different sources, such as databases, spreadsheets, and surveys.
Clean and prepare the data. This may involve removing duplicate data, correcting errors, and formatting the data in a consistent way.
Explore the data. This involves using data visualization and statistical analysis to understand the data and identify any patterns or trends.
Build a model or hypothesis. This involves using the data to develop a model or hypothesis that can be used to answer the business questions.
Test the model or hypothesis. This involves using the data to test the model or hypothesis and see how well it performs.
Interpret and communicate the results. This involves explaining the results of the data analysis to stakeholders in a clear and concise way.

Question 2: What are some of the challenges you have faced in previous data analysis projects, and how did you overcome them?

Ideal answer:
One of the biggest challenges I have faced in previous data analysis projects is dealing with missing data. I have overcome this challenge by using a variety of techniques, such as imputation and machine learning.
Another challenge I have faced is dealing with large datasets. I have overcome this challenge by using efficient data processing techniques and by using cloud computing platforms.

Question 3: Can you describe a time when you used data analysis to solve a business problem?

Ideal answer:
In my previous role at a retail company, I was tasked with identifying the products that were most likely to be purchased together. I used data analysis to identify patterns in the purchase data and to develop a model that could predict which products were most likely to be purchased together. This model was used to improve the company's product recommendations and to increase sales.

Question 4: What are some of your favorite data analysis tools and techniques?

Ideal answer:
Some of my favorite data analysis tools and techniques include:
Programming languages such as Python and R
Data visualization tools such as Tableau and Power BI
Statistical analysis tools such as SPSS and SAS
Machine learning algorithms such as linear regression and decision trees

Question 5: How do you stay up-to-date on the latest trends and developments in data analysis?

Ideal answer:
I stay up-to-date on the latest trends and developments in data analysis by reading industry publications, attending conferences, and taking online courses. I also follow thought leaders on social media and subscribe to newsletters.

By providing thoughtful and well-informed answers to these questions, you can demonstrate to your interviewer that you have the analytical skills and knowledge necessary to be successful in the role.

Like this post if you want more interview questions with detailed answers to be posted in the channel 👍❤️

Share with credits: https://t.iss.one/sqlspecialist

Hope it helps :)

Junior-level Data Analyst interview questions:

Introduction and Background

1. Can you tell me about your background and how you became interested in data analysis?
2. What do you know about our company/organization?
3. Why do you want to work as a data analyst?

Data Analysis and Interpretation

1. What is your experience with data analysis tools like Excel, SQL, or Tableau?
2. How would you approach analyzing a large dataset to identify trends and patterns?
3. Can you explain the concept of correlation versus causation?
4. How do you handle missing or incomplete data?
5. Can you walk me through a time when you had to interpret complex data results?

Technical Skills

1. Write a SQL query to extract data from a database.
2. How do you create a pivot table in Excel?
3. Can you explain the difference between a histogram and a box plot?
4. How do you perform data visualization using Tableau or Power BI?
5. Can you write a simple Python or R script to manipulate data?

Statistics and Math

1. What is the difference between mean, median, and mode?
2. Can you explain the concept of standard deviation and variance?
3. How do you calculate probability and confidence intervals?
4. Can you describe a time when you applied statistical concepts to a real-world problem?
5. How do you approach hypothesis testing?

Communication and Storytelling

1. Can you explain a complex data concept to a non-technical person?
2. How do you present data insights to stakeholders?
3. Can you walk me through a time when you had to communicate data results to a team?
4. How do you create effective data visualizations?
5. Can you tell a story using data?

Case Studies and Scenarios

1. You are given a dataset with customer purchase history. How would you analyze it to identify trends?
2. A company wants to increase sales. How would you use data to inform marketing strategies?
3. You notice a discrepancy in sales data. How would you investigate and resolve the issue?
4. Can you describe a time when you had to work with a stakeholder to understand their data needs?
5. How would you prioritize data projects with limited resources?

Behavioral Questions

1. Can you describe a time when you overcame a difficult data analysis challenge?
2. How do you handle tight deadlines and multiple projects?
3. Can you tell me about a project you worked on and your role in it?
4. How do you stay up-to-date with new data tools and technologies?
5. Can you describe a time when you received feedback on your data analysis work?

Final Questions

1. Do you have any questions about the company or role?
2. What do you think sets you apart from other candidates?
3. Can you summarize your experience and qualifications?
4. What are your long-term career goals?

Hope this helps you 😊

EXL Data Analyst Interview Experience:


SQL Questions

1. You have a table Transactions with columns TransactionID, CustomerID, Date, and Amount. Write a query to calculate the cumulative revenue per customer for each month in the last year.
2. A table Production contains columns PlantID, Date, and Output. Write a query to identify the plants that consistently exceeded their daily average output for at least 20 days in a given month.
3. In a table EmployeeAttendance with columns EmployeeID, Date, and Status (values: ‘Present’, ‘Absent’), write a query to find employees with the highest consecutive absences in the last quarter.
4. What are the pros and cons of using indexes in SQL, and when would you avoid using them?
5. Explain the differences between window functions and aggregate functions with examples.

Python Questions

6. Write a Python script to merge multiple CSV files from a directory into a single file and perform basic data cleaning.
7. Given a list of dictionaries, write a Python program to group the data by a specific key and calculate summary statistics for the grouped data.
8. Explain the difference between a list, a tuple, and a dictionary in Python, and provide examples of their usage.
9. Write a Python function to automate the generation of monthly reports from a dataset stored in an Excel file.

Power BI Questions

10. How would you create a dashboard in Power BI to track the operational efficiency of production plants?
11. Explain how you would handle a situation where the data source refresh in Power BI is causing delays.
12. What is the difference between row-level security and role-level security in Power BI?
13. How would you use Power BI to visualize trends and outliers in daily sales data?
14. Discuss how you would create a calculated measure to show YoY (Year-over-Year) growth in Power BI.

General Questions

15. Share an example where your data-driven insights helped solve a business problem or improve a process.
16. How do you prioritize tasks and manage deadlines in a high-pressure environment?

Myntra interview questions for Data Analyst

1. You have a dataset with missing values. How would you use a combination of Pandas and NumPy to fill missing values based on the mean of the column?
2. How would you create a new column in a Pandas DataFrame by normalizing an existing numeric column using NumPy’s np.min() and np.max()?
3. Explain how to group a Pandas DataFrame by one column and apply a NumPy function, like np.std() (standard deviation), to each group.
4. How can you convert a time-series column in a Pandas DataFrame to NumPy’s datetime format for faster time-based calculations?
5. How would you identify and remove outliers from a Pandas DataFrame using NumPy’s Z-score method (scipy.stats.zscore)?
6. How would you use NumPy’s percentile() function to calculate specific quantiles for a numeric column in a Pandas DataFrame?
7. How would you use NumPy's polyfit() function to perform linear regression on a dataset stored in a Pandas DataFrame?
8. How can you use a combination of Pandas and NumPy to transform categorical data into dummy variables (one-hot encoding)?
9. How would you use both Pandas and NumPy to split a dataset into training and testing sets based on a random seed?
10. How can you apply NumPy's vectorize() function on a Pandas Series for better performance?
11. How would you optimize a Pandas DataFrame containing millions of rows by converting columns to NumPy arrays? Explain the benefits in terms of memory and speed.
12. How can you perform complex mathematical operations, such as matrix multiplication, using NumPy on a subset of a Pandas DataFrame?
13. Explain how you can use np.select() to perform conditional column operations in a Pandas DataFrame.
14. How can you handle time series data in Pandas and use NumPy to perform statistical analysis like rolling variance or covariance?
15. How can you integrate NumPy's random module (np.random) to generate random numbers and add them as a new column in a Pandas DataFrame?
16. Explain how you would use Pandas' applymap() function combined with NumPy’s vectorized operations to transform all elements in a DataFrame.
17. How can you apply mathematical transformations (e.g., square root, logarithm) from NumPy to specific columns in a Pandas DataFrame?
18. How would you efficiently perform element-wise operations between a Pandas DataFrame and a NumPy array of different dimensions?
19. How can you use NumPy functions like np.linalg.inv() or np.linalg.det() for linear algebra operations on numeric columns of a Pandas DataFrame?
20. Explain how you would compute the covariance matrix between multiple numeric columns of a DataFrame using NumPy.
21. What are the key differences between a Pandas DataFrame and a NumPy array? When would you use one over the other?
22. How can you convert a NumPy array into a Pandas DataFrame, and vice versa? Provide an example.

You can find the answers here

Hope this helps you 😊

Interview guide for Data Analyst Role

When interviewing for a Data Analyst role as a fresher, you’ll likely encounter questions that focus on your understanding of data analysis concepts, technical skills, and problem-solving abilities. Here’s a comprehensive list of commonly asked interview questions:

1. General and Behavioral Questions

• Tell me about yourself.
• Why do you want to become a Data Analyst?
• What do you know about our company and why do you want to work here?
• Describe a time when you solved a problem using data.
• How do you prioritize tasks and manage deadlines?
• Tell me about a time when you worked in a team to complete a project.

2. Technical Questions

• What are the different types of joins in SQL? (Expect variations of SQL questions)
• How would you handle missing or inconsistent data?
• What is normalization? Why is it important?
• Explain the difference between primary keys and foreign keys in a database.
• What are the most common data types in SQL?
• How do you perform data cleaning in Excel?

3. Analytical Skills and Problem-Solving

• How would you find outliers in a dataset?
• How would you approach analyzing a dataset with 1 million rows?
• If given two datasets, how would you combine them?
• What steps would you take if your results didn’t match stakeholders’ expectations?
• How would you identify trends or patterns in a dataset?

4. Excel-Related Questions

• What are pivot tables and how do you use them?
• Explain VLOOKUP and HLOOKUP.
• How would you handle large datasets in Excel?
• What is the use of conditional formatting?
• How would you create a dashboard in Excel?
• How can you create a custom formula in Excel?

5. SQL Questions

• Write a SQL query to find the second highest salary in a table.
• What is the difference between WHERE and HAVING clauses?
• How would you optimize a slow-running query?
• What is the difference between UNION and UNION ALL?
• What is a subquery, and when would you use it?

6. Statistics and Data Analysis

• Explain the difference between mean, median, and mode.
• What is standard deviation, and why is it important?
• What is regression analysis? Can you explain linear regression?
• What is correlation, and how is it different from causation?
• What are some key metrics you would track for a marketing campaign?

7. Data Visualization and Tools

• What tools have you used for data visualization?
• Explain a situation where you used charts to tell a story.
• What is your experience with tools like Tableau or Power BI?
• How would you decide which chart type to use for visualizing data?
• Have you ever created a dashboard? If yes, what were the key features?

8. Python/R (If mentioned on your resume)

• What libraries do you use in Python for data analysis?
• How would you import a dataset and perform basic analysis in Python?
• What are some common data manipulation functions in pandas?
• How do you handle missing values in Python?

9. Scenario-Based Questions

• Imagine you are given a dataset of customer purchases; how would you segment the customers?
• You are given sales data for the past five years. What steps would you take to forecast the next year’s sales?
• If you find conflicting data in a report, how would you handle the situation?
• Describe a project where you identified key insights using data.

10. Aptitude or Logical Questions

• Some companies also include questions testing your quantitative aptitude, logical reasoning, and pattern recognition to gauge problem-solving skills.

Tips to Prepare:

1. Strengthen your Basics: Brush up on SQL, Excel, and statistical concepts.
2. Mock Interviews: Practice explaining your thought process for data problems.
3. Projects: Be ready to discuss any projects or internships you’ve done.
4. Stay Current: Read about trends in data analysis and business intelligence.

Hope this helps you 😊

Meesho Data Analyst interview experience (0-3) -


Power BI Questions:

1. Explain the concept of context transition in DAX and provide an example.
2. How would you optimize a complex Power BI report for faster performance?
3. Describe the process of creating and using calculation groups in Power BI.
4. Explain how you would handle large datasets in Power BI without compromising performance.
5. What is a composite model in Power BI, and how can it be used effectively?
6. How does the USERELATIONSHIP function work, and when would you use it?
7. Describe how to use Power Query M language for advanced data transformations.
8. Explain the difference between CROSSFILTER and TREATAS in DAX.

SQL Questions:

1. How would you optimize a slow-running query with multiple joins?
2. What is a recursive CTE, and can you provide an example of when to use it?
3. Explain the difference between clustered and non-clustered indexes and when to use each.
4. Write a query to find the second highest salary in each department.
5. How would you detect and resolve deadlocks in SQL?
6. Explain window functions and provide examples of ROW_NUMBER, RANK, and DENSE_RANK.
7. Describe the ACID properties in database transactions and their significance.
8. Write a query to calculate a running total with partitions based on specific conditions.

You can read detailed article with answers here

Hope this helps you 😊

Interview list for Data Analytics Roles

SQL Essentials:
- SELECT statements including WHERE, ORDER BY, GROUP BY, HAVING
- Basic JOINS: INNER, LEFT, RIGHT, FULL
- Aggregate functions: COUNT, SUM, AVG, MAX, MIN
- Subqueries, Common Table Expressions (WITH clause)
- CASE statements, advanced JOIN techniques, and Window functions (OVER, PARTITION BY, ROW_NUMBER, RANK)

Excel Proficiency:
- Cell operations, formulas (SUMIFS, COUNTIFS, AVERAGEIFS, LOOKUPS)
- PivotTables, PivotCharts, Data validation, What-if analysis
- Advanced formulas, Data Model & Power Pivot

Power BI Skills:
- Data modeling (importing data, managing relationships)
- Data transformation with Power Query, DAX for calculated columns/measures
- Creating interactive reports and dashboards, visualizations

Data Warehousing:
-Concepts of OLAP vs. OLTP
-Star and Snowflake schema designs
-ETL processes: Extract, Transform, Load
-Data lake vs. data warehouse

Cloud Computing for Data Analytics:
-Benefits of cloud services (AWS, Azure, Google Cloud)
-Data storage solutions: S3, Azure Blob Storage, Google Cloud Storage
-Cloud-based data analytics tools: BigQuery, Redshift, Snowflake
-Cost management and optimization strategies

Python Programming:
- Basic syntax, control structures, data structures (lists, dictionaries)
- Pandas & NumPy for data manipulation: DataFrames, Series, groupby
-plotting with Matplotlib, Seaborn for visualization

Statistics Fundamentals:
- Mean, Median, Mode, Standard Deviation, Variance
- Probability distributions, Hypothesis Testing, P-values
- Confidence Intervals, Correlation, Simple Linear Regression

I have curated top-notch Data Analytics Resources 👇👇
https://whatsapp.com/channel/0029VaGgzAk72WTmQFERKh02

Hope this helps you 😊

𝗪𝗮𝗻𝘁 𝘁𝗼 𝗸𝗻𝗼𝘄 𝘄𝗵𝗮𝘁 𝗵𝗮𝗽𝗽𝗲𝗻𝘀 𝗶𝗻 𝗮 𝗿𝗲𝗮𝗹 𝗱𝗮𝘁𝗮 𝗮𝗻𝗮𝗹𝘆𝘀𝘁 𝗶𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄?

𝗕𝗮𝘀𝗶𝗰 𝗜𝗻𝘁𝗿𝗼𝗱𝘂𝗰𝘁𝗶𝗼𝗻

-Brief introduction about yourself.

-Explanation of how you developed an interest in learning Power BI despite having a chemical background.


𝗧𝗼𝗼𝗹𝘀 𝗣𝗿𝗼𝗳𝗶𝗰𝗶𝗲𝗻𝗰𝘆

-Discussion about the tools you are proficient in.

-Detailed explanation of a project that demonstrated your proficiency in these tools.

𝗣𝗿𝗼𝗷𝗲𝗰𝘁 𝗘𝘅𝗽𝗹𝗮𝗻𝗮𝘁𝗶𝗼𝗻

Explain about any Data Analytics Project you did, below are some follow-up questions for sales related data analysis project

Follow-up Question:

Was there any improvement in sales after building the report?

Provide a clear before and after scenario in sales post-report creation.

What areas did you identify where the company was losing sales, and what were your recommendations?

- How do you check the quality of data when it's given to you?
Explain your methods for ensuring data quality.

- How do you handle null values? Describe your approach to managing null values in datasets.


𝗦𝗤𝗟 𝗾𝘂𝗲𝘀𝘁𝗶𝗼𝗻𝘀

-Explain the order in which SQL clauses are executed.

-Write a query to find the percentage of the 18-year-old population.
Details: You are given two tables:
Table 1: Contains states and their respective populations.
Table 2: Contains three columns (state, gender, and population of 18-year-olds).

-Explain window functions and how to rank values in SQL.

- Difference between JOIN and UNION.

-How to return unique values in SQL.

𝗕𝗲𝗵𝗮𝘃𝗶𝗼𝗿𝗮𝗹 𝗤𝘂𝗲𝘀𝘁𝗶𝗼𝗻𝘀

-Solve a puzzle involving 3 gallons of water in one jar and 2 gallons in another to get exactly 4 gallons.
Step-by-step solution for the water puzzle.

- What skills have you learned on your own? Discuss the skills you self-taught and their impact on your career.

-Describe cases when you showcased team spirit.

-⭐ 𝗦𝗼𝗰𝗶𝗮𝗹 𝗠𝗲𝗱𝗶𝗮 𝗔𝗽𝗽 𝗤𝘂𝗲𝘀𝘁𝗶𝗼𝗻
Scenario: Choose any social media app (I choose Discord).
Question: What function/feature would you add to the Discord app, and how would you track its success?

- Rate yourself on Excel, SQL, and Python out of 10.

- What are your strengths in data analytics?

Like if it helps :)

SQL Interview Questions (0-5 Year Experience)!!

Are you preparing for a SQL interview?

Here are some essential SQL concepts to review:

𝐁𝐚𝐬𝐢𝐜 𝐒𝐐𝐋 𝐂𝐨𝐧𝐜𝐞𝐩𝐭𝐬:

1. What is SQL, and why is it important in data analytics?
2. Explain the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.
3. What is the difference between WHERE and HAVING clauses?
4. How do you use GROUP BY and HAVING in a query?
5. Write a query to find duplicate records in a table.
6. How do you retrieve unique values from a table using SQL?
7. Explain the use of aggregate functions like COUNT(), SUM(), AVG(), MIN(), and MAX().
8. What is the purpose of a DISTINCT keyword in SQL?

𝐈𝐧𝐭𝐞𝐫𝐦𝐞𝐝𝐢𝐚𝐭𝐞 𝐒𝐐𝐋:

1. Write a query to find the second-highest salary from an employee table.
2. What are subqueries and how do you use them?
3. What is a Common Table Expression (CTE)? Give an example of when to use it.
4. Explain window functions like ROW_NUMBER(), RANK(), and DENSE_RANK().
5. How do you combine results of two queries using UNION and UNION ALL?
6. What are indexes in SQL, and how do they improve query performance?
7. Write a query to calculate the total sales for each month using GROUP BY.

𝐀𝐝𝐯𝐚𝐧𝐜𝐞𝐝 𝐒𝐐𝐋:

1. How do you optimize a slow-running SQL query?
2. What are views in SQL, and when would you use them?
3. What is the difference between a stored procedure and a function in SQL?
4. Explain the difference between TRUNCATE, DELETE, and DROP commands.
5. What are windowing functions, and how are they used in analytics?
6. How do you use PARTITION BY and ORDER BY in window functions?
7. How do you handle NULL values in SQL, and what functions help with that (e.g., COALESCE, ISNULL)?

Here you can find essential SQL Interview Resources👇
https://t.iss.one/mysqldata

Like this post if you need more 👍❤️

Hope it helps :)

✨The STAR method is a powerful technique used to answer behavioral interview questions effectively.

It helps structure responses by focusing on Situation, Task, Action, and Result. For analytics professionals, using the STAR method ensures that you demonstrate your problem-solving abilities, technical skills, and business acumen in a clear and concise way.

Here’s how the STAR method works, tailored for an analytics interview:

📍 1. Situation
Describe the context or challenge you faced. For analysts, this might be related to data challenges, business processes, or system inefficiencies. Be specific about the setting, whether it was a project, a recurring task, or a special initiative.

Example: “At my previous role as a data analyst at XYZ Company, we were experiencing a high churn rate among our subscription customers. This was a critical issue because it directly impacted revenue.”*

📍 2. Task
Explain the responsibilities you had or the goals you needed to achieve in that situation. In analytics, this usually revolves around diagnosing the problem, designing experiments, or conducting data analysis.

Example: “I was tasked with identifying the factors contributing to customer churn and providing actionable insights to the marketing team to help them improve retention.”*

📍 3. Action
Detail the specific actions you took to address the problem. Be sure to mention any tools, software, or methodologies you used (e.g., SQL, Python, data #visualization tools, #statistical #models). This is your opportunity to showcase your technical expertise and approach to problem-solving.

Example: “I collected and analyzed customer data using #SQL to extract key trends. I then used #Python for data cleaning and statistical analysis, focusing on engagement metrics, product usage patterns, and customer feedback. I also collaborated with the marketing and product teams to understand business priorities.”*

📍 4. Result
Highlight the outcome of your actions, especially any measurable impact. Quantify your results if possible, as this demonstrates your effectiveness as an analyst. Show how your analysis directly influenced business decisions or outcomes.

Example: “As a result of my analysis, we discovered that customers were disengaging due to a lack of certain product features. My insights led to a targeted marketing campaign and product improvements, reducing churn by 15% over the next quarter.”*

Example STAR Answer for an Analytics Interview Question:
Question: *"Tell me about a time you used data to solve a business problem."*

Answer (STAR format): 
🔻*S*: “At my previous company, our sales team was struggling with inconsistent performance, and management wasn’t sure which factors were driving the variance.” 
🔻*T*: “I was assigned the task of conducting a detailed analysis to identify key drivers of sales performance and propose data-driven recommendations.” 
🔻*A*: “I began by collecting sales data over the past year and segmented it by region, product line, and sales representative. I then used Python for #statistical #analysis and developed a regression model to determine the key factors influencing sales outcomes. I also visualized the data using #Tableau to present the findings to non-technical stakeholders.” 
🔻*R*: “The analysis revealed that product mix and regional seasonality were significant contributors to the variability. Based on my findings, the company adjusted their sales strategy, leading to a 20% increase in sales efficiency in the next quarter.”

Hope this helps you 😊

Infosys is hiring 20,000 freshers in various fields and here is a complete guide to crack  this interview

1. Understand the Interview Structure

Infosys fresher recruitment usually has three main stages:

• Aptitude Test (Written Exam)
• Technical Interview
• HR Interview

2. Aptitude Test Preparation

The first stage typically includes questions on logical reasoning, quantitative aptitude, and verbal ability. Prepare the following:

• Quantitative Aptitude: Topics include time & work, percentages, profit & loss, probability, permutations & combinations, and number series.
• Logical Reasoning: Focus on puzzles, blood relations, data interpretation, and syllogisms.
• Verbal Ability: This includes reading comprehension, sentence correction, error spotting, synonyms/antonyms, and fill-in-the-blanks.

Resources:

• Books: RS Aggarwal’s Quantitative Aptitude for quantitative topics.
• Websites: Platforms like IndiaBix or Testbook provide practice questions.

Tips:

• Practice regularly under timed conditions.
• Use mock tests to improve speed and accuracy.
• Focus on weak areas after taking a few practice tests.

3. Technical Interview Preparation

In this round, Infosys assesses your understanding of basic programming, algorithms, data structures, and other core subjects. Here’s how to prepare:

• Programming Languages: Have a solid foundation in at least one programming language (C, C++, Java, Python).
• Data Structures & Algorithms: Study key topics like arrays, linked lists, stacks, queues, trees, and sorting algorithms.
• DBMS, Operating Systems & Networks: Be prepared for basic questions on SQL, normalization, joins, process management, and networking protocols.

Sample Questions:
• How would you reverse a string in your preferred language?
• Explain the difference between a stack and a queue.
• What is a deadlock, and how can it be avoided?

Resources:
• GeeksforGeeks and LeetCode for coding practice and theory.
• Books like Cracking the Coding Interview by Gayle Laakmann McDowell.

Tips:
• Focus on problem-solving skills and code optimization.
• Be ready to explain your approach in technical questions.

4. Coding Round (If applicable)
Some Infosys roles might require you to go through a coding round. Practice coding problems related to arrays, strings, recursion, dynamic programming, and greedy algorithms.

Tools:
• HackerRank, CodeChef, and Codeforces are good platforms to practice coding challenges.
• Focus on coding efficiency and edge case handling.

5. HR Interview Preparation
In the HR round, you will be evaluated on your personality, communication skills, and cultural fit. Common questions include:
• Tell me about yourself.
• Why do you want to join Infosys?
• What are your strengths and weaknesses?

Tips:
• Prepare a structured self-introduction.
• Research Infosys’ values, projects, and recent developments to show enthusiasm for the company.
• Be honest but strategic with your answers regarding strengths and weaknesses.

6. Mock Interviews and Soft Skills
• Mock Interviews: Participate in mock interviews to simulate the real environment.
• Soft Skills: Work on clear communication and positive body language. Infosys looks for candidates who can explain technical concepts clearly.

7. Common Mistakes to Avoid
• Lack of Practice: Not practicing enough aptitude or coding questions can lead to poor performance in tests.
• Unclear Communication: Even if you know the solution, being unable to explain it well in technical interviews can hurt your chances.
• Overlooking HR Round: Many candidates prepare for technical rounds and ignore HR. Remember, HR rounds can be just as important.

8. Key Resources
• Aptitude: RS Aggarwal for Quantitative Aptitude.
• Coding: HackerRank, LeetCode.
• Technical Knowledge: GeeksforGeeks for theory and coding questions.
• Mock Tests: Websites like IndiaBix provide Infosys-specific mock tests and previous year papers.

Hope this helps you 😊

Important Interview Questions

1. What is a window function in SQL? How is it different from aggregate functions?
2. Explain the use of the OVER() clause in window functions.
3. What is the purpose of the PARTITION BY clause in window functions?
4. What is the role of the ORDER BY clause in a window function?
5. What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK() window functions?
6. How do window functions differ from group functions like GROUP BY?
7. Can you use window functions with an ORDER BY clause outside of the OVER() clause? Why or why not?
8. Write a query using the ROW_NUMBER() function to assign sequential numbers to rows in a result set.
9. How does the NTILE() function work in SQL? What is its use case?
10. What is the difference between LAG() and LEAD() window functions?

Hope this helps you 😊

Myntra interview questions for Data Analyst 2024.

1. You have a dataset with missing values. How would you use a combination of Pandas and NumPy to fill missing values based on the mean of the column?
2. How would you create a new column in a Pandas DataFrame by normalizing an existing numeric column using NumPy’s np.min() and np.max()?
3. Explain how to group a Pandas DataFrame by one column and apply a NumPy function, like np.std() (standard deviation), to each group.
4. How can you convert a time-series column in a Pandas DataFrame to NumPy’s datetime format for faster time-based calculations?
5. How would you identify and remove outliers from a Pandas DataFrame using NumPy’s Z-score method (scipy.stats.zscore)?
6. How would you use NumPy’s percentile() function to calculate specific quantiles for a numeric column in a Pandas DataFrame?
7. How would you use NumPy's polyfit() function to perform linear regression on a dataset stored in a Pandas DataFrame?
8. How can you use a combination of Pandas and NumPy to transform categorical data into dummy variables (one-hot encoding)?
9. How would you use both Pandas and NumPy to split a dataset into training and testing sets based on a random seed?
10. How can you apply NumPy's vectorize() function on a Pandas Series for better performance?
11. How would you optimize a Pandas DataFrame containing millions of rows by converting columns to NumPy arrays? Explain the benefits in terms of memory and speed.
12. How can you perform complex mathematical operations, such as matrix multiplication, using NumPy on a subset of a Pandas DataFrame?
13. Explain how you can use np.select() to perform conditional column operations in a Pandas DataFrame.
14. How can you handle time series data in Pandas and use NumPy to perform statistical analysis like rolling variance or covariance?
15. How can you integrate NumPy's random module (np.random) to generate random numbers and add them as a new column in a Pandas DataFrame?
16. Explain how you would use Pandas' applymap() function combined with NumPy’s vectorized operations to transform all elements in a DataFrame.
17. How can you apply mathematical transformations (e.g., square root, logarithm) from NumPy to specific columns in a Pandas DataFrame?
18. How would you efficiently perform element-wise operations between a Pandas DataFrame and a NumPy array of different dimensions?
19. How can you use NumPy functions like np.linalg.inv() or np.linalg.det() for linear algebra operations on numeric columns of a Pandas DataFrame?
20. Explain how you would compute the covariance matrix between multiple numeric columns of a DataFrame using NumPy.
21. What are the key differences between a Pandas DataFrame and a NumPy array? When would you use one over the other?
22. How can you convert a NumPy array into a Pandas DataFrame, and vice versa? Provide an example.

You can find the answers here

Hope this helps you 😊