EXL Interview question and answer for Power BI Developer (3 Years of Experience)

EXL Interview Experience for Power BI Developer (3 Years of Experience)

I recently appeared for an interview at EXL for the role of Power BI Developer. The selection process consisted of three rounds:

• 2 Technical Rounds
• 1 Managerial Round

Here, I’ll share the key technical questions I encountered, along with my approach to answering them.

---

SQL Questions

1️⃣ Write a SQL query to find the second most recent order date for each customer from a table Orders (OrderID, CustomerID, OrderDate).

To solve this, I used the ROW_NUMBER() window function:

sql

WITH RankedOrders AS (
    SELECT 
        CustomerID, 
        OrderDate, 
        ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS RowNum
    FROM Orders
)
SELECT 
    CustomerID, 
    OrderDate AS SecondMostRecentOrderDate
FROM RankedOrders
WHERE RowNum = 2;

2️⃣ Write a query to find the nth highest salary from a table Employees with columns (EmployeeID, Name, Salary).

The DENSE_RANK() function helped here, along with the input for n:

sql

WITH RankedSalaries AS (
    SELECT 
        Salary, 
        DENSE_RANK() OVER (ORDER BY Salary DESC) AS Rank
    FROM Employees
)
SELECT Salary
FROM RankedSalaries
WHERE Rank = <n>; -- Replace <n> with the desired rank

3️⃣ Explain the order of execution of SQL queries.

The logical order of execution for a SQL query is as follows:

1. FROM (including JOINs)
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

4️⃣ Given a table Products (ProductID, Name, Price) and a table Sales (SaleID, ProductID, Quantity), write a query to find the product with the highest revenue.

This involved calculating the total revenue per product and finding the maximum:

sql

SELECT 
    p.Name AS ProductName, 
    SUM(s.Quantity * p.Price) AS Revenue
FROM Products p
JOIN Sales s ON p.ProductID = s.ProductID
GROUP BY p.Name
ORDER BY Revenue DESC
LIMIT 1;

5️⃣ Write a query to calculate the cumulative salary of employees department-wise, who joined the company in the last 30 days.

Using SUM() as a window function:

sql

SELECT 
    DepartmentID, 
    EmployeeID, 
    Name, 
    SUM(Salary) OVER (PARTITION BY DepartmentID ORDER BY JoinDate) AS CumulativeSalary
FROM Employees
WHERE JoinDate >= DATEADD(DAY, -30, GETDATE());

---

Power BI Questions

1️⃣ Explain Row-Level Security (RLS) and its types.

Row-Level Security (RLS) in Power BI restricts data access based on user roles. It can be categorized into:

• Static RLS: Hardcoded filters applied during data modeling.
• Dynamic RLS: Filters applied based on the logged-in user’s attributes.

2️⃣ Why choose Power BI over other BI tools?

• User-friendly interface
• Seamless integration with Microsoft tools like Excel, Teams, and SharePoint
• Advanced data visualization and DAX capabilities
• Strong community support and frequent updates
• Cost-effective compared to other BI tools like Tableau

3️⃣ What are the different components of Power BI, and why do we need them?

• Power BI Desktop: Used for creating and designing reports.
• Power BI Service: A cloud-based platform for sharing and collaboration.
• Power BI Mobile: Access reports on the go.
• Power BI Gateway: Enables data refresh from on-premises sources.
• Power BI Report Server: Used for on-premises report hosting.

4️⃣ What are the different connectivity modes in Power BI?

• Import Mode: Data is imported into Power BI for faster performance.
• DirectQuery Mode: Connects directly to the data source without importing.
• Live Connection: Similar to DirectQuery but used for multidimensional models like SSAS.
• Composite Mode: Allows a combination of Import and DirectQuery.

---

Final Thoughts

This interview experience not only tested my technical proficiency in SQL and Power BI, but also emphasized the importance of understanding practical applications and scalability.

Sharing these questions and approaches will hopefully help those preparing for similar roles. Best of luck to everyone on their journey to becoming a Power BI expert! 🚀

#PowerBI #SQL #InterviewExperience #DataAnalyst #BIAnalyst