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

Question 1

A data analyst is performing exploratory data analysis on a dataset containing house prices. They want to identify outliers in the 'price' column using the IQR method. The first quartile (Q1) is $200,000, the third quartile (Q3) is $350,000, and the IQR is $150,000. What is the upper bound for identifying outliers?

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

Accepted Answer

$575,000. The IQR method defines outliers as values below Q1 - 1.5*IQR or above Q3 + 1.5*IQR. Upper bound = 350,000 + (1.5 * 150,000) = 350,000 + 225,000 = $575,000.

Answer

$500,000

Answer

$425,000

Answer

$650,000

Question 2

A data analyst needs to perform stratified sampling on a customer database to ensure proportional representation across three regions: North (40%), South (30%), and West (30%). The total sample size required is 1,000. How many customers should be sampled from the North region?

Accepted Answer

400. Stratified sampling with proportional allocation: sample size per stratum = (stratum proportion) * total sample size. North = 0.40 * 1000 = 400.

Answer

333

Answer

500

Answer

300

Question 3

A data analyst uses a CTE to find employees who earn more than the average salary in their department. Which SQL clause is used to define the CTE?

Accepted Answer

WITH. Common Table Expressions (CTEs) are defined using the WITH keyword, followed by the CTE name and AS (query).

Answer

DECLARE

Answer

DEFINE

Answer

CTE

Question 4

A data analyst is using pandas in Python to merge two DataFrames: sales (columns: sale_id, product_id, amount) and products (columns: product_id, product_name). Which pandas function should they use to combine these DataFrames on the 'product_id' column?

Accepted Answer

merge(). The pandas merge function is used to combine DataFrames on common columns. The syntax is pd.merge(sales, products, on='product_id').

Answer

combine()

Answer

join()

Answer

concat()

Question 5

In SQL, which string function would you use to remove leading and trailing spaces from a column named 'city'?

Accepted Answer

TRIM. TRIM removes leading and trailing spaces (or other specified characters) from a string. TRIM(city) returns the city without extra spaces.

Answer

RTRIM

Answer

LTRIM

Answer

CLEAN

Question 6

A data analyst is writing a query to rank products by total sales amount within each category. They want ties to have the same rank and no gaps in the ranking sequence. Which window function should they use?

Accepted Answer

DENSE_RANK(). DENSE_RANK() assigns the same rank to ties and does not skip subsequent ranks. RANK() also assigns same rank to ties but skips numbers, creating gaps.

Answer

ROW_NUMBER()

Answer

RANK()

Answer

NTILE()

Question 7

A data analyst needs to count the number of orders placed by each customer, but only for customers who have placed more than 5 orders. Which SQL clause should be used to filter the aggregated results?

Accepted Answer

HAVING. HAVING is used to filter groups after aggregation. The query would use GROUP BY customer_id, then HAVING COUNT(*) > 5.

Answer

FILTER

Answer

WHERE

Answer

LIMIT

Question 8

A data analyst is performing data profiling on a customer table. Which TWO of the following are key metrics to assess data quality? (Select TWO.)

Accepted Answer

Minimum and maximum values. Null counts indicate missing values, and min/max values can reveal outliers or unexpected ranges. Row count alone doesn't assess quality; cardinality and data type verification are also important but the question asks for key metrics among the options.

Answer

Row count

Answer

Cardinality

Answer

Data type verification

Question 9

A data analyst is using a recursive CTE to traverse a hierarchical organizational chart. Which THREE components are required to define a recursive CTE? (Select THREE.)

Accepted Answer

Anchor member. A recursive CTE requires an anchor member (initial query), a recursive member (that references the CTE itself), and the UNION ALL operator to combine them. The WITH clause is the outer syntax, not part of the recursion itself.

Answer

ORDER BY clause

Answer

WITH clause

Question 10

A data analyst wants to sample a large dataset of customer transactions. Which TWO sampling methods are probability-based and ensure every element has a known chance of being selected? (Select TWO.)

Accepted Answer

Simple random sampling. Simple random sampling and stratified sampling are probability-based methods where each element has a known probability. Systematic sampling is also probability-based but the question asks for TWO; convenience sampling is non-probability, and cluster sampling is probability-based but the question likely expects the two most common.

Answer

Convenience sampling

Answer

Systematic sampling

Answer

Cluster sampling

Question 11

In a dataset of customer orders, you need to count the number of distinct customers who have placed orders. Which SQL aggregate function should you use?

Accepted Answer

COUNT(DISTINCT customer_id). COUNT(DISTINCT column) counts the number of unique non-null values in a column. COUNT(*) counts all rows including duplicates, COUNT(column) counts non-null values including duplicates. DISTINCT alone is not an aggregate function.

Answer

DISTINCT COUNT(customer_id)

Answer

COUNT(customer_id)

Answer

COUNT(*)

Question 12

You are using pandas in Python to clean a dataset. You notice several rows with missing values in the 'age' column. Which method would you use to remove those rows?

Accepted Answer

df.dropna(). df.dropna() removes rows with any missing values by default. df.fillna() fills missing values, df.isna() returns a boolean mask, df.drop_duplicates() removes duplicate rows.

Answer

df.drop_duplicates()

Answer

df.fillna(0)

Answer

df.isna()

Question 13

A data analyst wants to retrieve the top 5 highest-paid employees from an 'employees' table, including ties. Which SQL clause should be used?

Accepted Answer

TOP 5 WITH TIES. In many SQL dialects, LIMIT can be used to restrict rows. However, to include ties, some databases offer WITH TIES with FETCH FIRST or TOP. Standard SQL: FETCH FIRST 5 ROWS WITH TIES. TOP 5 alone does not include ties. RANK() with WHERE clause can work but is more complex.

Answer

ORDER BY salary DESC LIMIT 5

Answer

HAVING salary > 50000

Answer

WHERE ROWNUM <= 5

Question 14

You are analyzing sales data and need to calculate the moving average of monthly sales over the previous 3 months for each month. Which type of function is best suited for this task?

Accepted Answer

Window function with OVER(). Window functions, specifically using OVER() with ORDER BY and a frame specification, can compute moving averages. Aggregate functions alone cannot access previous rows without a self-join. String and date functions are irrelevant.

Answer

String function

Answer

Aggregate function with GROUP BY

Answer

Date function

Question 15

A data quality assessment reveals that a column named 'email' contains values like 'user@example' (missing domain extension). Which data profiling technique would best identify such pattern violations?

Accepted Answer

Pattern analysis. Pattern analysis involves checking values against expected patterns (e.g., regex for email format). Cardinality counts distinct values, referential integrity checks relationships between tables, and data type verification checks data types.

Answer

Cardinality analysis

Answer

Referential integrity check

Answer

Data type verification