Question 1

A report query is running very slowly on a large dataset. Walk through your approach to diagnosing and improving SQL query performance.

Accepted Answer

Diagnosing and improving slow SQL queries requires a systematic approach that considers the query structure, database design, and the execution plan. As a BI analyst, encountering slow queries is inevitable as data volumes grow.

Start with the execution plan. Use EXPLAIN or EXPLAIN ANALYZE to see how the database processes the query. The execution plan reveals which tables are being scanned, what join methods are used, whether indexes are being utilized, and where the most time is spent. Look for full table scans on large tables, which indicate missing or unused indexes.

Examine the quer

Question 2

How would you write a SQL query to calculate month-over-month revenue growth percentage for each product category, including handling months with zero or no sales?

Accepted Answer

Calculating month-over-month revenue growth by product category requires combining aggregation, window functions, and careful NULL handling. This is a common BI requirement for trend analysis.

The approach involves several logical steps. First, aggregate revenue by month and category. Then use the LAG window function to access the previous month value. Finally, calculate the percentage change while handling edge cases.

Start by creating a complete date-category grid. If a product category had no sales in a particular month, that month-category combination will be missing from a simple ag

Question 3

What are small multiples in data visualization, and why are they effective for comparing data across categories or time periods?

Accepted Answer

Small multiples, also called trellis charts or panel charts, are a series of similar charts using the same scale and axes, each showing a different subset of the data. Edward Tufte popularized the concept as one of the most effective techniques for multivariate data visualization.

The core idea is to repeat a simple chart structure across multiple panels, one for each category or group. For example, instead of putting twelve product lines on a single line chart that becomes an unreadable tangle, you create twelve small line charts arranged in a grid. Each shows the trend for one product, al

Question 4

How would you visualize the relationship between customer acquisition cost, customer lifetime value, and churn rate across different customer segments? What visualization approach would you choose and why?

Accepted Answer

Visualizing three interrelated business metrics across segments requires careful design to make the relationships clear without overwhelming the viewer. There are several viable approaches, and the best choice depends on the specific story you need to tell.

A bubble chart is the most natural starting point for three variables. Plot customer acquisition cost on the x-axis, customer lifetime value on the y-axis, and encode churn rate as bubble size. Color each bubble by customer segment. This single view shows the relationship between all three metrics simultaneously. Quadrant lines can divid

Question 5

How do you use data validation in Excel to ensure data quality when multiple people are entering data into a shared workbook?

Accepted Answer

Data validation in Excel restricts the type and range of data that can be entered into specific cells. It is a critical tool for maintaining data quality in shared workbooks where multiple people contribute data, reducing errors at the point of entry rather than cleaning them after the fact.

The most common validation type is dropdown lists. By defining a list of acceptable values, you ensure that categorical fields like region, department, or status are entered consistently. Without dropdowns, one person types "North America" while another types "N. America" or "NA," creating inconsistenci

Question 6

What is the difference between VLOOKUP and INDEX-MATCH in Excel? When would you use each one?

Accepted Answer

VLOOKUP and INDEX-MATCH both retrieve data from a table based on a lookup value, but INDEX-MATCH is more flexible and generally preferred by experienced analysts.

VLOOKUP searches for a value in the leftmost column of a range and returns a value from a specified column number. The syntax is VLOOKUP(lookup_value, table_array, col_index_num, range_lookup). For example, VLOOKUP(A2, Products!A:D, 3, FALSE) looks up the value in A2 within the Products sheet and returns the value from the third column. The FALSE parameter specifies an exact match.

The key limitation of VLOOKUP is that it can o

Question 7

Revenue dropped ten percent this quarter. Walk through how you would decompose this metric to identify the root cause and present findings to leadership.

Accepted Answer

A ten percent revenue drop demands systematic investigation rather than jumping to conclusions. Metric decomposition breaks a high-level number into its component parts to isolate where the decline originated and why.

Start with the revenue formula decomposition. Revenue equals the number of customers multiplied by revenue per customer. Alternatively, revenue equals number of transactions multiplied by average transaction value. Determine which component changed. Did you have fewer customers, or did each customer spend less? This first split immediately narrows the investigation.

Next, s

Question 8

Two departments report different numbers for what they call the same metric. As a BI analyst, how do you resolve this and prevent it from happening again?

Accepted Answer

Metric conflicts between departments are one of the most common and damaging data problems in organizations. They erode trust in data and waste time in meetings arguing about which number is right. Resolving and preventing them is a critical BI analyst skill.

Start by understanding both numbers without assuming either is wrong. Meet with each department separately and document exactly how they calculate the metric. Get the precise definition, the data sources, the filters applied, the time boundaries, and any exclusions. Often what looks like a conflict is actually two valid calculations of

Question 9

A stakeholder asks you to add fifteen new metrics to an already crowded dashboard. How do you handle this request while maintaining dashboard quality and usability?

Accepted Answer

This situation tests your ability to balance stakeholder needs with design quality. Simply adding fifteen metrics to an already crowded dashboard would make it unusable, but flatly refusing a stakeholder request is not effective either. The solution requires understanding the underlying need and proposing alternatives.

Start by understanding the motivation. Ask why these metrics are needed now. What changed in the business that requires additional monitoring? Is there a new initiative, a recent problem, or a upcoming review that prompted this request? Understanding the why often reveals tha

Question 10

How do you handle data refresh and ensure that dashboards always show accurate, up-to-date information? What strategies do you use for different refresh requirements?

Accepted Answer

Data refresh strategy determines how current and reliable dashboard information is. Different business needs require different refresh approaches, and choosing the right one balances data freshness against system performance and cost.

Scheduled batch refresh is the most common approach. Data is extracted from source systems, transformed, and loaded into the reporting database at defined intervals. Daily refreshes running overnight are typical for most business dashboards. Weekly refreshes work for strategic reports that do not require daily monitoring. The key consideration is timing the re

Business Intelligence Analyst

Topics

SQL for Analytics

Data Visualization Fundamentals

Excel & Spreadsheet Mastery

Business Metrics & KPIs

Reporting & Dashboard Basics

Mock Interview

Quick Stats