Data analyst role-specific interview questions

As asked

Without writing it on a whiteboard, walk me through how you would structure a SQL query to return the second highest salary within each department from an employees table.

Sample answer outline

Explain a window function approach: DENSE_RANK or ROW_NUMBER partitioned by department and ordered by salary descending, then filter to rank equal to two in an outer query. Cover why DENSE_RANK handles ties differently from ROW_NUMBER, and what happens when a department has only one salary. Mention that a correlated subquery is the older alternative but reads worse and scales poorly.

Expect these follow-ups

• How does your answer change if two people tie for the top salary?
• Why pick DENSE_RANK over ROW_NUMBER here?
• What if the department has fewer than two employees?

As asked

A stakeholder says a report is missing customers. Explain the difference between an inner join and a left join, and how the wrong choice could drop those customers.

Sample answer outline

An inner join returns only rows with a match on both sides, so customers with no orders silently disappear. A left join keeps every customer and fills order columns with nulls where there is no match. Tie it to the business case: a revenue report joined on orders with an inner join will undercount the customer base. Mention checking row counts before and after a join as a sanity check and handling the nulls deliberately.

Expect these follow-ups

• How would you spot that a join silently dropped rows?
• When would a full outer join actually be the right call?
• What does a fan-out from a one to many join do to your sums?

As asked

You are handed a CSV export of 200,000 sales records that you have never seen before. Walk me through your process to get it analysis ready.

Sample answer outline

Start with profiling: row count, column types, null rates, distinct values, and ranges per column. Check for duplicates, inconsistent categories such as UK versus United Kingdom, mixed date formats, currency and unit consistency, and impossible values like negative quantities. Decide deliberately how to treat missing values rather than dropping silently. Document every transformation so the cleaning is reproducible and reviewable.

Expect these follow-ups

• How do you decide whether to drop or impute a missing value?
• What would make you go back to the data owner before analyzing?
• How do you make this cleaning reproducible next month?

As asked

You are about to send a headline figure to the leadership team. What checks do you run to be confident the number is right?

Sample answer outline

Triangulate the figure against a second source or a known total, sanity check the order of magnitude, and reconcile against last period to explain any large movement. Re-run the query on a small known slice you can hand verify, confirm the date range and filters match the question asked, and check for nulls or excluded rows that quietly change the denominator. Have a peer review the logic for anything high stakes.

Expect these follow-ups

• What is a number you got wrong once and how did you catch it?
• How do you reconcile two systems that disagree by a few percent?
• How do you balance speed against this level of rigor?

As asked

You are asked to build an executive dashboard in Power BI or Tableau. How do you decide what goes on it and how it is laid out?

Sample answer outline

Start from the decisions the executive makes, not the data available, and pick a small set of metrics tied to those decisions. Put the headline numbers and trend at the top, give context with comparisons to target or prior period, and avoid clutter and vanity charts. Define every metric clearly, set a refresh cadence, and confirm with the audience that the dashboard answers their real questions before polishing it.

Expect these follow-ups

• How do you stop a dashboard becoming a wall of every metric?
• How do you choose the right chart for a given metric?
• How do you handle two stakeholders who define a metric differently?

As asked

Your weekly revenue query suddenly reports roughly double the real figure after a join was added. How would you diagnose and fix it?

Sample answer outline

Suspect a fan-out: a one to many join multiplied the revenue rows, so SUM counts each order several times. Diagnose by checking the grain of each table, counting rows before and after the join, and looking for duplicate keys. Fix by aggregating the many side to the correct grain first, or by using COUNT DISTINCT and pre-aggregated subqueries. Stress validating the grain of every table before joining.

Expect these follow-ups

• How do you confirm which table introduced the duplicates?
• When is SELECT DISTINCT a band aid rather than a fix?
• How would you write a test so this regression is caught next time?

As asked

Stakeholders complain your Power BI report takes 40 seconds to load. How would you diagnose and speed it up?

Sample answer outline

Separate query time from rendering: too many visuals, high cardinality slicers, and row level calculations all hurt. Push aggregation upstream into the model or warehouse, use an extract or import mode over live queries where freshness allows, reduce the columns and rows pulled, and simplify DAX or calculated fields that scan the whole table. Measure before and after with the performance analyzer rather than guessing.

Expect these follow-ups

• When would you pre-aggregate in the warehouse instead of in the tool?
• How does cardinality of a filter affect performance?
• What is the trade off between live and imported data?

As asked

You inherit a finance spreadsheet that nobody trusts because numbers keep breaking. How would you rebuild it to be reliable and maintainable?

Sample answer outline

Separate inputs, calculations, and outputs onto distinct sheets, avoid hard coded numbers buried in formulas, and use named ranges or structured tables so logic is readable. Replace fragile nested lookups with XLOOKUP or INDEX MATCH, add input validation, and build in checksum cells that flag when totals stop reconciling. Document assumptions and version the file so a broken change can be traced.

Expect these follow-ups

• Why prefer XLOOKUP or INDEX MATCH over VLOOKUP?
• How do you build a self checking spreadsheet?
• When does a problem outgrow a spreadsheet entirely?

As asked

A recurring analysis currently lives in a pivot table that you rebuild by hand every Monday. How do you decide whether to keep it in Sheets or move it to SQL?

Sample answer outline

Weigh data volume, the need for repeatability, the number of consumers, and how often the source changes. A one off exploration is fine in a spreadsheet, but a weekly recurring report with growing data and several readers should move to SQL feeding a scheduled dashboard. Mention the cost of manual rebuild as a source of error and the value of automating the toil away.

Expect these follow-ups

• What signs tell you a spreadsheet has outgrown itself?
• How would you migrate the Monday routine without breaking trust?
• How do you keep the SQL version as approachable as the pivot was?

As asked

Leadership wants everyone to self serve in Looker rather than queue for your reports. How do you enable that without metrics drifting out of control?

Sample answer outline

Centralise metric definitions in a governed semantic layer such as LookML so a metric means the same thing everywhere, and expose curated, well named fields rather than raw tables. Provide certified dashboards, documentation, and guardrails on expensive queries. Train users on the common pitfalls and keep a feedback loop so genuine gaps come back to the analytics team rather than spawning shadow spreadsheets.

Expect these follow-ups

• How do you stop two teams inventing two definitions of revenue?
• What governance keeps self serve from producing wrong numbers?
• How do you decide which fields to expose and which to hide?