DA0-002 Data Acquisition and Preparation Practice Test 14 — 15 Questions

Question 1

A data analyst is using a window function to assign a unique rank to each employee within their department based on salary, with ties receiving the same rank and leaving gaps. Which function should be used?

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

Accepted Answer

RANK(). RANK() assigns a rank to each row within a partition based on the ORDER BY clause, giving tied values the same rank and then skipping subsequent ranks (e.g., 1, 2, 2, 4). This matches the requirement of 'ties receiving the same rank and leaving gaps.' DENSE_RANK() would not leave gaps, ROW_NUMBER() would not assign ties, and NTILE() divides rows into buckets rather than ranking them.

Answer

DENSE_RANK()

Answer

ROW_NUMBER()

Answer

NTILE()

Question 2

A data analyst needs to create a recursive CTE to traverse a hierarchical employee-manager table. Which of the following is a key requirement for a recursive CTE?

Accepted Answer

The recursive CTE must have at least one anchor member that does not reference the CTE. A recursive CTE is defined by two parts: an anchor member that produces the base result set without referencing the CTE itself, and a recursive member that references the CTE and is combined with the anchor via UNION ALL (or UNION). The anchor member is mandatory — without it, the recursion has no starting point and the query fails. This is the defining structural requirement of a recursive CTE.

Answer

The CTE must include a WHERE clause in the recursive member

Answer

The CTE must use the RECURSIVE keyword in the WITH clause

Answer

The recursive member must use UNION instead of UNION ALL

Question 3

A data analyst is performing data profiling on a customer table. Which metric provides the number of unique values in a column?

Accepted Answer

Cardinality. Cardinality refers to the number of distinct (unique) values in a column, which is exactly the metric described. It is a fundamental data profiling statistic used to assess column uniqueness, identify candidate keys, and inform indexing or partitioning decisions. High cardinality columns (e.g., primary keys) have many unique values; low cardinality columns (e.g., status flags) have few.

Answer

Row count

Answer

Standard deviation

Answer

Null count

Question 4

A data analyst wants to ensure a sample proportionally represents different regions in a population. Which sampling method should be used?

Accepted Answer

Stratified sampling. Stratified sampling divides the population into distinct subgroups (strata) based on a shared characteristic — here, region — and then draws a proportional random sample from each stratum. This guarantees that each region is represented in the sample in proportion to its size in the population, which is exactly what the analyst requires. Simple random sampling could, by chance, under- or over-represent certain regions.

Answer

Simple random sampling

Answer

Cluster sampling

Answer

Systematic sampling

Question 5

During EDA, an analyst calculates the Z-score for each data point in a dataset. A data point with a Z-score of 3.5 is identified. What does this indicate?

Accepted Answer

The data point is likely an outlier. A Z-score of 3.5 means the data point lies 3.5 standard deviations above the mean. In most distributions, values beyond ±3 standard deviations are statistically rare (about 0.3% of a normal distribution) and are commonly flagged as outliers. This is the standard EDA heuristic for outlier detection.

Answer

The data point has a high frequency

Answer

The data point is exactly at the mean

Answer

The data point is within the interquartile range

Question 6

A data analyst uses Python's pandas library to read a CSV file into a DataFrame. Which function is used to read the file?

Accepted Answer

pd.read_csv(). pandas reads delimited text files with the top-level function pd.read_csv(), which parses the CSV into a DataFrame and supports parameters like sep, header, dtype, and parse_dates. It is the canonical entry point for CSV ingestion in pandas and is imported as part of the standard `import pandas as pd` idiom. The other options either do not exist in the pandas API or target a different file format.

Answer

pd.import_csv()

Answer

pd.read_excel()

Answer

pd.load_csv()

Question 7

An analyst writes a SQL query that uses a window function: SELECT employee_id, salary, LAG(salary, 1) OVER (ORDER BY salary DESC) AS prev_salary FROM employees. What does the LAG function return for the row with the highest salary?

Accepted Answer

NULL. LAG(salary, 1) OVER (ORDER BY salary DESC) returns the salary value from the previous row in the window ordering. For the row with the highest salary, there is no preceding row, so LAG returns NULL. This is the defined behavior of LAG when the offset goes beyond the partition boundary.

Answer

The same salary value

Answer

The next highest salary

Answer

Zero

Question 8

A data analyst needs to identify duplicate customer records based on email and phone number. Which SQL techniques can be used to find duplicates? (Select TWO).

Accepted Answer

Use a CTE to assign ROW_NUMBER() and then select rows where rn > 1. Option E is correct because grouping by email and phone with GROUP BY and filtering with HAVING COUNT(*) > 1 returns exactly those email/phone combinations that appear more than once, which is the standard way to detect duplicate records. Option D is correct because a CTE can compute ROW_NUMBER() OVER (PARTITION BY email, phone ORDER BY customer_id) and then the outer query filters WHERE rn > 1, reliably identifying all rows beyond the first occurrence of each duplicate key. Option A is not correct because ORDER BY only sorts rows and does not detect or filter duplicates. Option B is not correct because SELECT DISTINCT removes duplicates and returns only unique combinations, the opposite of finding them. Option C is not correct because a window function cannot be referenced in the WHERE clause of the same query level, so WHERE rn > 1 would fail; the ROW_NUMBER() must be wrapped in a subquery or CTE as in option D.

Answer

SELECT email, phone FROM customers ORDER BY email, phone

Answer

SELECT DISTINCT email, phone FROM customers

Answer

SELECT email, phone, ROW_NUMBER() OVER (PARTITION BY email, phone ORDER BY customer_id) AS rn FROM customers WHERE rn > 1

Question 9

A data analyst is performing EDA on a dataset with numerical features. Which methods are appropriate for identifying outliers? (Select TWO).

Accepted Answer

Z-score method. The Z-score method (C) is correct because it quantifies how many standard deviations a value lies from the mean, so observations with |z| above a threshold such as 3 (or 2.5) are flagged as outliers in numerical features. The Interquartile range (IQR) method (E) is also correct because it defines outliers as values below Q1 − 1.5×IQR or above Q3 + 1.5×IQR, making it a robust, distribution-agnostic detection technique. Mean imputation (A) is not an outlier-detection method at all; it is a missing-value handling technique that replaces NaNs with the column mean. The Pearson correlation coefficient (B) measures the linear relationship between two variables, not the extremeness of individual values, so it cannot identify outliers. Standard deviation alone (D) only describes data spread and, without a rule like the Z-score, does not flag specific observations as outliers.

Answer

Mean imputation

Answer

Pearson correlation coefficient

Answer

Standard deviation alone

Question 10

A data analyst needs to retrieve all unique job titles from the employees table. Which SQL clause should be used with the SELECT statement?

Accepted Answer

DISTINCT. The DISTINCT keyword is used to return only distinct (different) values.

Answer

TOP

Answer

UNIQUE

Answer

ORDER BY

Question 11

A data analyst is writing a query to rank products by total sales within each category, showing dense rank and avoiding gaps. Which window function should be used?

Accepted Answer

DENSE_RANK(). DENSE_RANK() assigns ranks without gaps when ties occur — if two products tie for rank 1, the next product gets rank 2, not 3. This matches the requirement to 'avoid gaps' while still assigning equal ranks to ties. It is used with OVER (PARTITION BY category ORDER BY total_sales DESC).

Answer

ROW_NUMBER()

Answer

NTILE()

Answer

RANK()

Question 12

An analyst needs to count the number of orders per customer but only for customers who have placed more than 5 orders. Which SQL construct allows filtering after aggregation?

Accepted Answer

HAVING COUNT(*) > 5. HAVING is the SQL clause specifically designed to filter groups after aggregation has been performed by GROUP BY. Because COUNT(*) is an aggregate function, it cannot be evaluated in the WHERE clause, which runs before grouping. HAVING executes after GROUP BY and aggregation, so it can reference aggregate results like COUNT(*) > 5.

Answer

WHERE COUNT(*) > 5

Answer

LIMIT 5

Answer

ORDER BY COUNT(*) > 5

Question 13

A data analyst is using pandas to clean a DataFrame. They need to replace missing values in the 'age' column with the median age. Which method should they use?

Accepted Answer

df['age'].fillna(df['age'].median()). fillna() with median() fills NaN values with the median of the column.

Answer

df['age'].replace(np.nan, df['age'].mean())

Answer

df['age'].dropna()

Answer

df['age'].interpolate()

Question 14

During data profiling, an analyst wants to identify the number of distinct values in a column. Which SQL function should be used?

Accepted Answer

COUNT(DISTINCT column). COUNT(DISTINCT column) returns the number of unique non-null values in a column, which is exactly what data profiling requires to measure cardinality. It is the standard SQL aggregate for distinct value counts.

Answer

DISTINCT(column)

Answer

COUNT(*)

Answer

COUNT(column)

Question 15

A data analyst needs to sample 1000 customers from a database of 100,000 customers for a survey, ensuring every customer has an equal chance of selection. Which sampling method is most appropriate?

Accepted Answer

Simple random sampling. Simple random sampling is the method where every individual in the population has an equal chance of being selected. This directly matches the requirement that every customer has an equal chance of selection. It is the most straightforward probability sampling technique.

Answer

Cluster sampling

Answer

Stratified sampling

Answer

Systematic sampling