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

Question 1

A data analyst is performing data profiling on a customer dataset. Which metric would best reveal the number of distinct values in the 'state' column?

Accepted Answer

Cardinality. Cardinality refers to the number of unique values in a column, which directly indicates distinct states.

Answer

Mean

Answer

Row count

Answer

Null count

Question 2

An analyst is using Python pandas and has a DataFrame 'sales' with columns 'date', 'product', 'revenue'. They need to create a pivot table showing total revenue per product per month. Which pandas function is most appropriate?

Accepted Answer

sales.pivot_table(index='product', columns='month', values='revenue', aggfunc='sum'). pivot_table is specifically designed to reshape data and aggregate values based on index and columns.

Answer

sales.groupby(['product', 'month']).sum()

Answer

sales.pivot(index='product', columns='month', values='revenue')

Answer

sales.melt(id_vars=['product'], value_vars=['month', 'revenue'])

Question 3

A dataset contains a 'salary' column. The analyst wants to identify outliers using the IQR method. If Q1 = 40,000 and Q3 = 70,000, what is the upper threshold for a non-outlier?

Accepted Answer

115,000. Upper threshold = Q3 + 1.5 * IQR. IQR = 70,000 - 40,000 = 30,000. So upper = 70,000 + 45,000 = 115,000.

Answer

130,000

Answer

85,000

Answer

100,000

Question 4

An analyst is sampling a large customer database to estimate the average purchase amount. To ensure that the sample proportionally represents different customer segments (e.g., age groups), which sampling method should be used?

Accepted Answer

Stratified sampling. Stratified sampling divides the population into strata (e.g., age groups) and samples proportionally from each stratum.

Answer

Systematic sampling

Answer

Simple random sampling

Answer

Cluster sampling

Question 5

Which SQL aggregate function would an analyst use to calculate the average value of a numeric column?

Accepted Answer

AVG. AVG calculates the arithmetic mean of a numeric column.

Answer

SUM

Answer

COUNT

Answer

MEDIAN

Question 6

A data analyst is cleaning a dataset and finds that some cells in the 'email' column contain leading spaces. Which string function should be used to remove these spaces?

Accepted Answer

TRIM. TRIM removes leading and trailing spaces from a string.

Answer

LTRIM

Answer

REPLACE

Answer

SUBSTRING

Question 7

In a table 'sales_team' with columns 'salesperson', 'quarter', and 'revenue', an analyst wants to assign a rank to each salesperson within their quarter based on revenue, with the highest revenue getting rank 1. However, if two salespeople have the same revenue, they should receive the same rank, and the next rank should be the next consecutive integer (no gaps). Which window function should be used?

Accepted Answer

DENSE_RANK(). DENSE_RANK() assigns ranks with no gaps for ties, whereas RANK() leaves gaps.

Answer

RANK()

Answer

NTILE(4)

Answer

ROW_NUMBER()

Question 8

A data analyst is conducting exploratory data analysis (EDA) on a dataset. Which TWO tasks are typically performed during EDA? (Select two.)

Accepted Answer

Identify outliers using the IQR method. Outlier detection and correlation analysis are key EDA activities. Model building and data sampling are separate steps.

Answer

Create a sampling plan

Answer

Build a predictive regression model

Answer

Deploy the model to production

Question 9

An analyst is using SQL to analyze employee data. Which THREE of the following are valid uses of the WHERE clause? (Select three.)

Accepted Answer

Filter rows where manager_id is NULL using IS NULL. WHERE can filter using LIKE, BETWEEN, and IS NULL. HAVING is for aggregated results, and ORDER BY is for sorting.

Answer

Sort the result set by hire_date

Answer

Filter groups after aggregation using HAVING

Question 10

A data analyst needs to perform a stratified random sample of a customer database. Which TWO steps are essential for this sampling method? (Select two.)

Accepted Answer

Randomly select a proportional number from each stratum. Stratified sampling requires dividing the population into strata and then randomly sampling from each stratum.

Answer

Use simple random sampling on the whole population

Answer

Randomly select entire clusters of customers

Answer

Select every nth customer from a list

Question 11

A data analyst needs to count the number of distinct product categories in a table named 'products'. Which SQL function should be used in the SELECT clause?

Accepted Answer

COUNT(DISTINCT category). COUNT(DISTINCT column) counts unique non-null values in a column.

Answer

COUNT(category)

Answer

DISTINCT COUNT(category)

Answer

COUNT(*) WHERE category IS NOT NULL

Question 12

A data analyst wants to identify customers whose last name starts with 'Mc' from the 'customers' table. Which WHERE clause condition should be used?

Accepted Answer

last_name LIKE 'Mc%'. The LIKE operator with '%' wildcard matches any sequence of characters after 'Mc'.

Answer

last_name LIKE 'Mc_'

Answer

last_name IN ('Mc%')

Answer

last_name = 'Mc%'

Question 13

A data analyst is reviewing sales data and wants to find orders where the order total is between $100 and $500, inclusive. Which WHERE clause is correct?

Accepted Answer

total BETWEEN 100 AND 500. BETWEEN is inclusive of both endpoints.

Answer

total > 100 AND total < 500

Answer

total IN (100, 500)

Answer

total >= 100 OR total <= 500

Question 14

A data analyst runs the query: SELECT AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 60000. What is the purpose of the HAVING clause?

Accepted Answer

It filters departments where the average salary exceeds $60,000.. HAVING filters groups after aggregation, unlike WHERE which filters rows before aggregation.

Answer

It orders departments by average salary descending.

Answer

It returns only the department with the maximum average salary.

Answer

It filters individual employee rows with salary > 60000 before grouping.

Question 15

Which SQL function can be used to extract the year from a date column 'order_date'?

Accepted Answer

YEAR(order_date). The YEAR function extracts the year portion from a date.

Answer

DATEDIFF(year, order_date)

Answer

DATEADD(year, order_date)

Answer

FORMAT(order_date, 'yyyy')