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

Question 1

A data analyst wants to assign a unique sequential integer to each row in a result set, starting at 1, based on the order of the 'sales_amount' column descending. Which window function should be used?

Accepted Answer

ROW_NUMBER() OVER (ORDER BY sales_amount DESC). ROW_NUMBER() assigns a unique sequential integer to each row within a partition, starting at 1.

Answer

DENSE_RANK() OVER (ORDER BY sales_amount DESC)

Answer

RANK() OVER (ORDER BY sales_amount DESC)

Answer

NTILE(1) OVER (ORDER BY sales_amount DESC)

Question 2

A data analyst is using a recursive CTE to traverse an organizational hierarchy. What is the purpose of the anchor member in the recursive CTE?

Accepted Answer

It provides the initial seed or starting rows for the recursion.. The anchor member initializes the recursion with the base result set.

Answer

It filters the final output of the recursive CTE.

Answer

It specifies how to join the CTE with itself recursively.

Answer

It defines the termination condition for the recursion.

Question 3

A data analyst is profiling a dataset and finds that the 'email' column contains some NULL values. Which SQL query can be used to count how many rows have a NULL email?

Accepted Answer

SELECT COUNT(*) FROM table WHERE email IS NULL. COUNT(*) counts all rows; WHERE email IS NULL filters only null rows.

Answer

SELECT COUNT(email) FROM table WHERE email = NULL

Answer

SELECT SUM(CASE WHEN email IS NULL THEN 1 END) FROM table

Answer

SELECT COUNT(ISNULL(email)) FROM table

Question 4

Which data sampling method involves selecting every k-th element from a list after a random start?

Accepted Answer

Systematic sampling. Systematic sampling selects every k-th item after a random start.

Answer

Cluster sampling

Answer

Stratified sampling

Answer

Simple random sampling

Question 5

A data analyst is using pandas to read a CSV file named 'sales.csv'. Which line of code correctly reads the file into a DataFrame?

Accepted Answer

import pandas as pd; df = pd.read_csv('sales.csv'). The pandas function read_csv reads a CSV file into a DataFrame.

Answer

import csv; df = csv.read('sales.csv')

Answer

import pandas as pd; df = pd.read('sales.csv')

Answer

import numpy as np; df = np.read_csv('sales.csv')

Question 6

A data analyst is using the IQR method to identify outliers in a dataset. The first quartile (Q1) is 25 and the third quartile (Q3) is 45. What is the upper bound for identifying outliers?

Accepted Answer

75. Upper bound = Q3 + 1.5 * IQR; IQR = Q3 - Q1 = 20; 1.5*20 = 30; 45+30 = 75.

Answer

85

Answer

65

Answer

55

Question 7

A data analyst needs to create a new column 'full_name' by concatenating 'first_name' and 'last_name' with a space. Which SQL function should be used in the SELECT clause?

Accepted Answer

CONCAT(first_name, ' ', last_name). CONCAT concatenates strings; in some DBMS, || or + is used, but CONCAT is standard.

Answer

COMBINE(first_name, last_name)

Answer

JOIN(first_name, last_name)

Answer

first_name + ' ' + last_name

Question 8

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. Completeness is measured by null count and row count; null count shows missing values, row count gives total rows.

Answer

Cardinality

Answer

Mean

Answer

Standard deviation

Question 9

A data analyst is investigating a correlation between two continuous variables. Which THREE of the following are appropriate steps in this exploratory data analysis? (Select THREE.)

Accepted Answer

Calculate the Pearson correlation coefficient. Scatter plot visualizes relationship, correlation coefficient quantifies strength, and removing outliers may be needed to avoid misleading results.

Answer

Perform a t-test

Answer

Create a contingency table

Question 10

A data analyst wants to retrieve the top 5 highest-paid employees from the 'employees' table. Which SQL clauses could be used to achieve this? (Select TWO.)

Accepted Answer

ORDER BY salary DESC. ORDER BY salary DESC sorts descending, and LIMIT/TOP/FETCH restricts rows.

Answer

HAVING salary

Answer

ORDER BY salary ASC

Answer

GROUP BY salary

Question 11

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 12

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). SUM() with OVER(PARTITION BY department ORDER BY date) computes a running total within each department.

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 13

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 14

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 15

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. Null count directly measures missing values.

Answer

Cardinality

Answer

Mean

Answer

Row count