Deloitte Data Analyst Interview Questions and Answer

Deloitte Data Analyst Interview Questions: Insights and My Personal Approach to Answering Them

1. Tell us about yourself and your current job responsibilities.

Example Answer: "I am currently working as a Data Analyst at [Company Name], where I manage and analyze large datasets to drive business insights. My responsibilities include creating and maintaining Power BI dashboards, performing advanced SQL queries to extract and transform data, and collaborating with cross-functional teams to improve data-driven decision-making. Recently, I worked on a project where I streamlined reporting processes using DAX measures and optimized SQL queries, reducing report generation time by 30%."

---

2. Can you share some challenges you encountered in your recent project involving Power BI dashboards, and how did you resolve them?

Example Challenge: In a recent project, one of the key challenges was handling complex relationships between multiple datasets, which caused performance issues and incorrect aggregations in Power BI.

Resolution: To address this:

• I optimized data models by reducing unnecessary columns and creating a star schema for better relationships.

• Used summarized tables and DAX functions like SUMX and CALCULATE to simplify calculations.

• Implemented row-level security for user-specific data, which reduced redundancy and improved dashboard performance.

---

3. What distinguishes a KPI (Key Performance Indicator) from a dimension?

• KPI: A KPI is a measurable value that indicates performance relative to a goal (e.g., "Revenue Growth %," "Customer Retention Rate").

  • Example: Monthly Sales Target Achievement = 90%.
  • Example: Region = "North America," Product Category = "Electronics."

• Dimension: A dimension provides descriptive information and is used for categorization or filtering (e.g., "Region," "Product Category").

Key Difference: KPIs measure performance, while dimensions provide context.

---

4. Write a SQL query to find the third highest salary from an employee table with the following columns: EID, ESalary.

sql

SELECT DISTINCT ESalary 
FROM Employee
ORDER BY ESalary DESC
LIMIT 1 OFFSET 2;

Alternatively (if LIMIT is not supported):

sql

SELECT MAX(ESalary) 
FROM Employee
WHERE ESalary < (
    SELECT MAX(ESalary) 
    FROM Employee
    WHERE ESalary < (
        SELECT MAX(ESalary) FROM Employee
    )
);

---

5. Create a SQL procedure using ESalary as a parameter that selects all EIDs from the Employee table where ESalary is less than 50,000.

sql

CREATE PROCEDURE GetLowSalaryEmployees(IN salaryLimit INT)
BEGIN
    SELECT EID 
    FROM Employee
    WHERE ESalary < salaryLimit;
END;

Call Example:

sql

CALL GetLowSalaryEmployees(50000);

---

6. For the Employee table (columns EID, ESalary), retrieve all EIDs with odd salaries and join this with another table, empdetails (columns EID, EDOB), to obtain EDOB.

sql

SELECT e.EID, ed.EDOB
FROM Employee e
JOIN empdetails ed
ON e.EID = ed.EID
WHERE e.ESalary % 2 = 1;

---

7. How would you use the LEAD or LAG function in SQL to compare week-over-week data?

Example: To calculate the week-over-week sales difference:

sql

SELECT 
    Week, 
    Sales, 
    LEAD(Sales) OVER (ORDER BY Week) AS NextWeekSales,
    Sales - LAG(Sales) OVER (ORDER BY Week) AS WeekOverWeekDifference
FROM SalesData;

• LEAD fetches the value for the next row.
• LAG fetches the value for the previous row.

---

8. Can you explain how you would create a DAX measure in Power BI to calculate the year-over-year growth for a specific metric?

DAX Measure Example:

DAX

YoY Growth = 
DIVIDE(
    [Total Sales] - CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date])),
    CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
)

• SAMEPERIODLASTYEAR: Compares sales for the same period last year.
• DIVIDE: Calculates the percentage growth.

---

9. Identify a unique chart type in Power BI that differs from standard charts and explain its purpose.

Example Chart: Decomposition Tree

• Purpose: Breaks down a key metric (e.g., Total Sales) by various dimensions (e.g., Region, Product, Time).
• Use Case: Helps identify root causes for trends or variances.

---

10. Describe how you would implement a time intelligence feature in Power BI to analyze sales trends over different time periods.

Steps:

1. Create a Date Table: Use DAX to generate a continuous date table with columns for Year, Quarter, Month, Week, etc.

   DAX
   
   DateTable = CALENDAR(DATE(2022, 1, 1), DATE(2024, 12, 31))
   

2. Relate to Sales Data: Link the Date column in the date table to the sales dataset.

3. Create Time Intelligence Measures:

   • Sales Last Month:

     DAX
     
     SalesLastMonth = CALCULATE([Total Sales], PREVIOUSMONTH('Date'[Date]))
     

   • Sales Trend:

     DAX
     
     SalesTrend = CALCULATE([Total Sales], DATESYTD('Date'[Date]))
     

4. Visualize Trends: Use line charts or time slicers to showcase data over time (e.g., year-to-date, month-over-month).

---

These examples should give you a strong foundation for preparing responses to Deloitte's interview questions!