Data analyst technical interviews: an answer, an example, a limitation

You know the definition of a LEFT JOIN, but someone asks for an example and your explanation stalls. Or you can build a Power BI report, yet struggle to explain why one number changes when a filter is selected. I would rather find those gaps while preparing. My starting point is to explain a simple operation out loud and check whether the explanation survives a follow-up question.

This article covers Excel, SQL, Power BI, Tableau and Python at a junior or junior-plus level. These are practice questions, without a claim that I have measured how frequently employers ask them. A particular team may use only some of these tools. Start with the role description and its responsibilities. Collecting software names can otherwise consume an entire week without making your answers clearer.

All exercise data and scenarios below are synthetic. I want examples small enough to inspect on paper and reproduce in a tool. They are preparation material, with no promise that your interviewer will ask exactly these questions.

How I structure a technical answer

I prepare three parts. First, explain the mechanism in one or two sentences. Next, show a small example and predict its result. Finally, identify a limitation that matters to that example. This gives the other person something concrete to examine instead of a list of terms to recognise.

For a customer and order join, I might say: “I need every customer, including those without purchases, so I would use a LEFT JOIN. A customer with two orders will appear twice. Before aggregating, I would check what each row represents and whether the customer reference has unique identifiers.” That answer leaves room for a useful follow-up about duplication or missing matches.

I would avoid opening with five exceptions and a history of the SQL standard. Answer the question first, then develop the part the interviewer wants to explore. You can understand a topic well and still hide that understanding inside a ten-minute monologue. Unfortunately, the microphone does not automatically summarise it for you.

When the task is ambiguous, I clarify the intended output. Should there be one row per customer or per order? Should customers without purchases remain? Which period belongs in the report? Those questions can change the solution. Asking about chart colours at this point probably will not.

I also separate forgotten syntax from an uncertain mechanism. I can explain that I need to filter groups after aggregation and confirm the precise syntax for the database later. If I cannot identify which rows belong in the total, a syntax reference alone will not resolve the problem. I use a similar emphasis on evidence when discussing difficult interview questions.

Excel: lookups and the meaning of a missing value

What limits VLOOKUP?

In a conventional VLOOKUP, the lookup key must be in the first column of the supplied range. The return column is selected by number, so I check that this number still points to the right field after the table layout changes. For a customer identifier, I explicitly request an exact match. Omitting the last argument selects approximate matching. Microsoft’s VLOOKUP documentation describes those rules.

Here is my practice scenario. A reference table contains customer identifiers and regions, and I want to attach the region for customer 42 to an order list. Before choosing a function, I check whether 42 appears once in the reference. If there are two conflicting regions, getting an answer from a formula does not establish which region is correct.

XLOOKUP uses separate lookup and return ranges and defaults to exact matching. Microsoft notes that it is unavailable in Excel 2016 and 2019, so I would also check the recipient’s version. See the XLOOKUP documentation. Replacing the function does not resolve an ambiguous reference table.

What should happen to #N/A?

In an exact lookup, #N/A can indicate a missing match. I first check the key’s existence, its data type and any extra spaces. An identifier stored as text and a similar-looking number may need consistent representation. I keep the original values available so that I can trace where a difference arose.

IFNA handles the specific #N/A error. IFERROR catches a wider range of errors and can therefore conceal a broken reference too. During an interview, I would identify the condition I am handling and explain why the replacement value is meaningful. “Region not found” communicates something different from zero.

Imagine ten customers in a report have no assigned region. Showing an unassigned category and counting those cases helps someone repair the reference data. Removing the rows changes the customer population. Replacing a missing price with zero understates the order value. I can explain those consequences without reciting every Excel function I have encountered.

SQL: predict the result before running it

INNER JOIN and LEFT JOIN with three customers

I run these examples in SQLite. The setup is deliberately small. Each row in orders represents one order, and the amounts are arbitrary units. Taxes, refunds and currency conversion are outside this exercise.

-- example: fixture
CREATE TABLE customers (customer_id INTEGER PRIMARY KEY);
CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount INTEGER,
  status TEXT
);
INSERT INTO customers VALUES (1), (2), (3);
INSERT INTO orders VALUES
  (101, 1, 100, 'completed'),
  (102, 1, 50, 'cancelled'),
  (103, 2, 80, 'completed');

Customer 1 has two orders, customer 2 has one, and customer 3 has none. An ordinary INNER JOIN on customer_id returns three matches. With customers on the left, a LEFT JOIN returns four rows, including customer 3 with null order fields. This follows the SQLite SELECT and join rules.

Both joins can repeat values from the left side when several matches exist. Choosing a LEFT JOIN does not establish a one-to-one relationship. If a customer’s credit limit or annual target appears alongside every order, I cannot simply sum that field. It belongs to a different level of detail.

Where should the status condition go?

I want each customer’s completed order count, including a zero for a customer without any completed orders. I put the status condition in ON to restrict which orders match:

-- example: completed
SELECT c.customer_id, COUNT(o.order_id) AS completed_orders
FROM customers AS c
LEFT JOIN orders AS o
  ON c.customer_id = o.customer_id
 AND o.status = 'completed'
GROUP BY c.customer_id
ORDER BY c.customer_id;

The results are (1, 1), (2, 1) and (3, 0). Moving the status condition into WHERE would remove customer 3. I would also test a customer with only cancelled orders. That case exposes the mistake even in a dataset where every customer has purchased something.

I use COUNT(o.order_id) because it counts non-null order identifiers. COUNT(*) would also count the preserved row for the customer without a match. The SQLite aggregate documentation explains that distinction. My article on SQL join types develops the same reasoning further.

WHERE, HAVING and UNION

WHERE selects the input rows before grouping, while HAVING applies a condition to groups. Here I select completed orders first, then keep customers whose completed order amount totals at least 90:

-- example: having
SELECT customer_id, SUM(amount) AS total_amount
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING SUM(amount) >= 90
ORDER BY customer_id;

The result is (1, 100). The cancelled amount of 50 is excluded, and customer 2’s total of 80 falls below the threshold. Filtering individual amounts instead of the customer total would answer a different question. Try adding a second completed order worth 20 for customer 2. Their total becomes 100 even though neither order individually reaches 90.

For UNION, I explain how result sets are combined. UNION ALL retains duplicates, while UNION removes identical result rows. Two orders worth 100 can represent distinct events. If I select only the amount and use UNION, I can collapse them into one row. The “stacking tables” analogy therefore needs a qualification about duplicates and the number and meaning of columns. SQLite also uses dynamic typing; another database may require explicit type conversions.

Power BI: explain where a measure’s number comes from

A measure versus a calculated column

I start with a scenario: an Import model containing orders. A calculated column might assign an order-size category to each row. Its values are evaluated when the model recalculates, including during refresh. A measure returns a value in the current filter context, such as the order total for a selected customer. Microsoft’s calculation guidance explains this distinction.

For our sample, the total of all amounts is 230. Restricting the status to completed should produce 180. Selecting customer 1 as well should produce 100. I would write those three expected values down before building the report. They help me check whether the model responds correctly instead of trusting a professional-looking card.

A category column can still be used for filtering. However, a value in that individual column row does not recalculate just because a reader selects a slicer. Conversely, a measure’s expression may deliberately remove some filters. When someone says “the number stays the same,” I ask about the expression, relationships and filter scope before diagnosing the report from a screenshot.

Relationships, filter direction and slicers

In a simple model, the customer reference has a unique key, while orders can contain several rows per customer. A one-to-many relationship allows a customer selection to filter the orders. I inspect both sides and the active filter propagation path. I would not enable both directions simply because that sounds more comprehensive. Microsoft’s bidirectional relationship guidance covers the uses and costs.

A slicer presents a filtering control on the report canvas. Filters can also apply to a visual, a page or an entire report. Visual interactions and slicer synchronisation affect what the reader sees. The slicer documentation helps distinguish those settings from the calculation itself.

In an interview exercise, I would describe the selected population beside the result. “Sales: 180” without the status or period leaves too much room for interpretation. If you are still learning modelling, the article on Power BI skills provides a broader practice plan.

Tableau: freshness, level of detail and axes

What does a live connection fail to guarantee?

A live connection queries a source, while an extract is a prepared copy of data. Tableau extracts can be refreshed and limited to the required scope. The choice depends on the source, freshness requirements and response time. I would avoid promising that an extract will always be faster for every workload.

For live connections, I also check caching and how the view refreshes. Tableau documents separate data freshness policies. A “Live” label does not guarantee that a browser tab left open for an hour displays the latest source change. The source may itself receive new records with a delay.

Consider a synthetic morning report that needs complete data through the end of yesterday. I would first check when the source finishes loading, then the extract or view refresh. A report refreshed at 7:00 against a source still loading until 7:15 can be beautifully incomplete. This is a more useful answer than declaring one connection type universally superior.

What does LOD control?

Level of Detail expressions specify the granularity of a calculation. FIXED establishes the specified level independently of the view’s dimensions, while INCLUDE and EXCLUDE relate to the view’s detail. I also check filter order relative to LOD: FIXED takes account of context filters, among others, while ordinary dimension filters are applied later.

Here is an example to discuss. Customer A has orders worth 100 and 50, while customer B has one worth 80. Customer totals are 150 and 80. The average of those two totals is 115; the average of the three individual orders is about 76.67. Before writing a LOD expression, I name the unit of observation. If each customer’s total is repeated beside every order and then averaged, customer A receives more weight. The acronym does not remove the need to check the denominator.

Should axes always be synchronised?

Two measures on two axes need clear units and scales. A shared scale may help when comparing revenue with costs. It does not follow that order counts and a return percentage should be mechanically synchronised. Tableau describes the multiple-axis options, but the chart choice still needs a reason connected to the reader’s question.

I would start by considering two simple charts with a shared time axis. If I use Dual Axis, I check whether changing one scale suggests a relationship that the data does not establish. Two lines can appear convincing simply because their ranges have been adjusted to make them move together visually.

Python: data structures and assumptions

A list, a dictionary and a DataFrame

A list provides positional access and can retain repeated values. A dictionary maps unique keys to values; assigning to an existing key replaces its value. Modern Python preserves key insertion order, so describing dictionaries as having no order is too simplistic. The Python data structure documentation explains these properties.

I use a small example to demonstrate the consequence of choosing a structure. These synthetic values represent quantities from two rows belonging to the same order. The code uses standard Python only:

rows = [(101, 2), (101, 3), (102, 1)]
last_value = dict(rows)
totals = {}
for order_id, quantity in rows:
    totals[order_id] = totals.get(order_id, 0) + quantity

assert last_value == {101: 3, 102: 1}
assert totals == {101: 5, 102: 1}
print(totals)

Direct conversion to a dictionary retains the last value, 3, for key 101. The accumulating loop produces 5. Either result could be correct for a different purpose. In an interview, I explain whether I need a sum, the last recorded state or an error when a duplicate appears. With an actual file, I would also clarify event ordering and the meaning of a missing quantity.

A pandas DataFrame is a two-dimensional structure with labelled axes, and columns can have different types. The table analogy is helpful, but I do not transfer every SQL rule automatically. With DataFrame.merge, I can check the expected relationship through validate and inspect row provenance through indicator. Missing keys on both sides may match in pandas, unlike ordinary SQL equality comparisons involving NULL.

How do I choose a tool?

If the data already lives in a database and I need an aggregation, I would consider performing it there. A spreadsheet may be enough for a small exploratory calculation that people need to examine together. Python helps when I want repeatable processing with checks expressed in code. I always ask who will run the result, how frequently and over how much data.

Rewriting a spreadsheet in pandas does not automatically make the workflow scalable. Memory requirements, execution time and failure handling still matter. A tool choice should reduce the reader’s problem rather than merely add another logo to my CV.

A rehearsal you can do today

Choose three questions related to the job description. For each one, prepare a short answer, an example and a case that changes the result. Allow roughly two minutes for an initial answer. This is my suggested exercise, rather than a timing standard used by employers.

Then ask another person to change one assumption. What if a customer has only cancelled orders? What if the reference repeats an identifier? What if the dashboard uses yesterday’s copy? If you are practising alone, write the variation down before running the code. Predicting a result tells you more about your understanding than commenting on a completed table.

Assess four things: a correct mechanism, a concrete example result, a relevant limitation and a clear explanation. Give yourself one point for each condition you meet. Use the score to compare your own rehearsals. It cannot predict a hiring decision or measure every part of an analyst’s competence.

When you get stuck, record the precise gap. “I cannot explain COUNT after a LEFT JOIN” is a more useful learning note than “I am bad at SQL.” Return to the sample, count the rows manually, consult the documentation and try the explanation again the next day. Reproducing the answer after a break helps reveal whether you retained the reasoning.

In an actual interview, I would not invent experience with a tool I have never used. I can explain what I have practised, what I understand and what I would still need to verify. Preparation gives me a basis for that answer. For a wider exercise, see analyst skills tested through one project. For today, choose one question and develop an example whose result you can defend.