Accenture Data Analyst
Interview
YoE (1–4 Years)
CTC: 9–13 LPA
SQL Questions
1. Write a query to fetch the top 3 employees with the highest
salaries from an Employees table.
Fetch the Top 3 Employees with the Highest Salaries
Input Table: Employees
emp_id emp_name salary
101 Alice 95000
102 Bob 87000
103 Charlie 91000
104 David 75000
105 Eve 99000
Query:
SELECT emp_id, emp_name, salary
FROM Employees
ORDER BY salary DESC
LIMIT 3;
2. Write a Query to Find Customers Who Have Placed More Than
5 Orders in the Last 30 Days
Input Table: Orders
order_id customer_id order_date
201 1 2024-02-15
202 2 2024-02-10
203 1 2024-02-18
204 3 2024-02-20
205 1 2024-02-25
206 2 2024-02-27
207 1 2024-03-02
Query:
SELECT customer_id
FROM Orders
WHERE order_date >= NOW() - INTERVAL 30 DAY
GROUP BY customer_id
HAVING COUNT(order_id) > 5;
3. Write a Query to Retrieve the First and Last Purchase Date for
Each Customer
Input Table: Transactions
transaction_id customer_id purchase_date
301 1 2024-01-10
302 2 2024-01-12
303 1 2024-02-15
304 2 2024-02-20
305 1 2024-03-05
Query:
SELECT customer_id,
MIN(purchase_date) AS first_purchase,
MAX(purchase_date) AS last_purchase
FROM Transactions
GROUP BY customer_id;
4. Write a Query to Fetch All Orders Placed on Weekends
Input Table: Orders
order_id customer_id order_date
401 1 2024-02-17
402 2 2024-02-18
403 3 2024-02-19
404 1 2024-02-24
Query:
SELECT *
FROM Orders
WHERE DAYOFWEEK(order_date) IN (1, 7); -- 1 for Sunday, 7 for Saturday
5. Write a Query to Calculate the Average Sales per Region
Input Table: Sales
sales_id region sales_amount
501 North 50000
502 South 70000
503 North 80000
504 West 90000
Query:
SELECT region,
AVG(sales_amount) AS avg_sales
FROM Sales
GROUP BY region;
6. Write a query to Find the Second-Highest Salary Without
Using LIMIT or TOP
Query:
SELECT MAX(salary) AS second_highest_salary
FROM Employees
WHERE salary < (SELECT MAX(salary) FROM Employees);
7. Write a Query to Find Customers Who Have Never Placed an
Order
Input Tables:
Customers Table
customer_id customer_name
1 John
2 Alice
3 Bob
Orders Table
order_id customer_id
101 1
102 2
Query:
SELECT c.customer_id, c.customer_name
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;
8. Write a Query to Find the Most Frequently Sold Product in
Each Category
Input Table: Products
product_id category product_name
601 A Apple
product_id category product_name
602 B Banana
603 A Orange
Input Table: Sales
sale_id product_id quantity_sold
701 601 50
702 602 30
703 601 40
704 603 60
Query:
SELECT p.category, p.product_name, s.total_sold
FROM (
SELECT product_id, SUM(quantity_sold) AS total_sold,
RANK() OVER (PARTITION BY p.category ORDER BY SUM(quantity_sold) DESC) AS rnk
FROM Sales s
JOIN Products p ON s.product_id = p.product_id
GROUP BY p.category, s.product_id
)s
JOIN Products p ON s.product_id = p.product_id
WHERE s.rnk = 1;
9.Write a SQL query to Calculate Profit Margin for Each Product
and Rank Them Within Their Category
Input Table: Products
product_id category revenue cost
801 A 10000 7000
802 A 15000 9000
803 B 20000 15000
Query:
SELECT product_id, category,
(revenue - cost) / revenue AS profit_margin,
RANK() OVER (PARTITION BY category ORDER BY (revenue - cost) / revenue DESC) AS
rank
FROM Products;
10. Write a query to Fetch Orders Where the Total Order Value
Exceeds the Average Order Value
Input Table: Orders
order_id customer_id total_value
901 1 5000
902 2 7000
903 3 6000
904 4 8000
Query:
SELECT *
FROM Orders
WHERE total_value > (SELECT AVG(total_value) FROM Orders);
Power BI Questions
1. How would you create a dynamic report that shows revenue by
region and allows filtering by product and sales channel?
Creating a Dynamic Report for Revenue by Region with Filters for Product and Sales
Channel
Steps:
1. Load the data into Power BI.
2. Create a bar chart or matrix visual to display revenue by region.
3. Add slicers for Product and Sales Channel:
o Click on the Slicer visual in Power BI.
o Drag Product and Sales Channel fields into separate slicers.
4. Ensure that the slicers are synced across all report pages.
5. Use interactions to control how visuals respond to filters.
2. Write a DAX measure to calculate month-over-month revenue
growth.
DAX Measure for Month-over-Month Revenue Growth
MoM Growth =
VAR PrevMonthSales =
CALCULATE(
SUM(Sales[Revenue]),
PREVIOUSMONTH(Sales[Date])
)
VAR CurrentMonthSales = SUM(Sales[Revenue])
RETURN
IF(PrevMonthSales = 0, BLANK(), (CurrentMonthSales - PrevMonthSales) /
PrevMonthSales)
• This calculates the percentage change in revenue compared to the previous month.
3. Create a DAX measure to calculate cumulative sales for a
product category year-to-date.
DAX Measure for Cumulative Sales Year-to-Date (YTD) for a Product Category
Cumulative Sales YTD =
CALCULATE(
SUM(Sales[Revenue]),
FILTER(
ALLSELECTED(Sales),
Sales[Date] <= MAX(Sales[Date])
• This calculates running total sales for the selected product category in the current
year.
4. Explain how to create a slicer in Power BI to toggle between
different time periods (Monthly, Quarterly, Yearly).
Creating a Slicer to Toggle Between Monthly, Quarterly, and Yearly Views
Steps:
1. Create a calculated table with time periods:
TimePeriod = DATATABLE(
"Period", STRING,
{ "Monthly", "Quarterly", "Yearly" }
2. Add this table to a slicer.
3. Create a switch-based measure to adjust the date aggregation:
Revenue by Period =
SWITCH(
SELECTEDVALUE(TimePeriod[Period]),
"Monthly", SUM(Sales[Revenue]),
"Quarterly", CALCULATE(SUM(Sales[Revenue]), DATESQTD(Sales[Date])),
"Yearly", CALCULATE(SUM(Sales[Revenue]), DATESYTD(Sales[Date])),
SUM(Sales[Revenue]) -- Default to monthly if none selected
4. Use this measure in the report visuals.
5. Write a DAX formula to rank sales representatives based on
their total sales within each region.
DAX Formula to Rank Sales Representatives by Total Sales Within Each Region
Sales Rank =
RANKX(
FILTER(ALL(Sales), Sales[Region] = MAX(Sales[Region])),
SUM(Sales[Revenue]),
DESC,
DENSE
)
• This ranks sales reps based on total sales within their respective regions.
6. How would you implement Row-Level Security (RLS) in Power
BI to restrict data access based on user roles?
Implementing Row-Level Security (RLS) in Power BI
Steps:
1. Go to Model View → Manage Roles → Create New Role.
2. Define DAX Filter for the role:
[Region] = USERPRINCIPALNAME()
o This restricts users to see only their assigned region’s data.
3. Assign roles to users in the Power BI Service under Security settings.
7. How would you design a dashboard that dynamically
highlights top-performing regions and underperforming
products?
Designing a Dashboard to Highlight Top-Performing Regions and Underperforming
Products
Key Features:
• KPI Cards: Show overall revenue, profit, and sales trends.
• Bar Chart (Top-Performing Regions):
o Sort revenue in descending order.
o Use conditional formatting to highlight regions above the target.
• Table (Underperforming Products):
o Apply a DAX measure to flag products with sales below a threshold:
Low Sales Flag = IF(SUM(Sales[Revenue]) < 10000, "Underperforming", "Good")
o Apply conditional formatting to highlight low-performing products.
• Slicers for dynamic filtering by region, product, and sales channel.
8. Explain the steps to create a Data Flow in Power BI Service to
consolidate data from multiple sources.
Creating a Data Flow in Power BI Service
Steps:
1. Open Power BI Service → Go to Workspaces → Click New → Dataflow.
2. Select Add Data → Choose a data source (SQL, Excel, API, etc.).
3. Use Power Query Online to clean and transform data.
4. Define relationships and apply aggregations if needed.
5. Save and Refresh the Dataflow → Use it as a dataset in Power BI reports.
9. How do you optimize a large dataset in Power BI to improve
performance and refresh time?
Optimizing Large Datasets in Power BI for Performance & Refresh Time
Best Practices:
• Use Import Mode for smaller datasets, DirectQuery for real-time access.
• Remove Unused Columns in Power Query to reduce model size.
• Use Aggregations to pre-calculate summaries instead of detailed data.
• Create Star Schema: Avoid too many relationships and use fact & dimension
tables.
• Optimize DAX Measures: Prefer SUMX() over FILTER() when working with row-level
calculations.
• Reduce Data Granularity: Use pre-aggregated tables if possible.
• Enable Query Folding: Push transformations back to the database.
10. Demonstrate how you would use Power Query to:
Replace missing values in a dataset.
Unpivot columns to normalize the data.
Merge data from two files with different column names.
Power Query Tasks
(a) Replacing Missing Values
Steps:
1. Open Power Query Editor.
2. Select the column with missing values.
3. Click Transform → Replace Values → Enter a default value (0, Unknown, etc.).
4. Click Close & Apply to update the dataset.
M Code Alternative:
Table.ReplaceValue(Source, null, "Unknown", Replacer.ReplaceValue, {"ColumnName"})
(b) Unpivoting Columns to Normalize Data
Before Unpivoting:
Region Jan Sales Feb Sales Mar Sales
North 5000 6000 7000
South 4000 5000 6000
After Unpivoting:
Region Month Sales
North Jan Sales 5000
Region Month Sales
North Feb Sales 6000
South Jan Sales 4000
Steps in Power Query:
1. Select Jan Sales, Feb Sales, Mar Sales columns.
2. Click Transform → Unpivot Columns.
3. Rename the columns as Month and Sales.
(c) Merging Data from Two Files with Different Column Names
Example:
• File 1: Contains Customer_ID, Full_Name, Amount_Spent.
• File 2: Contains ID, Name, Total_Spent.
Steps in Power Query:
1. Load both files into Power Query.
2. Rename columns in File 2 to match File 1 (ID → Customer_ID, Name → Full_Name,
Total_Spent → Amount_Spent).
3. Click Home → Merge Queries.
4. Choose Customer_ID as the common key.
5. Expand the merged table and select required columns.
Behavioral & Scenario-Based Questions
1. Describe a time when you automated a reporting process to
improve efficiency.
Automating a Reporting Process for Efficiency
Situation: At my current role at Unisys, I was responsible for generating weekly reports on
incident resolution times for two clients. The manual process involved exporting data from
a data warehouse, cleaning it in Excel, and then updating Power BI dashboards.
Task: The process was time-consuming and prone to errors, so I needed to automate it to
improve efficiency.
Action:
• I leveraged Power Query to directly connect to the data warehouse and clean the
data within Power BI instead of using Excel.
• I used scheduled refresh in Power BI Service to automate data updates.
• I built DAX measures to calculate key metrics dynamically instead of using static
Excel formulas.
Result: The automation reduced report preparation time from 4 hours per week to 30
minutes, eliminated manual errors, and improved data refresh speed, allowing
stakeholders to access real-time insights.
2. Share an experience where you discovered a key business
insight through data analysis that influenced a decision.
Discovering a Key Business Insight Through Data Analysis
Situation: While analyzing customer support data for a client, I noticed an unusual
increase in chat support resolution times over the last quarter.
Task: My goal was to identify the cause of the issue and suggest improvements.
Action:
• I performed a trend analysis in Power BI and found that chat response times spiked
between 6 PM - 10 PM.
• Using data segmentation, I discovered that a new offshore support team was
handling chats during these hours.
• I compared chat transcripts before and after the shift change and identified that
agents lacked proper training, leading to delays.
Result: I presented the findings to the operations team, who implemented targeted
training. Within a month, chat resolution times improved by 25%, reducing customer
complaints.
3. How do you prioritize tasks when handling multiple projects
with tight deadlines?
Prioritizing Tasks With Multiple Projects & Tight Deadlines
Situation: As a Business Intelligence Engineer, I often work on multiple dashboards for
different clients, each with urgent deadlines.
Task: I needed to manage my time effectively while ensuring all deliverables met business
expectations.
Action:
• I used the Eisenhower Matrix to categorize tasks into urgent-important,
important-not-urgent, etc.
• I communicated with stakeholders to understand which insights were most critical
for decision-making.
• I leveraged Power BI templates and reusable DAX measures to speed up
development.
• I set up early-stage reviews to catch potential issues before the final submission.
Result: By focusing on high-impact tasks first and automating repetitive steps, I
successfully delivered all projects on time without compromising quality.
4. Have you faced discrepancies in data from different sources?
How did you resolve it?
Handling Discrepancies in Data From Different Sources
Situation: While building a financial dashboard, I noticed a mismatch between SQL-
based revenue data and Excel reports from the finance team.
Task: My goal was to identify the root cause and ensure accurate reporting.
Action:
• I compared data from multiple sources to pinpoint where discrepancies originated.
• I checked data refresh logs in Power BI and found that SQL data was updating
daily, while the Excel reports were manual uploads that were updated weekly.
• I worked with the finance team to standardize the data refresh frequency.
• I added a data validation step in Power Query to flag mismatches before the report
refresh.
Result: After implementing these changes, we eliminated data mismatches and improved
stakeholder trust in the dashboard.
5. How do you collaborate with cross-functional teams to ensure
data insights align with business goals?
Collaborating With Cross-Functional Teams for Business Insights
Situation: I worked on a customer retention dashboard for a retail client, requiring inputs
from marketing, sales, and customer service teams.
Task: My challenge was to ensure the dashboard provided insights that aligned with all
stakeholders' needs.
Action:
• I conducted stakeholder interviews to understand key metrics each team needed.
• I created mockup dashboards in Power BI and iterated based on feedback.
• I used row-level security (RLS) to ensure teams saw only relevant data.
• I set up monthly review meetings to refine the dashboard based on evolving
business needs.
Result: The final dashboard provided actionable insights, helping increase customer
retention by 12% in three months through targeted marketing campaigns.
