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

Question 1

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. Option B (Null count per column) is correct because completeness specifically measures how much required data is present, and counting NULLs (or missing/blank values) per column directly quantifies the gaps in each field. Option C (Row count) is correct because it establishes the total number of records in the table, which is the denominator needed to calculate completeness ratios such as (row count − null count) / row count and to detect missing rows. Option A (Minimum and maximum values) describes the range of values, which is a validity/domain concern rather than completeness. Option D (Cardinality) measures the number of distinct values and relates to uniqueness or diversity, not missingness. Option E (Mean value) is a central-tendency statistic that assumes numeric, non-null data and says nothing about how complete the data is.

Answer

Minimum and maximum values

Answer

Cardinality

Answer

Mean value

Question 2

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. Option A is correct because it aggregates rows with GROUP BY category, computes COUNT(*) as cnt, and then applies the QUALIFY clause with DENSE_RANK() OVER (ORDER BY cnt DESC) <= 3 to filter the ranked groups down to the top 3 most frequent categories. Option B is correct because it groups by category, orders the grouped result by COUNT(*) descending, and uses LIMIT 3 to return exactly the three categories with the highest frequencies. Option C is incorrect because it uses a LEFT JOIN with a subquery that filters categories having COUNT(*) > 3, which does not rank or limit the result to the top 3 categories. Option D is incorrect because SELECT DISTINCT category ORDER BY category DESC LIMIT 3 returns three category names in alphabetical descending order, not by frequency. Option E is incorrect because HAVING COUNT(*) > 3 filters categories with more than three occurrences, which is a threshold filter rather than a top-3 ranking.

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 3

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'

Question 4

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 5

A data analyst is performing data profiling on a customer table. Which TWO metrics are commonly used to assess the completeness of a column? (Select TWO.)

Accepted Answer

Row count. Row count (A) is correct because it establishes the total number of records in the column, which is the denominator needed to calculate completeness as the proportion of non-null values. Null count (B) is correct because it directly quantifies missing values in the column, and completeness is typically computed as (row count − null count) / row count. Together, row count and null count let the analyst measure how fully populated the column is. Cardinality (C) measures the number of distinct values and relates to uniqueness, not completeness. Mean (D) and standard deviation (E) are statistical aggregates describing central tendency and spread of numeric data, not missingness.

Answer

Cardinality

Answer

Mean

Answer

Standard deviation

Question 6

A dataset contains a column 'birthdate' in 'YYYY-MM-DD' format. The analyst needs to calculate the average age of customers as of today. Which combination of functions is most appropriate?

Accepted Answer

DATEDIFF(year, birthdate, GETDATE()). DATEDIFF(year, birthdate, GETDATE()) returns the number of year boundaries crossed between the two dates, which is the standard SQL method for calculating age. The other options subtract year components, which gives only an estimate that ignores the month and day, leading to inaccuracies.

Answer

AVG(YEAR(GETDATE()) - YEAR(birthdate))

Answer

YEAR(GETDATE()) - YEAR(birthdate)

Answer

EXTRACT(YEAR FROM GETDATE()) - EXTRACT(YEAR FROM birthdate)

Question 7

A data analyst is using pandas in Python to clean a dataset. Which method is most appropriate to replace missing numerical values with the median of the column?

Accepted Answer

df.fillna(df.median()). df.fillna(df.median()) replaces missing values in each numeric column with that column's median, because fillna accepts a Series/DataFrame of fill values aligned by column. This is the idiomatic pandas approach for median imputation and handles numeric columns correctly.

Answer

df.interpolate()

Answer

df.dropna()

Answer

df.replace(np.nan, df.mean())

Question 8

A data analyst is exploring a sales dataset and wants to identify columns that are likely to be foreign keys. Which TWO characteristics would indicate a foreign key?

Accepted Answer

The column name ends with '_id'. Option A is correct because a naming convention such as a column ending in '_id' (e.g., customer_id, product_id) strongly signals a foreign key that references another table's primary key. Option D is correct because the defining characteristic of a foreign key is that its values form a subset of the values in a primary key (or unique key) column of another table, enforcing referential integrity. Option B is not a reliable indicator, since NULLs merely indicate optional values and can appear in many non-key columns. Option C is not reliable either, as integer type is common to many columns and foreign keys can be non-integer (e.g., a VARCHAR code). Option E is incorrect because a UNIQUE constraint describes a candidate key or alternate key, not a foreign key, which by definition allows duplicate values referencing the same parent row.

Answer

The column contains NULL values

Answer

The column is of integer data type

Answer

The column has a UNIQUE constraint

Question 9

A data analyst needs to sample records from a large dataset for a quick analysis. Which TWO sampling methods are examples of probability sampling?

Accepted Answer

Simple random sampling. Simple random sampling (B) is a probability sampling method because every record in the dataset has an equal and known chance of being selected, typically via random number generation or a random seed. Systematic sampling (C) is also a probability method: it selects every k-th record after a random start, giving each element a known, nonzero selection probability (1/k). By contrast, snowball sampling (A) relies on referrals from initial participants, convenience sampling (D) selects easily accessible records, and quota sampling (E) fills predefined categories non-randomly — all are non-probability methods because selection probabilities are unknown or zero for some records.

Answer

Snowball sampling

Answer

Convenience sampling

Answer

Quota sampling

Question 10

An analyst needs to retrieve the year from an order_date column (datetime type). Which function should be used in SQL?

Accepted Answer

EXTRACT(YEAR FROM order_date). The EXTRACT(YEAR FROM order_date) function is the ANSI SQL standard way to retrieve the year component from a datetime value, and it is supported across major databases including PostgreSQL, Oracle, MySQL 8.0+, and SQL Server (with slight syntax variations). It returns the year as an integer and is the most portable, standards-compliant option. YEAR(order_date) also works in many databases but is not ANSI standard and is not supported in PostgreSQL or Oracle.

Answer

FORMAT(order_date, 'yyyy')

Answer

DATEADD(YEAR, order_date, 0)

Answer

YEAR(order_date)

Question 11

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 (A) is required because it collapses the rows into groups by region and product so that an aggregate can be computed per group. SUM (C) is the aggregate function that produces the total sales value for each region/product group, which is the metric being sorted. ORDER BY (D) is required to sort the result set, and DESC (E) is required to make that sort descending by total sales. HAVING (B) is not needed because no filtering of groups is requested; it only filters aggregated groups and would be optional here.

Answer

HAVING

Answer

SUM

Question 12

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';. It properly joins the customers and purchases tables on the customer ID, then filters for last names starting with 'Mc' using the LIKE operator with a trailing wildcard ('Mc%'), and restricts purchase dates to the 2023 calendar year using a BETWEEN range on the purchase_date column. The join is essential because the purchase_date column resides in the purchases table, not the customers table, and the query must correlate customers with their purchases. The date range '2023-01-01' to '2023-12-31' correctly captures all purchases made in 2023, including those with time components, since the end date is inclusive when using BETWEEN with date literals (assuming no time portion or using date-only comparison).

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 13

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. A Common Table Expression (CTE) is a named temporary result set defined with the WITH clause that exists only for the duration of a single SQL statement (e.g., SELECT, INSERT, UPDATE, DELETE). It can be referenced multiple times within that statement, making complex queries more readable and modular. Unlike a derived table, a CTE is explicitly named and can be self-referenced (recursive), which is ideal for hierarchical data. The key requirement here is 'within a single SQL statement,' which exactly matches the scope of a CTE.

Answer

Subquery

Answer

Temporary table

Answer

Derived table

Question 14

A data analyst needs to calculate the running total of sales for each product over time. Which window function clause is essential for this calculation?

Accepted Answer

PARTITION BY product_id ORDER BY sale_date. To calculate a running total of sales for each product over time, the window function must partition by product_id and order by sale_date. The PARTITION BY clause groups rows by product, and the ORDER BY clause within the window defines the cumulative order. The default frame when ORDER BY is specified is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which yields a running total. Thus, 'PARTITION BY product_id ORDER BY sale_date' is essential.

Answer

ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW

Answer

PARTITION BY product_id

Answer

ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Question 15

An analyst is using a CTE to compute hierarchical data. Which TWO statements about recursive CTEs are true?

Accepted Answer

The recursive member must reference the CTE name. Option A is correct because in a recursive CTE the recursive member must reference the CTE name itself to perform the iterative self-join that walks the hierarchy. Option B is correct because the anchor member is defined as the initial SELECT statement that does not reference the CTE, providing the base rows from which recursion starts. Option C is incorrect because recursive CTEs handle many hierarchical and graph structures, not just numerical sequences. Option D is incorrect because recursive CTEs require a UNION ALL (or UNION) between anchor and recursive members, but it is not mandatory that it specifically be UNION ALL in every implementation. Option E is incorrect because GROUP BY can be used in the anchor or recursive member, subject to the database engine's rules.

Answer

Recursive CTEs can only be used for numerical sequences

Answer

Recursive CTEs must include a UNION ALL operator

Answer

Recursive CTEs cannot be used with GROUP BY