DA0-002 Practice Test 21 — 15 Questions

Question 1

In a table 'employee_hierarchy' with columns 'employee_id', 'manager_id', and 'employee_name', an analyst needs to generate a list of all employees under a specific manager, including multiple levels of subordinates. Which SQL construct is most appropriate for querying this hierarchical data efficiently?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Recursive CTE. Recursive CTEs are designed to handle hierarchical data by repeatedly joining a CTE to itself until all levels are included.

Answer

Window function with PARTITION BY

Answer

Subquery in WHERE clause

Answer

Self-JOIN with WHERE clause

Question 2

A data analyst at a logistics company is analyzing delivery times for three different shipping carriers. The analyst wants to determine whether the mean delivery time differs across carriers. The data are normally distributed, and the variances across carriers are assumed equal. Which statistical test should the analyst use?

Accepted Answer

One-way ANOVA. One-way ANOVA is designed to compare means across three or more independent groups under assumptions of normality and equal variances. The scenario involves three carriers, continuous delivery times, and the goal of detecting mean differences, making ANOVA the correct choice. Other tests either handle only two groups or require categorical data, which does not fit the analysis objective.

Answer

Independent two-sample t-test

Answer

Paired t-test

Answer

Chi-square test of independence

Question 3

A data analyst is working with a dataset containing customer ages. The ages range from 18 to 90, but the analyst notices that the distribution is heavily skewed to the right. To better understand the central tendency, the analyst decides to calculate a measure that is resistant to outliers. Which measure of central tendency should the analyst use?

Accepted Answer

Median. The median is the appropriate measure because it is resistant to outliers and skewness, providing a better representation of the central value in a right-skewed distribution. The mean would be inflated by high ages, while the mode and range do not measure central tendency effectively for continuous skewed data.

Answer

Mean

Answer

Mode

Answer

Range

Question 4

A data analyst is evaluating a classification model that predicts whether customers will churn. The dataset is highly imbalanced, with only 5% of customers churning. The analyst wants to choose a metric that focuses on the model's ability to correctly identify actual churners. Which metric should the analyst prioritize?

Accepted Answer

Recall. Recall is the metric that quantifies the model's ability to find all relevant instances of the positive class. Since the analyst wants to correctly identify actual churners, recall is the most appropriate metric. Accuracy and specificity focus on the negative class or overall correctness, and precision does not capture missed positives.

Answer

Specificity

Answer

Precision

Answer

Accuracy

Question 5

A data analyst needs to retrieve the top 5 most expensive products from a 'products' table sorted by price descending. Which TWO SQL clauses are required to achieve this? (Select TWO).

Accepted Answer

ORDER BY price DESC. ORDER BY price DESC (C) is required because it sorts the result set by the price column in descending order, placing the most expensive products first. LIMIT 5 (E) is required because it restricts the result set to only the first five rows returned after sorting, giving the top 5 most expensive products. Together, ORDER BY price DESC followed by LIMIT 5 produce exactly the requested output. The other options do not belong: HAVING COUNT(*) > 1 (A) filters grouped aggregate results, WHERE price > 100 (B) applies a fixed numeric filter rather than selecting the top 5, and GROUP BY price (D) aggregates rows by price instead of simply sorting and limiting them.

Answer

HAVING COUNT(*) > 1

Answer

WHERE price > 100

Answer

GROUP BY price

Question 6

A data analyst creates a dashboard for executives to monitor quarterly sales. Which best practice ensures the dashboard is effective?

Accepted Answer

Place the most important metric in the top-left corner with simple charts.. Placing the most important metric in the top-left corner leverages the natural reading pattern (left-to-right, top-to-bottom) to immediately draw the executive's attention to the key insight. Using simple charts (e.g., bar or line charts) reduces cognitive load, enabling rapid comprehension of quarterly sales trends without distracting details. This aligns with dashboard design principles that prioritize clarity and actionability over data density.

Answer

Use a dark background with bright colors for contrast.

Answer

Include raw data tables for detailed analysis.

Answer

Use as many charts as possible to show all data.

Question 7

A data analyst is compiling a quarterly report for a retail chain and needs to show how sales revenue is distributed across five product categories. The analyst wants a single visualization that makes it easy to compare each category's share of total revenue and also includes a separate small panel displaying total revenue for the quarter. Which visualization approach best meets these requirements?

Accepted Answer

A dashboard with a pie chart showing category shares and a separate KPI card displaying total revenue.. The requirement is to compare each category's share of total revenue and to have a separate small panel for total revenue. A dashboard with a pie chart and a KPI card meets both: the pie chart shows proportions, and the KPI card is a distinct panel for the total. Other options either embed the total in the chart or use less effective visuals for comparing shares.

Answer

A stacked bar chart showing revenue by category, with the total revenue represented by the height of the entire stack.

Answer

A donut chart with each category's share, plus a small table in a separate panel listing the total revenue.

Answer

A pie chart showing each category's percentage of total revenue, with the total revenue displayed as the chart title.

Question 8

A data analyst at a healthcare organization is analyzing patient readmission rates. The analyst has a dataset with patient demographics, diagnosis codes, and length of stay. Before performing any statistical modeling, the analyst must address data quality issues. Which TWO of the following actions are most appropriate for ensuring the dataset is ready for analysis? (Choose two.)

Accepted Answer

Identify and remove duplicate patient records based on a unique patient identifier and admission date.. Ensuring data quality involves identifying and correcting issues that could bias results. Removing duplicate patient records prevents overcounting admissions, and standardizing diagnosis codes ensures consistency for analysis. Mean imputation can distort data, binning numerical variables is not a quality fix, and listwise deletion can introduce bias and reduce sample size. These two actions directly address common data quality problems in healthcare datasets.

Answer

Remove all records with any missing values to ensure a complete dataset.

Answer

Convert all numerical columns to categorical bins to simplify the analysis.

Answer

Impute missing values in the length of stay column using the mean length of stay for all patients.

Question 9

A financial analytics team is building a data warehouse to support complex analytical queries on historical stock trades. The data volume is in terabytes, and queries frequently join multiple large tables and perform aggregations. The team needs a storage model that minimizes query latency for these read-heavy analytical workloads. Which data modeling approach is most appropriate?

Accepted Answer

Dimensional star schema. A dimensional star schema is designed for analytical workloads, using fact and dimension tables to minimize joins and optimize aggregations. It supports columnar storage and partitioning, which are critical for terabyte-scale read-heavy queries. Normalized OLTP, EAV, and flat files each introduce performance bottlenecks or lack the necessary optimizations for complex analytical queries on historical stock trades.

Answer

Highly normalized OLTP schema

Answer

Entity-attribute-value (EAV) model

Answer

Flat file with no indexing

Question 10

An analyst is profiling a table of laboratory test results. The ResultValue column stores numeric readings, but a subset of rows contains the literal text "N/A" in that column, and a separate ResultUnit column records units such as mg/dL or mmol/L. The analyst must compare average ResultValue across two hospital sites. Which data issue most directly prevents a valid comparison, and what is the appropriate first step?

Accepted Answer

The "N/A" text forces the column to be interpreted as text, so numeric averages silently ignore or misorder values, and the analyst must convert or exclude those values before averaging. A numeric column polluted with text values like "N/A" typically gets coerced to a string type, which breaks arithmetic aggregation and can cause silent misordering or failed averages. The analyst must first isolate the non-numeric rows and convert or exclude them so the column is genuinely numeric, then compute the average for each site. Only after that remediation does the two-site comparison become valid.

Answer

The average must be computed as a median because laboratory results are always skewed, so the analyst should replace ResultValue with a rank-based statistic

Answer

The presence of "N/A" text in a numeric column makes the column string-typed, so the analyst must drop the entire ResultValue column before comparing sites

Answer

The ResultUnit column contains multiple unit systems, so the analyst must convert all readings to a single unit before averaging

Question 11

A data engineer is profiling a dataset of online orders. The order_id column contains a unique value for every row, but the analyst notices that a join to the customer table unexpectedly returns fewer rows than the orders table. After investigation, the engineer finds that some customer_id values in the orders table do not exist in the customer table. Which data quality dimension is primarily violated by the customer_id values?

Accepted Answer

Referential integrity. When foreign key values in the orders table fail to match primary keys in the customer table, the relationship between the tables is broken, which is a referential integrity violation. That is why the join loses rows. Accuracy, completeness, and timeliness describe other properties of data and do not capture a missing parent record for a valid-looking child key.

Answer

Timeliness

Answer

Accuracy

Answer

Completeness

Question 12

During a presentation, a stakeholder questions the validity of a correlation found. What is the best response?

Accepted Answer

Correlation does not imply causation, but we can perform further analysis.. It directly addresses the stakeholder's concern about validity by acknowledging the fundamental statistical principle that correlation does not imply causation. It then proposes a constructive next step—further analysis—which aligns with best practices in data communication, where validating insights requires additional testing (e.g., controlled experiments or causal inference methods). This response demonstrates both technical honesty and a commitment to rigorous data-driven decision-making.

Answer

We can accept the correlation as true.

Answer

We used a large sample so it's valid.

Answer

The p-value is low, so it's significant.

Question 13

A data analyst is compiling a monthly sales dashboard. The dataset contains a 'Revenue' column with a highly right-skewed distribution due to a few large enterprise transactions. The analyst wants a measure of central tendency that is robust to these outliers. Which measure should the analyst use?

Accepted Answer

Median. The median is the appropriate measure of central tendency when data is skewed or contains outliers because it depends only on the middle position. The mean would be inflated by the large enterprise transactions, while the mode and range do not serve as robust central measures. Thus, the median best represents the typical revenue.

Answer

Mode

Answer

Mean

Answer

Range

Question 14

A business analyst needs to show the monthly sales trend for the past three years. Which chart type is most appropriate?

Accepted Answer

Line chart. A line chart is the standard visualization for showing a continuous trend over time, with time on the x-axis and the measured value on the y-axis. Monthly sales across three years is a time series, so a line chart clearly reveals trends, seasonality, and growth patterns. It is the most appropriate choice for this analytical goal.

Answer

Histogram

Answer

Pie chart

Answer

Scatter plot

Question 15

A data analyst is working with a dataset that includes a categorical variable 'education_level' with four categories: High School, Bachelor's, Master's, and PhD. The analyst wants to include this variable in a linear regression model. Which encoding method should the analyst use to avoid the dummy variable trap?

Accepted Answer

One-hot encoding with three categories (dropping one). The dummy variable trap occurs when all dummy variables are included in a model with an intercept, causing perfect multicollinearity. To avoid it, one category is omitted as the reference. One-hot encoding with three categories (k-1) achieves this. Label encoding and binary encoding impose ordinality, which is inappropriate for nominal data. Including all four categories would cause multicollinearity.

Answer

Label encoding

Answer

Binary encoding

Answer

One-hot encoding with all four categories