DA0-002 Practice Test 22 — 15 Questions

Question 1

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 and systematic sampling are probability-based methods where every element has a known chance of selection.

Answer

Snowball sampling

Answer

Convenience sampling

Answer

Quota sampling

Question 2

A data analyst is performing EDA on a dataset with numerical features. Which methods are appropriate for identifying outliers? (Select TWO).

Accepted Answer

Z-score method. IQR method uses Q1 - 1.5*IQR and Q3 + 1.5*IQR to define outliers. Z-score method uses threshold (e.g., |Z| > 3) to identify outliers.

Answer

Mean imputation

Answer

Pearson correlation coefficient

Answer

Standard deviation alone

Question 3

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 4

A data analyst needs to perform a stratified random sample of a customer database. Which TWO steps are essential for this sampling method? (Select two.)

Accepted Answer

Randomly select a proportional number from each stratum. Stratified sampling requires dividing the population into strata and then randomly sampling from each stratum.

Answer

Use simple random sampling on the whole population

Answer

Randomly select entire clusters of customers

Answer

Select every nth customer from a list

Question 5

In SQL, which string function would you use to remove leading and trailing spaces from a column named 'city'?

Accepted Answer

TRIM. TRIM removes leading and trailing spaces (or other specified characters) from a string. TRIM(city) returns the city without extra spaces.

Answer

RTRIM

Answer

LTRIM

Answer

CLEAN

Question 6

A data analyst is using a recursive CTE to traverse a hierarchical organizational chart. Which THREE components are required to define a recursive CTE? (Select THREE.)

Accepted Answer

Anchor member. A recursive CTE requires an anchor member (initial query), a recursive member (that references the CTE itself), and the UNION ALL operator to combine them. The WITH clause is the outer syntax, not part of the recursion itself.

Answer

ORDER BY clause

Answer

WITH clause

Question 7

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 8

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 9

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. HAVING filters groups after aggregation, similar to WHERE but for aggregated results.

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 10

A data analyst uses a CTE to find employees who earn more than the average salary in their department. Which SQL clause is used to define the CTE?

Accepted Answer

WITH. Common Table Expressions (CTEs) are defined using the WITH keyword, followed by the CTE name and AS (query).

Answer

DECLARE

Answer

DEFINE

Answer

CTE

Question 11

A data analyst is creating a dashboard to monitor e-commerce performance. The dashboard will be used by both executives (strategic view) and operations managers (detailed view). The analyst plans to use interactive filters. Which THREE design principles or features are most important to apply for effective dashboard design?

Accepted Answer

Consistent color coding across all charts. Visual hierarchy ensures executives see key metrics first. Consistent color coding helps users quickly associate colors with categories. Interactive filters allow different users to drill down to their required level of detail. Data-ink ratio is important but less critical for multi-user dashboards. Annotations are useful for storytelling but not a core design principle for interactive dashboards.

Answer

Including annotations on every data point

Answer

Maximizing the data-ink ratio by removing all labels and titles

Question 12

When designing a dashboard for executives, which principle is most important to follow to ensure key information is immediately visible?

Accepted Answer

Create a visual hierarchy with the most important metric prominently displayed.. Visual hierarchy ensures the most important metric is prominently placed and sized to draw attention first.

Answer

Apply consistent color coding across all charts.

Answer

Use as many colors as possible to highlight different data points.

Answer

Include all available data to provide complete context.

Question 13

A senior data analyst is advising a team on dashboard design principles. Which THREE of the following are recommended best practices? (Choose three.)

Accepted Answer

Apply consistent color coding across all charts for similar metrics.. Maximizing data-ink ratio, using consistent color coding, and providing clear labels and titles are established best practices. Using many colors for variety is not recommended, nor is including every possible data point to avoid clutter.

Answer

Include every data point to provide complete context.

Answer

Use a variety of colors to make the dashboard visually exciting.

Question 14

In a Power BI report, a data analyst needs to create a measure that calculates the sum of sales only for products that have a profit margin greater than 10%. Which DAX function should be used to filter the calculation?

Accepted Answer

CALCULATE. CALCULATE modifies the filter context and can be combined with FILTER to apply specific conditions. SUMX iterates over a table but without CALCULATE it cannot apply the filter context properly in this scenario.

Answer

Answer

FILTER

Answer

SUMX

Question 15

A data analyst is presenting a story about why customer churn increased last quarter. They start by showing the current churn rate, then identify a key event (a pricing change), and finally show the impact of a proposed retention campaign. This structure follows which narrative arc?

Accepted Answer

Situation → Complication → Resolution. The situation-complication-resolution arc sets up the current state, introduces a problem, and then presents a solution.

Answer

Introduction → Methods → Results

Answer

Hook → Context → Call to action

Answer

Problem → Solution → Evidence