DA0-002 Practice Test 20 — 15 Questions

Question 1

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?

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

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 2

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 3

A data analyst needs to retrieve all unique job titles from the employees table. Which SQL clause should be used with the SELECT statement?

Accepted Answer

DISTINCT. The DISTINCT keyword is used to return only distinct (different) values.

Answer

TOP

Answer

UNIQUE

Answer

ORDER BY

Question 4

A data analyst needs to retrieve the top 5 most expensive products from a 'products' table sorted by price descending. Which TWO SQL clauses are required to achieve this? (Select TWO).

Accepted Answer

ORDER BY price DESC. ORDER BY DESC sorts prices descending, and LIMIT restricts to 5 rows. GROUP BY and HAVING are for aggregation; WHERE is for filtering but not needed here.

Answer

HAVING COUNT(*) > 1

Answer

WHERE price > 100

Answer

GROUP BY price

Question 5

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 6

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 7

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 8

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 9

A data analyst is reviewing a dataset of customer transactions and wants to assess data quality by profiling the 'order_date' column. Which TWO profiling tasks are most appropriate for this date column? (Select TWO).

Accepted Answer

Count of null values. Profiling a date column typically includes checking for null values and verifying the data type. Cardinality and pattern analysis are more relevant for categorical or string columns; variance is for numeric data.

Answer

Pattern analysis (e.g., format consistency)

Answer

Variance

Answer

Cardinality (number of unique values)

Question 10

In a dataset of employee salaries, the analyst notices one value that is significantly higher than the rest. Using the IQR method, which values are typically considered outliers?

Accepted Answer

Values beyond Q1 - 1.5*IQR or Q3 + 1.5*IQR. Outliers are values less than Q1 - 1.5*IQR or greater than Q3 + 1.5*IQR.

Answer

Values beyond Q1 - 3*IQR or Q3 + 3*IQR

Answer

Values beyond mean ± 2 standard deviations

Answer

Values beyond min and max

Question 11

An analyst needs to identify outliers in a numeric column 'transaction_amount' using the interquartile range (IQR) method. Which TWO steps are part of this process? (Select TWO).

Accepted Answer

Subtract 1.5 times the IQR from Q1 and add 1.5 times the IQR to Q3 to define bounds. The IQR method involves calculating Q1 and Q3 to find IQR, then defining lower and upper bounds as Q1 - 1.5*IQR and Q3 + 1.5*IQR. Computing mean and standard deviation is for Z-score method; calculating median alone is insufficient.

Answer

Calculate the median of the column

Answer

Sort the data and remove the top and bottom 5%

Answer

Compute the mean and standard deviation of the column

Question 12

A marketing team wants to segment customers into distinct groups based on purchasing behavior. The data includes numeric features such as frequency, monetary value, and recency. Which unsupervised learning algorithm should be used?

Accepted Answer

K-means clustering. K-means clustering is the correct choice because it is an unsupervised learning algorithm that partitions data into K distinct clusters based on feature similarity. For segmenting customers by purchasing behavior (frequency, monetary value, recency), K-means groups customers with similar numeric patterns without requiring labeled outcomes, making it ideal for exploratory segmentation.

Answer

Decision tree

Answer

Linear regression

Answer

Association rules

Question 13

A company wants to ensure that data visualizations are accessible to colorblind users. Which of the following is a best practice?

Accepted Answer

Combine color with patterns or labels.. Combining color with patterns or labels ensures that information is conveyed through multiple visual channels, making it accessible to colorblind users who may not distinguish certain hues. This practice aligns with WCAG (Web Content Accessibility Guidelines) 2.1, which recommend using more than one sensory characteristic (e.g., shape, text, or pattern) to communicate data, rather than relying solely on color.

Answer

Avoid using any colors.

Answer

Rely solely on color to convey information.

Answer

Use only shades of green and red.

Question 14

Which TWO of the following are effective techniques for presenting data to a non-technical audience?

Accepted Answer

Highlight the most important insights using callouts.. Highlighting key insights with callouts directly addresses the needs of a non-technical audience by drawing attention to the most important findings without requiring them to interpret complex data. This technique aligns with best practices for data storytelling, where visual emphasis on critical points improves comprehension and retention for stakeholders who may not have a technical background.

Answer

Explain the statistical methods used in the analysis.

Answer

Include detailed data tables for reference.

Answer

Use many different colors to distinguish data points.

Question 15

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