Databricks Data Engineer Associate Question 113
Select 2A data engineer is tasked with analyzing a dataset containing customer transactions. The engineer needs to determine the number of rows where the 'transaction_amount' column is null, as well as the number of rows where the 'status' column is equal to 'completed'. Which of the following statements correctly uses the count_if function and null checks to achieve this?
- A
COUNT_IF(transaction_amount IS NULL) AS null_transactions
- B
COUNT(transaction_amount IS NULL) AS null_transactions
- C
COUNT_IF(status = 'completed') AS completed_transactions
- D
COUNT(status = 'completed') AS completed_transactions
- E
COUNT_IF(transaction_amount IS NOT NULL) AS non_null_transactions
Show answer and explanation
Correct answers: A, C
Explanation
The COUNT_IF function is specifically designed to count rows that meet a given condition, such as 'IS NULL' or a comparison like 'status = completed'. On the other hand, the COUNT function is used to count non-NULL values in a column and does not directly accept conditions. The correct usage of COUNT_IF allows the data engineer to accurately count rows meeting the stated conditions.
- A. Correct.
This is correct. The COUNT_IF function is used to count rows where a specific condition is true, such as 'transaction_amount IS NULL'.
- B. Incorrect.
This is incorrect. The COUNT function does not accept a condition directly; it is used to count non-NULL values in a column.
- C. Correct.
This is correct. COUNT_IF can be used to count rows where a specific condition is true, such as 'status = completed'.
- D. Incorrect.
This is incorrect. Similar to the second option, the COUNT function does not directly accept conditions but counts non-NULL values.
- E. Incorrect.
This is incorrect. While COUNT_IF can count non-NULL values if a condition like 'transaction_amount IS NOT NULL' is provided, this was not relevant to the task described in the question.