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

Question 1

A data analyst needs to sample 10% of customers from each of three regions (North, South, Central) to ensure proportional representation. Which sampling method should be used?

Accepted Answer

Stratified sampling. Stratified sampling divides the population into strata (regions) and samples proportionally from each. Simple random sampling would not guarantee proportional representation. Systematic sampling selects every kth element. Cluster sampling selects entire groups randomly.

Answer

Systematic sampling

Answer

Cluster sampling

Answer

Simple random sampling

Question 2

In SQL, you want to retrieve all products whose names start with 'Pro'. Which WHERE clause should you use?

Accepted Answer

WHERE product_name LIKE 'Pro%'. LIKE with pattern 'Pro%' matches strings starting with 'Pro' followed by any characters. '%Pro%' matches any string containing 'Pro', 'Pro_' matches 'Pro' plus one character, and 'Pro' is exact match.

Answer

WHERE product_name LIKE '%Pro%'

Answer

WHERE product_name LIKE 'Pro_'

Answer

WHERE product_name = 'Pro'

Question 3

You have a table 'Orders' with columns order_id, customer_id, order_date, and amount. You need to write a query that returns each customer's most recent order date and the amount for that order. Which approach is correct?

Accepted Answer

SELECT customer_id, order_date, amount FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn FROM Orders) t WHERE rn = 1. Using a window function with ROW_NUMBER() to rank orders per customer by date descending, then filtering for rank=1, gives the most recent order details. FIRST_VALUE() can also get the amount, but requires careful framing. GROUP BY with MAX(date) alone cannot get the corresponding amount.

Answer

SELECT customer_id, MAX(order_date), amount FROM Orders GROUP BY customer_id

Answer

SELECT customer_id, FIRST_VALUE(order_date) OVER (PARTITION BY customer_id ORDER BY order_date DESC), FIRST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY order_date DESC) FROM Orders

Answer

SELECT customer_id, order_date, amount FROM Orders WHERE order_date IN (SELECT MAX(order_date) FROM Orders GROUP BY customer_id)

Question 4

During exploratory data analysis, you calculate the IQR for a numeric column and find that several data points fall below Q1 - 1.5*IQR. These points are likely:

Accepted Answer

Outliers. The IQR method defines outliers as points below Q1 - 1.5*IQR or above Q3 + 1.5*IQR. Points below the lower fence are considered outliers. They are not necessarily errors, but potential outliers. They are not the median or mode.

Answer

Normal variations within the distribution

Answer

The mode of the dataset

Answer

The median of the dataset

Question 5

You have a hierarchical table 'Employees' with columns emp_id, emp_name, manager_id (referencing emp_id). You need to generate a full reporting chain from a given employee up to the CEO. Which SQL construct is most appropriate?

Accepted Answer

Recursive CTE with UNION ALL. Recursive CTEs are designed for hierarchical data, allowing iteration through parent-child relationships. Non-recursive CTEs cannot loop. Self-join with multiple levels is possible but requires knowing the depth. Window functions are not suitable for tree traversal.

Answer

Non-recursive CTE

Answer

Window function with PARTITION BY

Answer

Self-join with multiple JOINs

Question 6

A data analyst wants to extract the year from a date column 'order_date' in a SQL database. Which function should be used?

Accepted Answer

YEAR(order_date). The YEAR() function extracts the year from a date. DATEADD adds intervals, DATEDIFF calculates differences, GETDATE() returns current date.

Answer

DATEADD(year, order_date, 0)

Answer

DATEDIFF(year, order_date, GETDATE())

Answer

GETDATE()

Question 7

In pandas, you have a DataFrame 'df' with columns 'product' and 'sales'. You want to calculate the total sales per product. Which method should you use?

Accepted Answer

df.groupby('product')['sales'].sum(). df.groupby('product')['sales'].sum() groups by product and sums sales. df.pivot_table can also do it but is more complex. df.merge is for joining, df.apply is for applying a function element-wise or row/column-wise.

Answer

df['sales'].apply(sum)

Answer

df.pivot_table(values='sales', index='product', aggfunc='sum')

Answer

df.merge(df, on='product')

Question 8

A data analyst is performing data profiling on a customer table. Which TWO metrics are most useful for understanding the completeness of the data? (Choose two.)

Accepted Answer

Null count per column. Row count gives total records, null count gives missing values per column, both help assess completeness. Cardinality is for uniqueness, min/max for range, mean for central tendency.

Answer

Minimum and maximum values

Answer

Cardinality

Answer

Mean value

Question 9

A data analyst needs to identify the top 3 most frequent product categories from a sales table. Which SQL techniques can be used to achieve this? (Choose two.)

Accepted Answer

SELECT category, COUNT(*) as cnt FROM sales GROUP BY category QUALIFY DENSE_RANK() OVER (ORDER BY cnt DESC) <= 3. Both GROUP BY with ORDER BY and LIMIT, and using a window function like DENSE_RANK() to rank categories by count and filter top 3, are valid. HAVING is for filtering groups after aggregation, but without ORDER BY and LIMIT, it doesn't give top 3. A subquery with COUNT(*)>3 would get categories with count >3, not top 3. LEFT JOIN is irrelevant.

Answer

SELECT category FROM sales LEFT JOIN (SELECT category FROM sales GROUP BY category HAVING COUNT(*) > 3) AS t ON sales.category = t.category

Answer

SELECT DISTINCT category FROM sales ORDER BY category DESC LIMIT 3

Answer

SELECT category, COUNT(*) FROM sales GROUP BY category HAVING COUNT(*) > 3

Question 10

A data analyst is cleaning text data in a SQL database. Which THREE string functions are commonly used to standardize and clean text? (Choose three.)

Accepted Answer

REPLACE. TRIM removes leading/trailing spaces, UPPER/LOWER standardize case, REPLACE substitutes substrings. CONCAT concatenates strings, LENGTH returns length, SUBSTRING extracts part of string.

Answer

LENGTH

Answer

CONCAT

Question 11

A data analyst needs to retrieve only unique job titles from the 'employees' table. Which SQL keyword should be used in the SELECT clause?

Accepted Answer

DISTINCT. The DISTINCT keyword removes duplicate rows from the result set, returning only unique values.

Answer

TOP

Answer

UNIQUE

Answer

FILTER

Question 12

A dataset contains sales transactions with columns 'order_date', 'amount', and 'region'. The analyst wants to calculate the total sales per region for orders placed in 2023, but only include regions where total sales exceed $10,000. Which SQL clause should be used to filter the aggregated results?

Accepted Answer

HAVING. The HAVING clause filters groups after aggregation, whereas WHERE filters rows before grouping.

Answer

WHERE

Answer

GROUP BY

Answer

FILTER

Question 13

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?

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 14

A data analyst runs a query to count the number of customers in each city. The query uses COUNT(*) and GROUP BY city. However, the result includes NULL for some cities. What will COUNT(*) return for a group where the city is NULL?

Accepted Answer

The number of rows with NULL city. COUNT(*) counts all rows in a group, regardless of NULL values in any column. If the city is NULL, all rows in that group are counted.

Answer

NULL

Answer

0

Answer

The number of non-NULL cities

Question 15

A table 'orders' contains columns 'order_id', 'customer_id', 'order_date', and 'total'. An analyst needs to find orders placed between January 1, 2023 and December 31, 2023. Which WHERE clause is correct?

Accepted Answer

WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'. The BETWEEN operator is inclusive and is the standard way to filter date ranges.

Answer

WHERE order_date > '2023-01-01' AND order_date < '2023-12-31'

Answer

WHERE order_date IN ('2023-01-01', '2023-12-31')

Answer

WHERE order_date >= '2023-01-01' OR order_date <= '2023-12-31'