Data Analyst Interview Resources

Amazon Data Analyst Interview Questions for 1-3 years of experience role :- A. SQL: 1. You have two tables: Employee and Department. - Employee Table Columns: Employee_id, Employee_Name, Department_id, Salary - Department Table Columns: Department_id, Department_Name, Location Write an SQL query to find the name of the employee with the highest salary in each location. 2. You have two tables: Orders and Customers. - Orders Table Columns: Order_id, Customer_id, Order_Date, Amount - Customers Table Columns: Customer_id, Customer_Name, Join_Date Write an SQL query to calculate the total order amount for each customer who joined in the current year. The output should contain Customer_Name and the total amount. B. Python: 1. Basic oral questions on NumPy (e.g., array creation, slicing, broadcasting) and Matplotlib (e.g., plot types, customization). 2. Basic oral questions on pandas (like: groupby, loc/iloc, merge & join, etc.) 2. Write the code in NumPy and Pandas to replicate the functionality of your answer to the second SQL question. C. Leadership or Situational Questions: (Based on the leadership principle of Bias for Action) - Describe a situation where you had to make a quick decision with limited information. How did you proceed, and what was the outcome? (Based on the leadership principle of Dive Deep) - Can you share an example of a project where you had to delve deeply into the data to uncover insights or solve a problem? What steps did you take, and what were the results? (Based on the leadership principle of Customer Obsession) - Tell us about a time when you went above and beyond to meet a customer's needs or expectations. How did you identify their requirements, and what actions did you take to deliver exceptional service? D. Excel: Questions on advanced functions like VLOOKUP, XLookup, SUMPRODUCT, INDIRECT, TEXT functions, SUMIFS, COUNTIFS, LOOKUPS, INDEX & MATCH, AVERAGEIFS. Plus, some basic questions on pivot tables, conditional formatting, data validation, and charts. I have curated best 80+ top-notch Data Analytics Resources 👇👇 https://topmate.io/analyst/861634 Like if it helps :)

Some Imp Scenario Q & A for product based Company : You are a data analyst at a logistics company. The company wants to analyze delivery performance and customer satisfaction. Your tasks are: 1. Identify late deliveries and their impact on customer satisfaction. 2. Calculate the average delivery time for each region. 3. Create a Power BI report to visualize delivery performance and identify areas for improvement Answer: SQL Queries to Retrieve Data 1. Identify Late Deliveries and Their Impact on Customer Satisfaction: SELECT d.DeliveryID, d.CustomerID, d.DeliveryDate, d.ExpectedDeliveryDate, d.DeliveryTime, c.SatisfactionScore FROM Deliveries d JOIN Customers c ON d.CustomerID = c.CustomerID WHERE d.DeliveryDate > d.ExpectedDeliveryDate; 2. Calculate the Average Delivery Time for Each Region: SELECT Region, AVG(DATEDIFF(day, OrderDate, DeliveryDate)) AS AvgDeliveryTime FROM Deliveries GROUP BY Region; 3. Customer Satisfaction by Delivery Performance: SELECT DeliveryPerformance, AVG(SatisfactionScore) AS AvgSatisfactionScore FROM ( SELECT d.CustomerID, c.SatisfactionScore, CASE WHEN d.DeliveryDate <= d.ExpectedDeliveryDate THEN 'On Time' ELSE 'Late' END AS DeliveryPerformance FROM Deliveries d JOIN Customers c ON d.CustomerID = c.CustomerID ) AS DeliveryData GROUP BY DeliveryPerformance; Import Data into Power BI 1. Load Data: Open Power BI Desktop. Use the "Get Data" feature to connect to your SQL database. Import the result sets from the SQL queries into Power BI. 2. Create Relationships (if necessary): Ensure that the data tables are properly related, such as linking the Deliveries table to the Customers table. Create Visualizations 1. Late Deliveries and Their Impact on Customer Satisfaction: Create a table visual. Drag DeliveryID, CustomerID, DeliveryDate, ExpectedDeliveryDate, DeliveryTime, and SatisfactionScore to the Values. 2. Average Delivery Time for Each Region: Create a bar chart. Drag Region to the Axis. Drag AvgDeliveryTime to the Values. 3. Customer Satisfaction by Delivery Performance: Create a bar chart. Drag DeliveryPerformance to the Axis. Drag AvgSatisfactionScore to the Values. 4. Overall Delivery Analysis: Create a pie chart. Drag Region to the Legend. Drag AvgDeliveryTime to the Values. Optimize Performance 1. Data Model Optimization: Filter data to include only necessary columns and rows. Use summarized tables to pre-aggregate data. 2. DAX Optimization: Create measures for dynamic calculations. Simplify DAX formulas to improve performance. 3. Visualization Optimization: Limit the number of visuals per page. Avoid excessive use of slicers or custom visuals that can impact performance. I have curated best 80+ top-notch Data Analytics Resources 👇👇 https://topmate.io/analyst/861634 Like if it helps :)

TOP CONCEPTS FOR INTERVIEW PREPARATION!! 🚀TOP 10 SQL Concepts for Job Interview 1. Aggregate Functions (SUM/AVG) 2. Group By and Order By 3. JOINs (Inner/Left/Right) 4. Union and Union All 5. Date and Time processing 6. String processing 7. Window Functions (Partition by) 8. Subquery 9. View and Index 10. Common Table Expression (CTE) 🚀TOP 10 Statistics Concepts for Job Interview 1. Sampling 2. Experiments (A/B tests) 3. Descriptive Statistics 4. p-value 5. Probability Distributions 6. t-test 7. ANOVA 8. Correlation 9. Linear Regression 10. Logistics Regression 🚀TOP 10 Python Concepts for Job Interview 1. Reading data from file/table 2. Writing data to file/table 3. Data Types 4. Function 5. Data Preprocessing (numpy/pandas) 6. Data Visualisation (Matplotlib/seaborn/bokeh) 7. Machine Learning (sklearn) 8. Deep Learning (Tensorflow/Keras/PyTorch) 9. Distributed Processing (PySpark) 10. Functional and Object Oriented Programming Like ❤️ the post if it was helpful to you!!!

1. What are the various types of refresh options provided in Power BI? Package refresh - This synchronizes your Power BI Desktop or Excel file between the Power BI service and OneDrive, or SharePoint Online. Model or data refresh - This refreshes the dataset within the Power BI service with data from the original data source. Tile refresh - This updates the cache for tile visuals every 15 minutes on the dashboard once data changes. Visual container refresh - This refreshes the visible container and updates the cached report visuals within a report once the data changes. 2. Explain some date manipulation functions in SQL. Getdate: As its name suggests, the getdate function gives us today’s date. Dateadd: The dateadd function is used for adding a time or date interval to a date.Datediff: The datediff function is used for calculating the difference between two dates based on a given interval. Datename: The datename function can be used for extracting the parts of a date. Year, month, day: The year, month, and day functions allow for decomposing a date. 3. What is CTE in SQL? A CTE (Common Table Expression) is a one-time result set that only exists for the duration of the query. It allows us to refer to data within a single SELECT, INSERT, UPDATE, DELETE, CREATE VIEW, or MERGE statement's execution scope. It is temporary because its result cannot be stored anywhere and will be lost as soon as a query's execution is completed.

I'm sure you had an idea, but something got in the way and you didn't develop it. The channel "Usual thing" is about this, the author tries to implement different business ideas, but every day he encounters problems and discusses them with you. https://t.me/usual_thing

Statistical interview questions for entry-level data analyst roles in an MNC. 1. Explain the difference between mean, median, and mode. When would you use each? 2. How do you calculate the variance and standard deviation of a dataset? 3. What is skewness and kurtosis? How do they help in understanding data distribution? 4. What is the central limit theorem, and why is it important in statistics? 5. Describe different types of probability distributions (e.g., normal, binomial, Poisson). 6. Explain the difference between a population and a sample. Why is sampling important? 7. What are null and alternative hypotheses? How do you formulate them? 8. Describe the steps in conducting a hypothesis test. 9. What is a p-value? How do you interpret it in the context of a hypothesis test? 10. When would you use a t-test versus a z-test? 11. Explain how you would conduct an independent two-sample t-test. What assumptions must be met? 12. Describe a scenario where you would use a paired sample t-test. 13. What is ANOVA, and how does it differ from a t-test? 14. Explain how you would interpret the results of a one-way ANOVA. 15. Describe a situation where you might use a two-way ANOVA. 16. What is a chi-square test for independence? When would you use it? 17. How do you interpret the results of a chi-square goodness-of-fit test? 18. Explain the assumptions and limitations of chi-square tests. 19. What is the difference between simple linear regression and multiple regression? 20. How do you assess the goodness-of-fit of a regression model? 21. Explain multicollinearity and how you would detect and handle it in a regression model. 22. What is the difference between correlation and causation? 23. How do you interpret the Pearson correlation coefficient? 24. When would you use Spearman rank correlation instead of Pearson correlation? 25. What are some common methods for forecasting time series data? 26. Explain the components of a time series (trend, seasonality, residuals). 27. How would you handle missing data in a time series dataset? 28. Describe your approach to exploratory data analysis (EDA). 29. How do you handle outliers in a dataset? 30. Explain the steps you would take to validate the results of your analysis. 31. Give an example of how you have used statistical analysis to solve a real-world problem I have curated best 80+ top-notch Data Analytics Resources 👇👇 https://topmate.io/analyst/861634 Hope this helps you 😊

If you want to earn 6-figures working as a data analyst, learn these 6 important skills: Excel - advanced Excel functions for data manipulation and interpretation. Data Cleaning is about mastering data preprocessing and cleaning techniques. Python/R - data analysis, preparation and manipulation Statistical Analysis - understanding fundamental statistics for data Data Visualization - clear and effective visual representations of data SQL - querying and managing databases efficiently

If you are a data analyst and thinking of getting started with freelancing on upwork then here's something you should know. You should be ready to invest money if you want to get started with freelancing on upwork. So there's something called connects on Upwork. For simplicity you can consider connects as the currency of upwork which one will spend while submitting a proposal for the freelancing tasks listed on the platform. Previously upwork used to give some free connects to every new account but these days they don't. So you have to buy the connects at the rate of 100 connects per $15 + Taxes (without upgrading to upwork plus) which will be 1.3k + taxes in INR. Let's say you submit proposal for those jobs asking for 20 connects, the max you will be able to submit is 5 jobs and you will get the job or not again depend on many factors. You may end up having no jobs even after spending 100 connects and then again you have to repeat the cycle. Everything looks shiny from outside but reality can be different. Every platform requires investment either in the form of time, dedication, money or combination of all.

Best practices for writing SQL queries: Join for more: https://t.me/learndataanalysis 1- Write SQL keywords in capital letters. 2- Use table aliases with columns when you are joining multiple tables. 3- Never use select *, always mention list of columns in select clause. 4- Add useful comments wherever you write complex logic. Avoid too many comments. 5- Use joins instead of subqueries when possible for better performance. 6- Create CTEs instead of multiple sub queries , it will make your query easy to read. 7- Join tables using JOIN keywords instead of writing join condition in where clause for better readability. 8- Never use order by in sub queries , It will unnecessary increase runtime. 9- If you know there are no duplicates in 2 tables, use UNION ALL instead of UNION for better performance. SQL Basics: https://t.me/sqlanalyst/105

WebScraping with Gen AI During this session, we'll explore the following topics: 1️⃣ Basics of Web Scraping: Understand the fundamental concepts and techniques of web scraping and its legal and ethical considerations. 2️⃣ Scraping with Gen AI: Discover how Gen AI revolutionizes the web scraping landscape with real-world examples. 3️⃣ Jina Reader API: Get acquainted with the Jina Reader API, a powerful tool for obtaining LLM-friendly input from URLs or web searches. 4️⃣ ScrapeGraphAI: Dive into ScrapeGraphAI, a groundbreaking Python library that combines LLMs and direct graph logic for creating robust scraping pipelines. Event Details: 🗓 Date: 22 June, Saturday ⏰ Time: 11:00 AM IST 🔗 Register now: https://www.buildfastwithai.com/events/web-scraping-with-gen-ai Connect with Founder from IIT Delhi; https://www.linkedin.com/in/satvik-paramkusham/

Here are some Statistics Interview Questions for Data analyst Interview Que 1. What Is the Difference Between Inferential Statistics and Descriptive Statistics? Ans 1. The difference between inferential statistics and descriptive statistics is that inferential statistics are used to draw conclusions about a population based on the data you’ve collected. In contrast, descriptive statistics are used to summarize your data. Que 2. What Is the Difference Between Quantitative Data and Qualitative Data? Ans 2. Quantitative data is numerical data that can be measured, counted, and expressed as a percentage. Qualitative data is non-numerical information that describes subjective experiences or opinions about an event or topic. Que 3. How Do You Calculate Range and Interquartile Range? Ans 3. Range and interquartile range are two ways to calculate the spread of data. The range is the difference between the highest and lowest value in a set of data. The interquartile range is the difference between the 75th percentile and 25th percentile of a set of data. Que 4. Explain Pareto Principle Ans 4. The Pareto Principle, also known as the 80-20 rule, is a principle that states that 20% of causes are responsible for 80% of effects. Que 5. What Are Left-Skewed Distribution and Right-Skewed Distribution? Ans 5. Left-skewed distributions have a longer tail to the left (lower values), while right-skewed distributions have a longer tail to the right (higher values). Que 6. What Is an Outlier, and How Can You Find One? Ans 6. An outlier is an observation point that is distant from other data points. It’s important to note that the term “outlier” doesn’t refer to the numerical value of a data point but rather the distance between it and all other values. Que 7. What Are Skewness and Kurtosis? Ans 7. Skewness is an excellent way to measure the symmetry of distribution and the likelihood of a given value falling in the tails. With symmetrical distribution, the mean and median coincide. If the data distribution isn’t symmetrical, it’s skewed. There are two types of skewness: Positive is when the right tail is longer. Most values are clustered around the left tail, and the median is smaller than the mean. Negative is when the left tail is longer. Most values are clustered around the right tail, and the median is greater than the mean Kurtosis, on the other hand, reveals how heavy or light-tailed data is compared to the normal distribution. There are three types of kurtoses: Mesokurtic distributions approximate a normal distribution. Leptokurtic distributions have a pointy shape and heavy tails, indicating a high probability of extreme events occurring. Platykurtic distributions have a flat shape and light tails. They reveal a low probability of the occurrence of extreme events.

How to become a "Data Analyst" in 36 weeks? Here's the plan - ☞ Microsoft Excel (First 18 Weeks) (Mon-Fri) ☞ Power BI (Next 18 Weeks) (Mon-Fri) ☞ SQL (All 36 weeks) (Sat & Sun) Important Point :- ➜ Spend 2.5 hour's ( Mon - Fri) ➜ Spend 4 hour's ( Sat & Sun) ➜ Follow the Limited resources to avoid Confusion I have curated best 80+ top-notch Data Analytics Resources 👇👇 https://topmate.io/analyst/861634 Like if it helps :)

𝗪𝗮𝗻𝘁 𝘁𝗼 𝗸𝗻𝗼𝘄 𝘄𝗵𝗮𝘁 𝗵𝗮𝗽𝗽𝗲𝗻𝘀 𝗶𝗻 𝗮 𝗿𝗲𝗮𝗹 𝗱𝗮𝘁𝗮 𝗮𝗻𝗮𝗹𝘆𝘀𝘁 𝗶𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄? 𝗕𝗮𝘀𝗶𝗰 𝗜𝗻𝘁𝗿𝗼𝗱𝘂𝗰𝘁𝗶𝗼𝗻 -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? I have curated best 80+ top-notch Data Analytics Resources 👇👇 https://topmate.io/analyst/861634 Like if it helps :)

You don't need to know everything about every data tool. Focus on what will help land you your job. For Excel: - IFS (all variations) - XLOOKUP - IMPORTRANGE (in GSheets) - Pivot Tables - Dynamic functions like TODAY() For SQL: - Sum - Group By - Window Functions - CTEs - Joins For Tableau: - Calculated Columns - Sets - Groups - Formatting For Power BI: - Power Query for data transformation - DAX (Data Analysis Expressions) for creating custom calculations - Relationships between tables - Creating interactive and dynamic dashboards - Utilizing slicers and filters effectively I have created 100-Day Roadmap & Resources for Data Analyst 👇👇 https://topmate.io/analyst/981703 Hope it helps :)

Want to become a data analyst? Stage 1 – Excel Stage 2 – SQL + Project Stage 3 – Python (Pandas, NumPy) + Project Stage 4 – Data Visualization (Matplotlib, Seaborn) + Project Stage 5 – Statistics + Project Stage 6 – Machine Learning (Scikit-learn) + Project Stage 7 – Big Data Tools (Hadoop, Spark) + Project 🏆 – DataAnalytics

How to handle null values in data analytics project 👇👇 https://t.me/learndataanalysis/960

Some practical interview questions for data analyst role in Power BI: • Data Import Scenario: Describe how you would import data from various sources (Excel,SQL Server, CSV) into Power BI. • Data Cleaning Exercise: In Power BI, how would you handle a dataset with missing values and inconsistent formats to prepare it for analysis? • Handling Large Datasets: If you're working with a very large dataset in Power BI that is causing performance issues, what strategies would you use to optimize the data processing? • Calculated Columns and Measures: Explain how you would use calculated columns and measures in Power BI to analyze year-over-year growth. • Data Modeling Case: You have sales data in one table and customer data in another. How would you create a data model in Power BI to analyze customer purchase behavior? • Visualizations Task: Describe your approach to visualizing sales data in Power BI to highlight trends over time across different product categories. • Dashboard Optimization: A Power BI dashboard is loading slowly. What steps would you take to diagnose and improve its performance? • Data Refresh Scheduling: How would you set up and manage automatic data refreshes for a weekly sales report in Power BI? • Row-Level Security: How would you implement user-level security in Power BI for a report that needs different access levels for various users? • Troubleshooting a DAX Calculation: If a DAX formula in Power BI is not returning the expected results, how would you go about troubleshooting it? • Integration with Other Tools: Describe a scenario where you integrated Power BI with another tool or service (like Excel, Azure, or a web API). • Interactive Reports Creation: How would you design a Power BI report that allows user interaction, such as using slicers or drill-down features? • Adapting to Data Source Changes: If there are structural changes in a primary data source (like addition or removal of columns), how would you update your Power BI reports and dashboards? • Sharing Reports: Explain how you would share a report with your team and set up access controls using Power BI Service. • SQL Queries in Power BI: How do you use SQL queries in Power BI for advanced data transformation or analysis? • Error Handling in Data Sources: How do you manage and resolve errors in data sources or calculations in Power BI? • Custom Visuals Usage: Have you used custom visuals in Power BI? Describe the scenario and the benefits. • Power BI Templates: Provide an example of a situation where you created or used a Power BI template. What advantages did this offer? • Performance Tuning: What steps do you take to ensure your Power BI reports are performing optimally when dealing with large datasets or complex calculations? I have curated the best interview resources to crack Power BI Interviews 👇👇 https://topmate.io/analyst/866125 Hope you'll like it Like for more 👍❤️