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

Question 1

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 must have an anchor member (non-recursive) and a recursive member that references the CTE name, connected by UNION ALL.

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 2

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 values in a column.

Answer

Row count

Answer

Standard deviation

Answer

Null count

Question 3

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 strata (regions) and samples proportionally from each.

Answer

Simple random sampling

Answer

Cluster sampling

Answer

Systematic sampling

Question 4

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 value is 3.5 standard deviations from the mean, commonly considered an outlier (threshold often >3 or <-3).

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 5

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(). pd.read_csv() is the standard pandas function to read a CSV file.

Answer

pd.import_csv()

Answer

pd.read_excel()

Answer

pd.load_csv()

Question 6

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 returns the previous row's value in the ordered partition. For the first row (highest salary), there is no previous row, so it returns NULL.

Answer

The same salary value

Answer

The next highest salary

Answer

Zero

Question 7

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. GROUP BY with COUNT and HAVING COUNT > 1 filters groups with duplicates. ROW_NUMBER() with PARTITION BY can assign row numbers to identify duplicates.

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 8

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. IQR method uses Q1 - 1.5*IQR and Q3 + 1.5*IQR to define outliers. Z-score method uses threshold (e.g., |Z| > 3) to identify outliers.

Answer

Mean imputation

Answer

Pearson correlation coefficient

Answer

Standard deviation alone

Question 9

An analyst needs to aggregate sales data by region and product, then sort the results by total sales in descending order. Which SQL clauses are required? (Select THREE).

Accepted Answer

GROUP BY. GROUP BY is needed to aggregate, ORDER BY to sort, and DESC for descending order. SUM is an aggregate function but not a clause.

Answer

HAVING

Answer

SUM

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 wants to find customers whose last name starts with 'Mc' and have made purchases in 2023. The purchase table has a purchase_date column. Which SQL query accomplishes this?

Accepted Answer

SELECT * FROM customers c JOIN purchases p ON c.id = p.customer_id WHERE last_name LIKE 'Mc%' AND p.purchase_date BETWEEN '2023-01-01' AND '2023-12-31';. The correct query (option D) joins the customers and purchases tables on customer ID to link customers with their purchases. It uses `LIKE 'Mc%'` to match last names starting with 'Mc' (the wildcard '%' matches any sequence of characters) and filters purchase dates within the year 2023 using `BETWEEN '2023-01-01' AND '2023-12-31'`. Options A, B, and C are incorrect: A uses `LIKE 'Mc_'` which matches exactly two characters after 'Mc', not any sequence; B uses `LIKE '%Mc%'` which matches 'Mc' anywhere in the name, not just the beginning; C uses `= 'Mc%'` which treats the wildcard as a literal character; and all three fail to join the purchases table, so they cannot filter by purchase date.

Answer

SELECT * FROM customers WHERE last_name LIKE 'Mc_' AND YEAR(purchase_date) = 2023;

Answer

SELECT * FROM customers WHERE last_name LIKE '%Mc%' AND purchase_date = 2023;

Answer

SELECT * FROM customers WHERE last_name = 'Mc%' AND YEAR(purchase_date) = 2023;

Question 12

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, so tied values get the same rank and the next rank is the next consecutive number.

Answer

ROW_NUMBER()

Answer

NTILE()

Answer

RANK()

Question 13

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 used to filter groups based on aggregate conditions, unlike WHERE which filters before aggregation.

Answer

WHERE COUNT(*) > 5

Answer

LIMIT 5

Answer

ORDER BY COUNT(*) > 5

Question 14

A data analyst wants to create a temporary result set that can be referenced within a single SQL statement. Which feature should be used?

Accepted Answer

CTE. CTE (Common Table Expression) defined with WITH clause creates a temporary named result set usable within the query.

Answer

Subquery

Answer

Temporary table

Answer

Derived table

Question 15

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()