Preparation for Technical Data Analytics Interviews

🧱 SQL - Technical Interview Assessment. Measure your readiness for Data Analyst technical interviews by solving questions focused on SQL fundamentals, data manipulation, and business-driven scenarios.

NULL + NOT IN What is the most important issue with the following query? SELECT customer_id FROM customers WHERE customer_id NOT IN ( SELECT customer_id FROM orders );. It always returns all customers who have never ordered. It may return no rows if the subquery contains at least one NULL. NOT IN cannot be used with subqueries. The query will fail because customer_id must be aggregated.

LEFT JOIN trap Consider: SELECT c.customer_id, o.order_id FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.order_date >= '2026-01-01'; What is the most accurate description of this query?. It always preserves customers with no orders. The WHERE condition can eliminate NULL-extended rows, effectively making the result behave like an INNER JOIN for that condition. The query is invalid because LEFT JOIN cannot use WHERE. The query returns only customers with multiple orders.

Window Functions Which statement correctly distinguishes ROW_NUMBER(), RANK(), and DENSE_RANK()?. ROW_NUMBER() assigns the same number to tied rows. RANK() never produces gaps in ranking values. DENSE_RANK() gives tied rows the same rank without gaps in subsequent ranks. RANK() and DENSE_RANK() always produce identical results.

Second-highest value You need the second-highest salary in each department. Employees with the same salary should receive the same rank. Which function is most appropriate?. ROW_NUMBER(). RANK(). DENSE_RANK(). NTILE(2).

5. WHERE vs HAVING Which query correctly returns departments whose average salary is greater than 80,000?. SELECT department_id FROM employees WHERE AVG(salary) > 80000 GROUP BY department_id;. SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary) > 80000;. SELECT department_id FROM employees HAVING AVG(salary) > 80000 WHERE department_id IS NOT NULL;. SELECT department_id FROM employees WHERE salary > 80000 GROUP BY department_id;.

COUNT and NULL What is the difference between the following expressions? COUNT(*) COUNT(customer_id) COUNT(DISTINCT customer_id). They always return the same value. COUNT(*) counts rows, while COUNT(customer_id) ignores NULLs and COUNT(DISTINCT customer_id) also removes duplicate non-NULL values. COUNT(*) ignores NULLs but the other two do not. COUNT(DISTINCT customer_id) counts NULL as a unique customer.

Aggregation after a JOIN You join a customer table to an orders table where each customer can have many orders. You then calculate: SUM(o.amount) What is the primary risk if another one-to-many table is also joined before the aggregation?. SQL automatically removes duplicate rows. The SUM may be inflated because rows can be multiplied by the joins. SUM cannot be used after a JOIN. The database will always reject the query.

LAG() What does the following expression calculate? LAG(sales_amount) OVER ( PARTITION BY customer_id ORDER BY order_date ). The customer's maximum sales amount. The next sales amount for the customer. The previous sales amount for the customer according to order date. The total sales amount for the customer.

Filtering a window function Why is the following generally invalid in SQL? SELECT employee_id, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank FROM employees WHERE salary_rank <= 3;. Window functions cannot contain ORDER BY. Aliases can never be used anywhere in SQL. Window functions are evaluated after the WHERE phase, so their result generally must be filtered in an outer query or CTE. RANK() only works with GROUP BY.

Correlated subquery What makes a subquery "correlated"?. It contains an aggregate function. It references a column from the outer query. It uses a CTE. It contains a JOIN.

EXISTS vs IN Which statement is generally true about EXISTS?. EXISTS always returns the matching column values. EXISTS checks whether the subquery returns at least one row. EXISTS cannot reference columns from the outer query. EXISTS automatically removes duplicates from the outer query.

CASE and NULL What will this expression return when status is NULL? CASE WHEN status = 'Active' THEN 'A' WHEN status <> 'Active' THEN 'I' ELSE 'Unknown' END. 'A'. 'I'. 'Unknown'. NULL.

Three-valued logic Which condition correctly identifies rows where email is NULL?. WHERE email = NULL. WHERE email == NULL. WHERE email IS NULL. WHERE email <> NULL.

Date filtering A column created_at contains timestamps. You need all records created on January 15, 2026, regardless of the time. Which condition is generally the safest?. WHERE created_at = '2026-01-15'. WHERE created_at BETWEEN '2026-01-15' AND '2026-01-15'. WHERE created_at >= '2026-01-15' AND created_at < '2026-01-16'. WHERE CAST(created_at AS DATE) = '2026-01-15'.

GROUP BY behavior Consider: SELECT customer_id, order_date, SUM(amount) FROM orders GROUP BY customer_id; What is the issue?. SUM cannot be used with GROUP BY. order_date is neither aggregated nor included in the GROUP BY. customer_id cannot be grouped. There is no issue.

CTE purpose What is the primary purpose of a Common Table Expression (CTE)?. Permanently store a new table in the database. Create a temporary named result set that can be referenced within a query. Automatically improve query performance. Replace all indexes on the underlying tables.

UNION vs UNION ALL What is the key difference between UNION and UNION ALL?. UNION ALL removes duplicates while UNION preserves them. UNION removes duplicate rows while UNION ALL retains them. Both always remove duplicates. UNION can combine different numbers of columns.

Duplicate customers You run: SELECT c.customer_id FROM customers c JOIN orders o ON c.customer_id = o.customer_id; A customer has 15 orders. How many times can that customer's customer_id appear?. Exactly once. Exactly twice. Up to 15 times, assuming each order produces one matching row. Never more than the number of customers.

Removing duplicate results You need each customer ID only once from a query that may produce multiple rows per customer. Which is the most direct solution?. GROUP BY customer_id or SELECT DISTINCT customer_id. ORDER BY customer_id. HAVING customer_id. LIMIT 1.

Rolling average You need to calculate a 7-day rolling average of daily revenue. Which SQL concept is most directly relevant?. Recursive DELETE. Window functions with an appropriate window frame. CROSS JOIN only. UNION ALL only.

Top N per group You need the top 3 highest-paid employees in each department. Which approach is generally most appropriate?. ORDER BY salary DESC LIMIT 3. GROUP BY department_id with MAX(salary). A window function such as ROW_NUMBER() or DENSE_RANK() partitioned by department. DISTINCT department_id, salary.

WHERE before GROUP BY Which statement about SQL's logical query processing order is generally correct?. GROUP BY is logically processed before WHERE. WHERE is logically processed before GROUP BY. SELECT is always logically processed before WHERE. HAVING is logically processed before FROM.

LEFT JOIN condition placement You want all customers, but only their orders from 2026. Which approach preserves customers who have no 2026 orders?. SELECT * FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.order_date >= '2026-01-01';. SELECT * FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.order_date >= '2026-01-01';. SELECT * FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id WHERE o.order_date >= '2026-01-01';. Both A and B always produce exactly the same result.

Query logic — interview trap Consider: SELECT customer_id FROM orders GROUP BY customer_id HAVING COUNT(*) > 1 AND SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) = 0; What does this query return?. Customers with exactly one order that was not cancelled. Customers with more than one order and no cancelled orders. Customers with more than one cancelled order. Customers whose total order amount is greater than zero.

Advanced — consecutive activity You need to identify customers who made purchases on at least 3 consecutive calendar days. Which approach is most appropriate?. Use COUNT(*) grouped only by customer. Use LAG()/window functions or a gaps-and-islands technique to identify consecutive dates. Use MAX(purchase_date) grouped by customer. Use DISTINCT customer_id and ORDER BY purchase_date.

🧹Data Cleaning - Technical Interview Assessment. Assess your readiness for Data Analyst technical interviews by solving questions related to data quality, validation techniques, missing values, duplicate detection, outlier analysis, and data consistency.

Missing values — business context A dataset contains 5% missing values in customer_income. The missing records are disproportionately concentrated among customers from one geographic region. What is the best first step?. Replace all missing values with the global median. Remove all rows containing missing income values. Investigate whether the missingness is systematic and understand why income is missing before choosing a treatment. Replace missing values with zero because income was not reported.

Duplicate records You discover that 2% of rows are exact duplicates. However, some customers legitimately made two identical purchases on the same day for the same amount. What should you do?. Delete every duplicate row because duplicates always indicate bad data. Keep all rows because deleting duplicates is always dangerous. Determine whether the rows represent duplicate records or legitimate transactions using an appropriate business key or transaction identifier. Keep only the first occurrence of each customer.

Outliers A dataset contains a transaction_amount column where most values are between $10 and $500, but a few transactions are above $100,000. What is the most appropriate approach?. Automatically remove all values above the 99th percentile. Replace all values above $500 with the median. Investigate whether the extreme values are valid transactions, data-entry errors, or a different type of transaction before modifying them. Remove all values outside 1.5 × IQR without further investigation.

Mean vs median imputation When is median imputation generally preferable to mean imputation?. When the variable is categorical. When the numerical variable is strongly skewed or contains influential outliers. When there are no missing values. When the variable follows a perfectly symmetric distribution.

Impossible values A customer_age column contains the following values: 21, 34, 47, -5, 29, 999, 42 What is the best interpretation?. -5 and 999 should automatically be replaced with the median. They are valid outliers and should always remain unchanged. They are likely invalid values and should be investigated using domain rules before deciding whether to correct, remove, or mark them as missing. All ages should be standardized using z-scores.

Date inconsistencies A dataset contains dates in formats such as: 01/02/2026 2026-02-01 Feb 1, 2026 What is the most important risk when standardizing these values?. Dates cannot be converted into a common format. Ambiguous formats such as 01/02/2026 can be interpreted differently depending on locale. Converting dates always removes the time component. ISO date formats cannot be stored in databases.

Standardizing categorical values A country column contains: USA U.S.A. United States US usa What is the best cleaning strategy?. Convert everything to lowercase and assume the values are equivalent. Replace every value manually without documenting the transformation. Define a controlled mapping/reference standard and map equivalent representations to a canonical value. Remove all rows containing inconsistent country names.

Missing vs zero A company's dataset contains a discount column. Some rows contain NULL, while others contain 0. Which statement is most accurate?. NULL and 0 should always be treated as the same value. NULL may mean unknown/not recorded, while 0 may explicitly mean no discount was applied. All NULL discounts should automatically become zero. Zero should always be interpreted as missing data.

Data leakage You are preparing a dataset to train a model that predicts whether a customer will churn. Which situation represents potential data leakage?. Using the customer's age at the time the prediction is made. Using historical purchases made before the prediction date. Using a variable that was generated after the customer had already churned. Removing rows with invalid customer IDs.

Referential integrity You have an orders dataset containing customer_id. Some IDs do not exist in the customer master table. What should you investigate first?. Automatically delete all orders with unmatched customer IDs. Determine whether the unmatched IDs are caused by missing customer records, timing issues, incorrect IDs, or legitimate historical records. Replace every unmatched ID with the most common customer ID. Replace every unmatched ID with the most common customer ID.

Data type problems A revenue column is stored as strings and contains values such as: "$1,250.00" "$850" "N/A" "unknown" What is the most robust cleaning approach?. Convert the entire column directly to numeric and ignore conversion errors. Remove every row containing non-numeric characters. Define how valid currency strings and non-numeric placeholders should be handled, standardize the values, then convert the column to an appropriate numeric type. Replace every non-numeric value with zero.

Sampling bias during cleaning You are cleaning a dataset containing customer complaints. You notice that 30% of complaints have missing demographic information. Why could simply removing those rows be problematic?. Removing rows never affects analysis. Missing demographic information may be associated with particular customer groups, creating selection bias if those rows are removed. Demographic variables can never contain missing values. Removing rows automatically creates duplicate records.

Duplicate detection — subtle case You are asked to find duplicate customer records. Two records have the same name and email address but different customer_id values. What is the best conclusion?. They are definitely duplicates and one should immediately be deleted. They are definitely two different customers because their IDs differ. They are potential duplicates and should be investigated using additional identifying attributes and business rules. Customer IDs should be ignored during duplicate detection.

Cleaning order Which sequence is generally the most defensible when cleaning a new dataset?. Delete outliers → fill missing values → inspect the data → define business rules. Inspect/profile the data → understand business context → define cleaning rules → apply transformations → validate the results. Fill all missing values → remove duplicates → standardize categories → inspect the result. Standardize everything automatically → remove all unusual values → calculate statistics.

Validation After Cleaning You have cleaned a dataset by removing duplicates, standardizing categories, converting data types, and handling missing values. What is the most important next step?. Assume the dataset is clean because the transformations completed successfully. Export the dataset immediately to Excel for reporting. Validate the cleaned dataset against expected business rules, distributions, row counts, uniqueness constraints, and key metrics. Remove another round of outliers to make the dataset more consistent.

📊 Data Visualization - Technical Interview Assessment. Assess your ability to communicate insights through effective visualizations by solving questions focused on chart selection, dashboard storytelling, KPI presentation, visual design principles, and business communication.

Choosing the right chart You need to compare the revenue of 12 different product categories for the same month. The primary goal is to allow the user to quickly identify which categories generated the most and least revenue. Which visualization is generally the most appropriate?. Pie chart. Sorted horizontal bar chart. Line chart. Scatter plot.

Time-series visualization You are showing monthly revenue over a three-year period. The main goal is to identify trends, seasonality, and changes over time. Which visualization is generally the best choice?. Pie chart. Line chart. Treemap. Horizontal bar chart with one bar per year.

Misleading axis A bar chart compares sales between two regions. Region A has sales of $980,000 and Region B has sales of $1,000,000. The y-axis starts at $950,000, making the difference appear extremely large. What is the main problem?. Bar charts cannot be used for financial data. The truncated axis can visually exaggerate the difference between the values. The chart should use a logarithmic scale. The values should always be displayed as percentages.

Correlation vs causation A dashboard shows that customers who receive more marketing emails tend to have higher revenue. Which conclusion is most appropriate?. Sending more emails causes customers to spend more. Higher revenue causes customers to receive more emails. There is an association between email frequency and revenue, but the visualization alone does not establish causation. The relationship is invalid unless the correlation coefficient is exactly 1.

Dual-axis charts You want to visualize monthly revenue and customer satisfaction score on the same chart. Revenue ranges from $0 to $10 million, while satisfaction ranges from 0 to 100. What is the main risk of using a dual-axis chart?. Dual-axis charts cannot contain two measures. Different scales can create the impression of a stronger or weaker relationship depending on how the axes are configured. Both metrics must use identical units. Satisfaction scores cannot be visualized over time.

Pie chart trap A stakeholder asks for a pie chart showing the percentage of revenue generated by 18 different product categories. What is the strongest objection?. Pie charts can only represent percentages below 50%. Pie charts are generally poor for comparing many categories because differences between similar-sized slices are difficult to judge. Pie charts cannot represent revenue. Pie charts should only be used for time-series data.

KPI design A business dashboard displays: Total Revenue Total Orders Average Order Value Customer Count The CEO asks: "Which KPI should I focus on to understand whether the business is becoming more profitable?" What is the best response?. Total Revenue, because revenue always represents profitability. Total Orders, because more orders always mean more profit. None of these necessarily measures profitability; a profit or margin metric would be more appropriate. Customer Count, because more customers always produce higher profit.

Average vs distribution Two regions both have an average customer lifetime value of $500. You want to determine whether the customer populations are actually similar. What visualization would provide the most useful additional information?. A second KPI showing the same average. A distribution visualization such as a histogram or box plot. A pie chart of the two averages. A single number showing the global average.

Outliers in visualization You create a scatter plot showing customer income versus spending. A small number of customers have extremely high income and spending. What is the potential consequence of these observations?. They automatically indicate data errors. They can compress the majority of observations visually, making the main relationship harder to see. They should always be removed before visualization. Scatter plots cannot contain outliers.

Data aggregation You have transaction-level data containing millions of rows. A dashboard displays daily revenue. What is generally the most appropriate approach?. Display every transaction as a separate visual mark. Aggregate the data to the appropriate daily level while preserving the ability to drill down when needed. Randomly sample 1% of transactions. Remove transactions with small values.

Dashboard clutter A dashboard contains 17 charts, 12 KPI cards, 8 slicers, and several decorative elements. The stakeholder says: "Everything is important." What is the best analytical response?. Keep everything because more information always improves a dashboard. Remove all charts and keep only KPI cards. Prioritize visuals based on the decisions and questions the dashboard is intended to support. Make every visual the same size.

Color usage A chart contains 15 product categories, each represented by a different color. What is the main concern?. Using more than five colors is technically invalid. Too many distinct colors can increase cognitive load and make comparisons harder. Colors should never be used in business dashboards. Every category must have a unique color to avoid ambiguity.

Color as encoding You are visualizing sales by region and want to highlight regions that are below their quarterly target. Which approach is generally strongest?. Use random colors for each region. Use color intentionally to distinguish performance relative to target, while maintaining a consistent meaning throughout the dashboard. Use as many colors as possible to maximize visual contrast. Use 3D effects to make underperforming regions more noticeable.

Choosing a metric A product manager wants to know whether a new feature improved user engagement. Which metric would be the most informative?. Number of registered users, regardless of feature usage. A clearly defined engagement metric measured before and after the feature release, ideally with an appropriate comparison group or experimental design. Total website traffic only. The average age of users.

Percentages vs absolute values Region A generates $10 million in revenue and Region B generates $2 million. However, Region A has 1 million customers while Region B has 50,000. A stakeholder wants to compare "revenue performance" between regions. What is the best analytical response?. Always compare absolute revenue because percentages are misleading. Compare only customer counts. Consider both absolute revenue and normalized metrics such as revenue per customer, depending on the business question. Use a pie chart because it automatically normalizes the values.

Small multiples You need to compare monthly sales trends across 20 different stores. Putting all 20 stores on a single line chart makes the visualization extremely difficult to read. Which technique could be especially useful?. Use small multiples with consistent scales and formatting. Use a 3D pie chart. Use one different y-axis for every store. Remove all stores except the highest-performing one.

Baseline manipulation Two line charts show the same metric over the same period. One chart uses a y-axis from 0 to 100, while the other uses a y-axis from 48 to 60. The second chart makes the change appear much more dramatic. What should an analyst consider?. The second chart is always more accurate. The first chart is always more accurate. The choice of axis range affects visual perception and should reflect the analytical purpose without misleading the audience. Axis ranges have no effect on interpretation.

Dashboard filtering trap A dashboard shows "Average Revenue per Customer." A user applies a filter for a single high-value customer and sees the KPI increase dramatically. What is the most important consideration?. The dashboard is automatically incorrect. The KPI may be mathematically correct but its interpretation depends on the active filter context and sample size. Average metrics should never be used in dashboards. Filters should always be disabled for KPI cards.

Storytelling A dashboard is intended for executives who have approximately two minutes to understand why quarterly revenue declined. Which design is most effective?. Show every available metric and allow executives to explore the data themselves. Lead with the key outcome, provide the most relevant drivers and comparisons, and allow deeper exploration if needed. Use as many visualizations as possible to demonstrate analytical sophistication. Present raw transaction-level data first.

Visualization and statistical uncertainty You are comparing conversion rates between two groups. Group A has a conversion rate of 12%, while Group B has a conversion rate of 14%. Which statement is most appropriate?. Group B definitely performs better because 14% is greater than 12%. The 2-percentage-point difference may or may not be statistically or practically meaningful; sample size and uncertainty should be considered. The difference is exactly 16.7%. Conversion rates should always be displayed as absolute counts instead.

🧮 Excel - Technical Interview Assessment. Evaluate your ability to analyze, transform, and summarize data using Excel through interview-style questions based on real business scenarios.

XLOOKUP — Exact Matching You use the following formula: =XLOOKUP(A2, Customers[Customer_ID], Customers[Revenue]) What is the default match behavior of XLOOKUP?. Approximate match, assuming the lookup column is sorted. Exact match. Wildcard match only. It returns the first value greater than or equal to the lookup value.

XLOOKUP with duplicates A customer table contains multiple rows for the same Customer_ID because each row represents an order. You use: =XLOOKUP(A2, Orders[Customer_ID], Orders[Amount]) What will happen if the customer has multiple matching orders?. XLOOKUP automatically sums all matching amounts. XLOOKUP returns the first matching result by default. XLOOKUP returns the largest matching amount. XLOOKUP returns an error because duplicates are not allowed.

SUMIFS — multiple conditions You need to calculate total sales where: Region = "North" Product = "Laptop" Salesperson = "John" Which formula is most appropriate?. =SUMIF(Sales[Region],"North",Sales[Amount]). =SUMIFS(Sales[Amount],Sales[Region],"North",Sales[Product],"Laptop",Sales[Salesperson],"John"). =SUM(Sales[Amount],Sales[Region],"North",Sales[Product],"Laptop"). =COUNTIFS(Sales[Region],"North",Sales[Product],"Laptop",Sales[Salesperson],"John").

COUNTIFS and blanks You want to count customers whose Status is not "Cancelled". Which formula is most appropriate?. =COUNTIF(StatusRange,"<>Cancelled"). =COUNTIF(StatusRange,"Not Cancelled"). =COUNTIF(StatusRange,"!=Cancelled"). =COUNTIF(StatusRange,"<>").

INDEX/MATCH vs XLOOKUP Which is a valid reason an analyst might still use INDEX/MATCH even when XLOOKUP is available?. INDEX/MATCH can perform lookups without requiring a lookup value. Compatibility with older Excel versions or existing models. XLOOKUP cannot perform exact matches. INDEX/MATCH automatically handles duplicate values better.

IFERROR — hidden problems Consider: =IFERROR(A2/B2,0) What is a potential analytical problem with this formula?. IFERROR cannot handle division errors. It may hide genuine data-quality or logic problems by converting errors into zero. It always causes Excel to crash when B2 is zero. It converts all numbers into text.

Dates — TODAY() You create: =TODAY()-A2 where A2 contains a customer's signup date. What does the formula generally calculate?. The customer's signup year. The number of days between the signup date and today. The customer's age. The number of months since signup, regardless of day.

Month comparison You need to determine whether two dates belong to the same calendar month and year. Which approach is generally the most robust?. Compare only MONTH(date1) and MONTH(date2). Compare only DAY(date1) and DAY(date2). Compare both month and year, or normalize both dates to a month-level representation. Compare the displayed text values of the dates.

Dynamic arrays What happens when you enter: =FILTER(A2:D100, D2:D100="Active") in a modern version of Excel?. It returns only the first matching row. It returns all matching rows and can spill the results into adjacent cells. It permanently deletes inactive records. It converts the range into a PivotTable.

Spill Errors You enter a dynamic array formula, but Excel returns: #SPILL! What is the most likely explanation?. The formula contains a syntax error. One or more cells in the intended spill range are blocking the output. The workbook contains too many worksheets. Excel cannot perform calculations on arrays.

PivotTable — aggregation trap A sales dataset contains one row per transaction. You create a PivotTable with: Salesperson in Rows Transaction_ID in Values Excel summarizes Transaction_ID as Count. What does this metric represent?. Total transaction revenue. Number of transactions associated with each salesperson. Average transaction value. Number of unique customers.

PivotTable — Distinct Customers You need to calculate the number of unique customers per region using a PivotTable. What is the key requirement?. Use Sum on the Customer ID field. Use Count on Customer ID without considering duplicates. Use a Distinct Count, typically available when the data is added to the Data Model. Sort Customer ID alphabetically.

Absolute vs Relative References Consider: =B2*$F$1 You copy this formula from row 2 to row 10. What happens?. Both references change. Neither reference changes. B2 changes relative to the new row, while $F$1 remains fixed. $F$1 changes but B2 remains fixed.

Performance — large datasets An Excel workbook contains hundreds of thousands of rows and thousands of formulas using repeated VLOOKUP operations against the same large range. The workbook is becoming slow. Which approach could improve performance and maintainability?. Add more formatting and conditional formatting. Replace all formulas with manually typed values without documenting the process. Consider structured tables, efficient lookup strategies, Power Query/Data Model, or reducing repeated calculations. Increase the font size of the worksheet.

Lookup logic You need to return the latest transaction amount for a customer, where the customer can have multiple transactions. Which statement is most accurate?. A standard XLOOKUP using Customer ID alone automatically returns the latest transaction. You need logic that identifies the latest transaction first, potentially using MAXIFS, FILTER, SORT, XLOOKUP, or a combination depending on the Excel version and data structure. VLOOKUP automatically returns the transaction with the latest date. INDEX/MATCH cannot be used for this problem.