EXL Data Analyst Interview Questions

CTC: 15 – 30 LPA
Experience: 0–5 Years

SQL INTERVIEW QUESTIONS

Question 1: Retrieve the Top 3 Revenue-Generating Products Within Each Category
Input Table: Products

ProductID ProductName Category Revenue

101 Laptop Pro Electronics 150000

102 Gaming PC Electronics 180000

103 Smartphone Electronics 120000

104 Tablet Air Electronics 90000

105 Wireless Earbuds Electronics 60000

201 Office Chair Furniture 80000

202 Dining Table Furniture 110000

203 Bookshelf Furniture 50000

204 Sofa Set Furniture 130000

301 T-Shirt Apparel 30000

302 Jeans Apparel 45000

303 Jacket Apparel 70000

MySQL Query:
SELECT
Category,
ProductName,
Revenue,
ProductRank
FROM (
SELECT
Category,
ProductName,
Revenue,
ROW_NUMBER() OVER (PARTITION BY Category ORDER BY Revenue DESC) AS
ProductRank
FROM Products
) AS RankedProducts
WHERE ProductRank <= 3
ORDER BY Category, ProductRank;
Output Table:

Category ProductName Revenue ProductRank

Apparel Jacket 70000 1

Apparel Jeans 45000 2

Apparel T-Shirt 30000 3

Electronics Gaming PC 180000 1

Electronics Laptop Pro 150000 2

Electronics Smartphone 120000 3

Furniture Sofa Set 130000 1

Furniture Dining Table 110000 2

Furniture Office Chair 80000 3

Question 2: Identify Products Whose Revenue Exceeds Overall Average
Input Table: Products

ProductID ProductName Revenue

101 Laptop Pro 150000

102 Gaming PC 180000

103 Smartphone 120000

104 Tablet Air 90000

201 Office Chair 80000

202 Dining Table 110000

301 T-Shirt 30000

302 Jeans 45000

MySQL Query:
SELECT
ProductName,
Revenue AS TotalProductRevenue
FROM Products
WHERE Revenue > (SELECT AVG(Revenue) FROM Products);
Output Table:

ProductName TotalProductRevenue

Laptop Pro 150000

Gaming PC 180000

Smartphone 120000

Dining Table 110000

Note: The overall average revenue for this table is approximately 100,250.
Question 3: Use LAG and CASE to Find Customers with Month-Over-Month Spending
Increase
Input Table: Transactions

TransactionID CustomerID TransactionDate Amount

1 C1 2023-01-10 100

2 C1 2023-01-25 50

3 C2 2023-01-15 200

4 C1 2023-02-05 180

5 C2 2023-02-20 150

6 C3 2023-02-10 300

7 C1 2023-03-01 250

8 C2 2023-03-10 220

9 C3 2023-03-15 280

MySQL Query:
WITH MonthlySpending AS (
SELECT
CustomerID,
DATE_FORMAT(TransactionDate, '%Y-%m') AS TransactionMonth,
SUM(Amount) AS MonthlyTotalSpending
FROM Transactions
GROUP BY CustomerID, DATE_FORMAT(TransactionDate, '%Y-%m')
),
LaggedSpending AS (
SELECT
CustomerID,
TransactionMonth,
MonthlyTotalSpending,
 LAG(MonthlyTotalSpending, 1, 0) OVER (PARTITION BY CustomerID ORDER BY
TransactionMonth) AS PreviousMonthSpending
FROM MonthlySpending
)
SELECT
CustomerID,
TransactionMonth AS Month,
MonthlyTotalSpending AS CurrentMonthSpending,
PreviousMonthSpending,
CASE
WHEN MonthlyTotalSpending > PreviousMonthSpending AND PreviousMonthSpending
> 0 THEN 'Increase'
WHEN MonthlyTotalSpending < PreviousMonthSpending AND PreviousMonthSpending
> 0 THEN 'Decrease'
WHEN MonthlyTotalSpending = PreviousMonthSpending AND PreviousMonthSpending
> 0 THEN 'No Change'
ELSE 'New/First Month'
END AS SpendingTrend
FROM LaggedSpending
WHERE MonthlyTotalSpending > PreviousMonthSpending AND PreviousMonthSpending > 0
ORDER BY CustomerID, TransactionMonth;
Output Table:

CustomerID Month CurrentMonthSpending PreviousMonthSpending SpendingTrend

C1 2023-03 250 180 Increase

C2 2023-03 220 150 Increase

Question 4: Mark the First and Last Transaction for Every User
Input Table: UserTransactions

TransactionID UserID TransactionDate Amount

101 U1 2023-01-05 50

102 U2 2023-01-08 120

103 U1 2023-01-15 75

104 U3 2023-01-20 200

105 U2 2023-01-22 90

106 U1 2023-02-01 100

107 U3 2023-02-10 150

108 U2 2023-02-15 110

109 U1 2023-02-25 80

MySQL Query:
SELECT
TransactionID,
UserID,
TransactionDate,
Amount,
CASE WHEN rn_asc = 1 THEN 'Yes' ELSE 'No' END AS IsFirstTransaction,
CASE WHEN rn_desc = 1 THEN 'Yes' ELSE 'No' END AS IsLastTransaction
FROM (
SELECT
TransactionID,
UserID,
TransactionDate,
Amount,
ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY TransactionDate ASC) AS
rn_asc,
 ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY TransactionDate DESC) AS
rn_desc
FROM UserTransactions
) AS RankedTransactions
ORDER BY UserID, TransactionDate;
Output Table:

TransactionID UserID TransactionDate Amount IsFirstTransaction IsLastTransaction

101 U1 2023-01-05 50 Yes No

103 U1 2023-01-15 75 No No

106 U1 2023-02-01 100 No No

109 U1 2023-02-25 80 No Yes

102 U2 2023-01-08 120 Yes No

105 U2 2023-01-22 90 No No

108 U2 2023-02-15 110 No Yes

104 U3 2023-01-20 200 Yes No

107 U3 2023-02-10 150 No Yes

Question 5: Find Employees Who Share the Same Manager and Earn the Same Salary
Input Table: Employees

EmployeeID EmployeeName ManagerID Salary

1 Alice 10 60000

2 Bob 10 70000

3 Charlie 11 65000

4 David 10 60000

5 Eve 11 65000

6 Frank 12 80000
EmployeeID EmployeeName ManagerID Salary

7 Grace 10 70000

8 Heidi 11 60000

MySQL Query:
SELECT
[Link] AS Employee1,
[Link] AS Employee2,
[Link],
[Link]
FROM Employees e1
JOIN Employees e2
ON [Link] = [Link]
AND [Link] = [Link]
AND [Link] < [Link]
ORDER BY [Link], [Link], [Link];
Output Table:

Employee1 Employee2 ManagerID Salary

Alice David 10 60000

Bob Grace 10 70000

Charlie Eve 11 65000

PYTHON INTERVIEW QUESTIONS
Question 6: Find the largest and smallest numbers in a list without using built-in min() or
max()
Approach:
We iterate through the list and maintain two variables, largest and smallest, initialized to the
first element of the list. For each element, we compare it to the current largest and smallest
and update accordingly. This ensures we find the extremes in a single pass.
Python Code:
def find_largest_smallest(nums):
"""
Finds the largest and smallest numbers in a list without using min() or max().

Args:
nums: List of numeric values.

Returns:
A tuple (smallest, largest)
"""
if not nums:
return None, None # Handle empty list

largest = smallest = nums[0]

for num in nums[1:]:
if num > largest:
largest = num
if num < smallest:
smallest = num
return smallest, largest

# Example Usage:
numbers = [4, 7, 1, 9, 0, 5]
smallest, largest = find_largest_smallest(numbers)
print(f"List: {numbers}")
print(f"Smallest: {smallest}, Largest: {largest}")

empty_list = []
smallest, largest = find_largest_smallest(empty_list)
print(f"Empty list: Smallest: {smallest}, Largest: {largest}")
Output:
List: [4, 7, 1, 9, 0, 5]
Smallest: 0, Largest: 9
Empty list: Smallest: None, Largest: None

Question 7: Remove duplicates from a list without using set()

Approach:
We can iterate through the list and maintain a new list to store unique elements. For each
element, we check if it is already in the new list; if not, we append it.
Python Code:
def remove_duplicates(input_list):
"""
Removes duplicates from a list without using set().

Args:
input_list: List with possible duplicate elements.

Returns:
A list with duplicates removed.
"""
unique_list = []
for item in input_list:
 if item not in unique_list:
unique_list.append(item)
return unique_list

# Example Usage:
numbers = [1, 2, 2, 3, 4, 4, 5]
print(f"Original list: {numbers}")
print(f"Without duplicates: {remove_duplicates(numbers)}")

strings = ['apple', 'banana', 'apple', 'cherry']

print(f"Original string list: {strings}")
print(f"Without duplicates: {remove_duplicates(strings)}")
Output:
Original list: [1, 2, 2, 3, 4, 4, 5]
Without duplicates: [1, 2, 3, 4, 5]
Original string list: ['apple', 'banana', 'apple', 'cherry']
Without duplicates: ['apple', 'banana', 'cherry']

Question 8: Flatten a nested list

Approach:
We iterate through the elements. If an element is a list, we recursively flatten it; otherwise,
we add it to the result list. This works for arbitrary levels of nesting.
Python Code:
def flatten_list(nested_list):
"""
Flattens a nested list into a single-level list.

Args:
nested_list: List containing nested lists.
 Returns:
A flattened list.
"""
flat_list = []
for item in nested_list:
if isinstance(item, list):
flat_list.extend(flatten_list(item))
else:
flat_list.append(item)
return flat_list

# Example Usage:
nested = [1, [2, 3], [4, [5, 6]], 7]
print(f"Original nested list: {nested}")
print(f"Flattened list: {flatten_list(nested)}")

nested_empty = []
print(f"Original empty nested list: {nested_empty}")
print(f"Flattened list: {flatten_list(nested_empty)}")
Output:
Original nested list: [1, [2, 3], [4, [5, 6]], 7]
Flattened list: [1, 2, 3, 4, 5, 6, 7]
Original empty nested list: []
Flattened list: []

Question 9: Find all prime numbers in a given range

Approach:
We iterate through each number in the range and check divisibility by all numbers from 2 up
to the square root of the number. If a number has no divisors, it is prime.
Python Code:
def primes_in_range(start, end):
"""
Finds all prime numbers in a given range [start, end].

Args:
start: Start of range (inclusive)
end: End of range (inclusive)

Returns:
A list of prime numbers in the range.
"""
primes = []
for num in range(max(2, start), end + 1):
is_prime = True
for i in range(2, int(num ** 0.5) + 1):
if num % i == 0:
is_prime = False
break
if is_prime:
[Link](num)
return primes

# Example Usage:
print(f"Primes between 10 and 20: {primes_in_range(10, 20)}")
print(f"Primes between 1 and 10: {primes_in_range(1, 10)}")
Output:
Primes between 10 and 20: [11, 13, 17, 19]
Primes between 1 and 10: [2, 3, 5, 7]
Question 10: Check if a list is a palindrome
Approach:
A palindrome list reads the same forwards and backwards. We can check this by comparing
elements from the start and end moving towards the center.
Python Code:
def is_palindrome_list(lst):
"""
Checks if a list is a palindrome.

Args:
lst: List to check

Returns:
True if palindrome, False otherwise.
"""
left = 0
right = len(lst) - 1
while left < right:
if lst[left] != lst[right]:
return False
left += 1
right -= 1
return True

# Example Usage:
lst1 = [1, 2, 3, 2, 1]
lst2 = ['a', 'b', 'b', 'a']
lst3 = [1, 2, 3, 4]

print(f"{lst1} is palindrome: {is_palindrome_list(lst1)}")

print(f"{lst2} is palindrome: {is_palindrome_list(lst2)}")
print(f"{lst3} is palindrome: {is_palindrome_list(lst3)}")
Output:
[1, 2, 3, 2, 1] is palindrome: True
['a', 'b', 'b', 'a'] is palindrome: True
[1, 2, 3, 4] is palindrome: False

MS EXCEL INTERVIEW QUESTIONS

Question 1: Difference between COUNT, COUNTA, and COUNTBLANK
Approach:
These three functions are used to count cells in a range, but they differ in what they include:
• COUNT: Counts only numeric values; ignores blanks, text, logical values, and errors.
• COUNTA: Counts all non-empty cells including numbers, text, logical values, and
errors.
• COUNTBLANK: Counts only empty cells. Cells with formulas that return "" are
considered blank.
Practical Example:

Cell Content

A1 100

A2 Hello

A3 "" (empty)

A4 50

A5 TRUE

A6 #N/A!

A7 (empty cell)

Formulas and Outputs:

• COUNT:
=COUNT(A1:A7) → 2 (A1, A4)
• COUNTA:
=COUNTA(A1:A7) → 5 (A1, A2, A4, A5, A6)
 • COUNTBLANK:
=COUNTBLANK(A1:A7) → 2 (A3, A7)

Question 2: Using INDEX + MATCH to look up a value to the left

Approach:
INDEX + MATCH is more flexible than VLOOKUP because it can return values from any
column, regardless of their position.
• MATCH(lookup_value, lookup_array, 0): Returns the position of the value.
• INDEX(array, row_num): Returns the value at the specified row in a column.
Practical Example:

EmployeeID EmployeeName Department

101 Alice HR

102 Bob Sales

103 Charlie IT

104 David Marketing

Scenario 1: Find Employee Name (Column B) for Employee ID (Column A)

• Lookup Value: 103 (E1)
• Formula: =INDEX(B:B, MATCH(E1, A:A, 0))
• Output: Charlie
Scenario 2: Find Employee ID (Column A) for Department (Column C) – Left lookup
• Lookup Value: IT (E2)
• Formula: =INDEX(A:A, MATCH(E2, C:C, 0))
• Output: 103
This demonstrates that INDEX + MATCH can perform “left lookups,” unlike VLOOKUP.

Question 3: Using IFERROR in complex formulas

Approach:
IFERROR(value, value_if_error) helps handle errors gracefully.
• value: Formula to check
• value_if_error: What to display if an error occurs
Practical Example:

ProductID Price

P001 150

P002 200

P003 120

• Scenario 1: Lookup P002 →

=IFERROR(VLOOKUP(D1, A1:B4, 2, FALSE), "Product Not Found") → 200
• Scenario 2: Lookup P005 →
=IFERROR(VLOOKUP(D2, A1:B4, 2, FALSE), "Product Not Found") → Product Not
Found
IFERROR ensures spreadsheets remain user-friendly and avoids unsightly errors like #N/A! or
#DIV/0!.

Question 4: Highlight duplicates excluding the first instance

Approach:
Use Conditional Formatting with a custom formula using COUNTIF.
• Formula: =COUNTIF($A$1:A1,A1)>1
o $A$1 → Absolute reference (top of the range)
o A1 → Relative reference (current cell)
Practical Example:

Apple

Banana

Apple

Cherry

Banana

Apple

Steps:
1. Select range A1:A6 → Home → Conditional Formatting → New Rule → Use a formula
 2. Enter formula: =COUNTIF($A$1:A1,A1)>1
3. Choose formatting (e.g., light red fill) → OK
Output:
• Highlighted cells: A3, A5, A6 (duplicates excluding first occurrence)

Question 5: SUMIFS to calculate total sales with multiple criteria

Approach:
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) sums values
based on multiple conditions.
Practical Example:

Region Month Sales

North Jan 100

West Jan 150

East Feb 200

West Jan 120

South Mar 80

West Feb 180

• Formula: =SUMIFS(C:C, A:A, "West", B:B, "Jan")

• Explanation: Sum Sales (C:C) where Region = West AND Month = Jan
• Output: 150 + 120 = 270
POWER BI INTERVIEW QUESTIONS
Question 1: Key Difference Between Calculated Column and Measure
Calculated Column:
• Definition: Adds a new column to a table; calculated row by row using DAX.
• Calculation Time: Values are computed at data refresh.
• Storage: Stored physically in the model; increases memory usage.
• Context: Row context (independent of report filters).
• Usage: Filtering, grouping, slicing, creating attributes.
• Example Use Cases:
• FullName = [FirstName] & " " & [LastName]
• ProfitMargin = ([Sales] - [Cost]) / [Sales]
• OrderYear = YEAR([OrderDate])
Measure:
• Definition: Dynamic calculation based on filter context; not stored physically.
• Calculation Time: Evaluated on-the-fly in visuals.
• Storage: Minimal memory (only DAX formula stored).
• Context: Filter context (affected by slicers, filters, cross-filtering).
• Usage: Aggregations, KPIs, ratios, complex analytics.
• Example Use Cases:
• Total Sales = SUM(Sales[Amount])
• Average Order Value = AVERAGEX(Sales, Sales[Amount])
Key Differences Summary:

Feature Calculated Column Measure

Calculation Row by row (stored) On-the-fly, aggregated

Storage Consumes memory Minimal (formula only)

Context Row context Filter context

Usage Attributes, slicing, grouping Aggregations, KPIs, calculations

Output Adds a column Returns a single aggregated value

When to Use:
• Calculated Column: New attributes per row.
• Measure: Aggregations or calculations that respond to filters/interactions.

Question 2: Managing Many-to-Many Relationships

Scenario: Students enroll in multiple courses, and courses have multiple students.
Solution: Use a Bridge Table (Junction Table / Factless Fact Table).
Steps:
1. Identify the two many-side tables (e.g., Students, Courses).
2. Create a Bridge Table containing unique keys of both tables (e.g., Enrollment:
StudentID, CourseID).
3. Establish One-to-Many Relationships:
o Students[StudentID] → Enrollment[StudentID]
o Courses[CourseID] → Enrollment[CourseID]
4. Set filter direction from “one” to “many.”
5. Use bridge table for aggregations and filter propagation.
Tables Example:

Students Courses Enrollment

StudentID (PK) CourseID (PK) StudentID (FK)

StudentName CourseName CourseID (FK)

EnrollmentDate

Why it Works:
• Selecting a student filters the bridge table → shows enrolled courses.
• Selecting a course filters bridge table → shows enrolled students.
• Prevents ambiguous filters and ensures accurate measures.
Question 3: DAX Measure for Cumulative Sales per Customer
Scenario: Running total of sales per customer.
DAX Formula:
Cumulative Sales Per Customer =
CALCULATE(
SUM(Sales[SalesAmount]),
FILTER(
ALL(DimDate[Date]),
DimDate[Date] <= MAX(DimDate[Date])
),
ALLEXCEPT(Sales, Sales[CustomerKey])
)
Explanation:
1. SUM(Sales[SalesAmount]) → Base aggregation.
2. FILTER(ALL(DimDate[Date]), DimDate[Date] <= MAX(DimDate[Date])) → Includes all
dates up to the current context for running total.
3. ALLEXCEPT(Sales, Sales[CustomerKey]) → Keeps Customer context intact while
ignoring other filters.
Visual Usage:
• Place CustomerKey and OrderDate in a table visual.
• The measure calculates sales cumulatively for each customer up to each date.

Question 4: Applying Dynamic Filters Using Slicers

Overview:
Slicers allow users to interactively filter data and create dynamic visuals.
Steps to Implement Slicers:
1. Add a Slicer Visual: Visualizations pane → Slicer icon.
2. Drag Field: e.g., Region, Year, Product Category.
3. Configure Settings:
o Multi-select / Single-select
 o Orientation: Vertical / Horizontal
o Search box for large lists
o Headers, fonts, colors
4. Manage Interactions: Format → Edit interactions → choose Filter, Highlight, or None.
5. Sync Slicers Across Pages: View → Sync Slicers → Sync selections across pages.
Best Practices:
• Logical grouping and placement
• Hierarchy slicers for drill-down
• Clear labels
• Include “Select All” or reset button with bookmarks

Question 5: Designing a KPI Visual for Current vs. Previous Month Sales
Steps:
Step 1: Base Measure for Total Sales
Total Sales = SUM(Sales[SalesAmount])
Step 2: Current Month Sales
VAR MaxDateSelected = MAX(DimDate[Date])
RETURN
CALCULATE(
[Total Sales],
FILTER(
ALL(DimDate[Date]),
YEAR(DimDate[Date]) = YEAR(MaxDateSelected) &&
MONTH(DimDate[Date]) = MONTH(MaxDateSelected)
)
)
Step 3: Previous Month Sales
VAR MaxDateSelected = MAX(DimDate[Date])
RETURN
CALCULATE(
[Total Sales],
FILTER(
ALL(DimDate[Date]),
DimDate[Date] >= STARTOFMONTH(EDATE(MaxDateSelected, -1)) &&
DimDate[Date] <= ENDOFMONTH(EDATE(MaxDateSelected, -1))
)
)
Step 4: KPI Visual Setup
1. Add KPI visual → Drag Current Month Sales to Indicator.
2. Drag Date hierarchy to Trend axis.
3. Drag Previous Month Sales to Target.
4. Format:
o Colors (green/red for performance)
o Trend axis visibility
o Display units
Outcome: KPI visual shows current month sales vs previous month, with trend and
performance indicator.