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

Question 1

A data analyst needs to count the number of customers who have placed at least one order. Which SQL query should be used?

Accepted Answer

SELECT COUNT(DISTINCT customer_id) FROM orders. COUNT(DISTINCT customer_id) counts each unique customer exactly once, which is precisely what is needed to answer 'how many customers placed at least one order' — a customer with multiple orders is counted only once. This is the standard SQL pattern for counting distinct entities in a fact table.

Answer

SELECT DISTINCT COUNT(customer_id) FROM orders

Answer

SELECT SUM(customer_id) FROM orders

Answer

SELECT COUNT(customer_id) FROM orders

Question 2

A data analyst wants to find the top 5 products by total sales amount, but only for products that have been sold more than 50 times. Which SQL query accomplishes this?

Accepted Answer

SELECT product_id, SUM(sales_amount) FROM sales GROUP BY product_id HAVING COUNT(*) > 50 ORDER BY SUM(sales_amount) DESC LIMIT 5. HAVING filters after aggregation, then ORDER BY and LIMIT give the top 5.

Answer

SELECT product_id, SUM(sales_amount) FROM sales WHERE COUNT(*) > 50 GROUP BY product_id ORDER BY SUM(sales_amount) DESC LIMIT 5

Answer

SELECT product_id, SUM(sales_amount) FROM sales GROUP BY product_id HAVING COUNT(*) > 50 ORDER BY SUM(sales_amount) ASC LIMIT 5

Answer

SELECT product_id, SUM(sales_amount) FROM sales GROUP BY product_id WHERE COUNT(*) > 50 ORDER BY SUM(sales_amount) DESC LIMIT 5

Question 3

An analyst needs to compute a running total of sales for each department, ordered by date. Which window function is most appropriate?

Accepted Answer

SUM(sales) OVER (PARTITION BY department ORDER BY date). The running total of sales for each department, ordered by date, requires a window function that sums sales over a partition by department and orders by date. SUM(sales) OVER (PARTITION BY department ORDER BY date) computes a cumulative sum within each department as the date progresses, which is exactly a running total.

Answer

ROW_NUMBER() OVER (PARTITION BY department ORDER BY date)

Answer

SUM(sales) OVER (ORDER BY date)

Answer

LAG(sales, 1) OVER (PARTITION BY department ORDER BY date)

Question 4

A data analyst wants to generate a report showing employee names and their department names, but some employees are not assigned to any department. The analyst wants to include all employees. Which JOIN type should be used?

Accepted Answer

LEFT JOIN. LEFT JOIN includes all rows from the left table (employees) even if no match in departments.

Answer

INNER JOIN

Answer

CROSS JOIN

Answer

RIGHT JOIN

Question 5

A data analyst is performing data profiling on a customer table. Which metric would best help identify missing values in the 'phone' column?

Accepted Answer

Null count. The null count metric directly measures the number of missing (NULL) values in a column, which is exactly what the analyst needs to identify missing phone numbers. Data profiling tools report null count per column as a standard completeness metric. Other metrics like cardinality or mean do not reveal missingness.

Answer

Cardinality

Answer

Mean

Answer

Row count

Question 6

A data analyst wants to randomly select 100 customers from a database for a survey, ensuring that the sample reflects the proportion of male and female customers in the population. Which sampling method is most appropriate?

Accepted Answer

Stratified sampling. Stratified sampling divides the population into homogeneous subgroups (strata) — here, male and female — and then draws random samples from each stratum in proportion to its size in the population. This guarantees the sample mirrors the population's gender proportions, which is exactly what the analyst requires. Simple random sampling could, by chance, under- or over-represent one gender.

Answer

Cluster sampling

Answer

Simple random sampling

Answer

Systematic sampling

Question 7

In a table with columns 'employee_id' and 'manager_id', a data analyst needs to retrieve the hierarchy level of each employee, where the top manager has manager_id NULL. Which SQL feature is best suited?

Accepted Answer

A recursive CTE. Recursive CTE can traverse hierarchical data to compute levels.

Answer

A window function with ROW_NUMBER()

Answer

A GROUP BY clause with aggregation

Answer

A self-join with a LEFT JOIN

Question 8

An analyst wants to identify outliers in a dataset using the IQR method. Which values are typically considered outliers?

Accepted Answer

Values below Q1 - 1.5*IQR or above Q3 + 1.5*IQR. The IQR (interquartile range) method defines outliers as values that fall below Q1 - 1.5*IQR or above Q3 + 1.5*IQR, where IQR = Q3 - Q1. This is Tukey's standard fence rule used in box-and-whisker plots. The 1.5 multiplier is the conventional threshold for 'mild' outliers, while 3.0*IQR marks 'extreme' outliers.

Answer

Values below the mean or above the mean

Answer

Values below Q1 - IQR or above Q3 + IQR

Answer

Values below Q2 - 2*IQR or above Q2 + 2*IQR

Question 9

A dataset contains transaction amounts with a few extremely high values. The analyst wants to reduce the impact of these outliers on the average. Which measure of central tendency is most robust?

Accepted Answer

Median. Median is not affected by extreme values, while mean is sensitive.

Answer

Mean

Answer

Mode

Answer

Standard deviation

Question 10

A data analyst uses a CTE to simplify a complex query. Which keyword is used to define a CTE?

Accepted Answer

WITH. A Common Table Expression (CTE) is defined using the WITH keyword followed by a name and an AS clause containing the subquery. WITH precedes the main SELECT and can define one or more named CTEs separated by commas.

Answer

DEFINE

Answer

CTE

Answer

DECLARE

Question 11

A data analyst is using Python pandas to perform exploratory data analysis. Which THREE methods are commonly used to assess data quality and distributions?

Accepted Answer

df.describe(). describe() gives summary statistics, info() shows data types and non-null counts, and value_counts() shows frequency distributions.

Answer

df.transpose()

Answer

df.sort_values()

Question 12

A data analyst runs the following query: SELECT DISTINCT city FROM customers. What is the primary purpose of using the DISTINCT keyword in this query?

Accepted Answer

To remove duplicate city names. The DISTINCT keyword in a SELECT statement eliminates duplicate rows from the result set. In 'SELECT DISTINCT city FROM customers', if multiple customers live in the same city, that city name appears only once in the output, producing a list of unique city values. DISTINCT operates on the entire row of selected columns, so if multiple columns are selected, the combination must be unique to be retained.

Answer

To sort the cities alphabetically

Answer

To count the number of cities

Answer

To filter cities that start with a specific letter

Question 13

In a sales database, an analyst needs to retrieve all orders where the order amount is between $100 and $500. Which WHERE clause should be used?

Accepted Answer

WHERE amount BETWEEN 100 AND 500. The BETWEEN operator is inclusive of the boundary values, so WHERE amount BETWEEN 100 AND 500 will return all orders where the amount is greater than or equal to 100 and less than or equal to 500. This is the most concise and correct way to express the range.

Answer

WHERE amount IN (100, 500)

Answer

WHERE amount >= 100 AND amount <= 500

Answer

WHERE amount > 100 AND amount < 500

Question 14

A data analyst uses the following query: SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department HAVING AVG(salary) > 50000. What is the purpose of the HAVING clause in this query?

Accepted Answer

To filter groups (departments) based on the average salary. The HAVING clause filters groups after aggregation, so in this query it keeps only departments whose average salary exceeds 50000. It operates on the result of GROUP BY, which is why it can reference aggregate functions like AVG(salary) that WHERE cannot.

Answer

To ensure only departments with more than 50000 employees are shown

Answer

To filter individual employee records before grouping

Answer

To sort departments by average salary

Question 15

A data analyst wants to concatenate first_name and last_name columns with a space in between. Which string function combination should be used in SQL?

Accepted Answer

CONCAT(first_name, ' ', last_name). CONCAT(first_name, ' ', last_name) is correct because CONCAT accepts multiple arguments and joins them in order; inserting a literal space string ' ' between the two column arguments produces the desired 'first last' format. This is the standard, portable SQL approach for joining columns with a delimiter.

Answer

first_name + ' ' + last_name

Answer

SUBSTRING(first_name, 1, 1) + '.' + last_name

Answer

CONCAT(first_name, last_name)