SnowPro Associate: Platform Question 170
Single answer● Run basic SQL commandsA data engineer needs to quickly validate the contents of a Snowflake table named SALES before building a dashboard. The engineer wants to return only the unique combinations of CUSTOMER_ID and REGION for rows where ORDER_DATE is in 2024, and sort the results by REGION alphabetically. Which SQL statement will meet this requirement?
- A
SELECT UNIQUE CUSTOMER_ID, REGION FROM SALES WHERE YEAR(ORDER_DATE) = 2024 ORDER BY REGION;
- B
SELECT DISTINCT CUSTOMER_ID, REGION FROM SALES WHERE ORDER_DATE BETWEEN '2024-01-01' AND '2024-12-31' ORDER BY REGION;
- C
SELECT CUSTOMER_ID, REGION FROM SALES WHERE ORDER_DATE LIKE '2024%' GROUP BY REGION;
- D
SELECT DISTINCT CUSTOMER_ID, REGION FROM SALES HAVING ORDER_DATE >= '2024-01-01' AND ORDER_DATE < '2025-01-01' SORT BY REGION;
Show answer and explanation
Correct answer: B
Explanation
The correct answer is Option 2 because it uses standard Snowflake SQL syntax to satisfy all parts of the requirement: DISTINCT for unique combinations, WHERE to filter rows, and ORDER BY to sort the result set. This reflects a common real-world task when validating data before downstream reporting. In Snowflake, SELECT DISTINCT returns distinct row combinations across the listed columns. WHERE filters source rows before any grouping or sorting. ORDER BY controls presentation of the final result set. Option 2 also uses a practical date filter for the 2024 calendar year. Snowflake documentation for SELECT statements, DISTINCT, filtering with WHERE, and sorting with ORDER BY supports this approach.
- A. Incorrect.
Incorrect. In Snowflake, DISTINCT is the standard SQL keyword used to return unique row combinations. UNIQUE is not the correct keyword in a SELECT statement for this purpose. Although YEAR(ORDER_DATE) can be used syntactically in Snowflake, this option is invalid because of the use of UNIQUE.
- B. Correct.
Correct. SELECT DISTINCT CUSTOMER_ID, REGION returns unique combinations of those two columns. The WHERE clause filters rows to dates in 2024, and ORDER BY REGION sorts the output alphabetically by region. This is a practical and valid Snowflake SQL query for basic data validation.
- C. Incorrect.
Incorrect. This option has multiple problems. ORDER_DATE is a date column in most realistic schemas, so using LIKE against it is not appropriate for reliable date filtering. Also, GROUP BY REGION is invalid here because CUSTOMER_ID is selected but not aggregated or grouped. Even if corrected, it would not necessarily return unique CUSTOMER_ID and REGION combinations as requested.
- D. Incorrect.
Incorrect. HAVING is used to filter aggregated results after GROUP BY, not to filter base rows in place of WHERE in this scenario. In addition, SORT BY is not valid Snowflake SQL syntax; Snowflake uses ORDER BY. A correct date range filter could use >= '2024-01-01' and < '2025-01-01', but the rest of the statement makes this option incorrect.