Scenario based Interview Questions & Answers for Data Analyst 1. Scenario: You are working on a SQL database that stores customer information. The database has a table called "Orders" that contains order details. Your task is to write a SQL query to retrieve the total number of orders placed by each customer. Question: - Write a SQL query to find the total number of orders placed by each customer. Expected Answer: SELECT CustomerID, COUNT(*) AS TotalOrders FROM Orders GROUP BY CustomerID; 2. Scenario: You are working on a SQL database that stores employee information. The database has a table called "Employees" that contains employee details. Your task is to write a SQL query to retrieve the names of all employees who have been with the company for more than 5 years. Question: - Write a SQL query to find the names of employees who have been with the company for more than 5 years. Expected Answer: SELECT Name FROM Employees WHERE DATEDIFF(year, HireDate, GETDATE()) > 5; Power BI Scenario-Based Questions 1. Scenario: You have been given a dataset in Power BI that contains sales data for a company. Your task is to create a report that shows the total sales by product category and region. Expected Answer: - Load the dataset into Power BI. - Create relationships if necessary. - Use the "Fields" pane to select the necessary fields (Product Category, Region, Sales). - Drag these fields into the "Values" area of a new visualization (e.g., a table or bar chart). - Use the "Filters" pane to filter data as needed. - Format the visualization to enhance clarity and readability. 2. Scenario: You have been asked to create a Power BI dashboard that displays real-time stock prices for a set of companies. The stock prices are available through an API. Expected Answer: - Use Power BI Desktop to connect to the API. - Go to "Get Data" > "Web" and enter the API URL. - Configure the data refresh settings to ensure real-time updates (e.g., setting up a scheduled refresh or using DirectQuery if supported). - Create visualizations using the imported data. - Publish the report to the Power BI service and set up a data gateway if needed for continuous refresh. 3. Scenario: You have been given a Power BI report that contains multiple visualizations. The report is taking a long time to load and is impacting the performance of the application. Expected Answer: - Analyze the current performance using Performance Analyzer. - Optimize data model by reducing the number of columns and rows, and removing unnecessary calculations. - Use aggregated tables to pre-compute results. - Simplify DAX calculations. - Optimize visualizations by reducing the number of visuals per page and avoiding complex custom visuals. - Ensure proper indexing on the data source. Hope it helps :)