Databricks Data Engineer Associate Question 115
Select 2You are working with a Delta table named 'sales' in Databricks that contains thousands of sales transactions. The table has a column named 'discount' that can contain NULL values. You need to calculate the total number of entries where no discount was applied and compare it to the total number of NULL values in the 'discount' column. Which of the following approaches will correctly achieve this?
- A
Use the
count_if(discount IS NULL)function to count rows with NULL values in the 'discount' column. - B
Use the
count(*) - count(discount)formula to calculate the number of NULL values in the 'discount' column. - C
Use the
count_if(discount IS NOT NULL)function to count rows where a discount was applied. - D
Use the
count_if(discount IS NULL)function to count rows where no discount was applied. - E
Use the
count(*) - count(discount IS NULL)formula to calculate the number of rows with NULL values in the 'discount' column.
Show answer and explanation
Correct answers: A, B
Explanation
To count the number of rows where no discount was applied (NULL values in the 'discount' column), you can use either count_if(discount IS NULL) or the formula count(*) - count(discount). Both approaches work because the first directly counts rows satisfying the condition, while the second computes the difference between the total rows and the rows with non-NULL values. Understanding these approaches ensures accurate analysis of data containing NULLs.
- A. Correct.
Correct. The
count_if(discount IS NULL)function is specifically designed to count rows where the condition (in this case, NULL values) is satisfied. - B. Correct.
Correct. The formula
count(*) - count(discount)works becausecount(discount)excludes NULL values, so subtracting it from the total count gives the count of NULL values. - C. Incorrect.
Incorrect. The
count_if(discount IS NOT NULL)function counts rows where a value exists, not where no discount was applied. - D. Incorrect.
Incorrect. While this syntax seems logical, it is not valid because
count_if(discount IS NULL)counts the rows with NULL values, not rows where no discount was applied. - E. Incorrect.
Incorrect. This syntax is invalid because
count(discount IS NULL)is not a valid SQL aggregation function.