Microsoft Certified: Fabric Analytics Engineer AssociateExplore and analyze data (15-20%)Hard
A data analyst needs to query a Delta Lake table named `customer_transactions` in Microsoft Fabric. The table includes `transaction_id` (string), `customer_id` (int), `transaction_date` (date), and `amount` (decimal). They need to retrieve all transactions that occurred on weekends (Saturday or Sunday). Which SQL `WHERE` clause condition should be used?
- AWHERE DAYOFWEEK(transaction_date) IN (1, 7)
- BWHERE DATE_PART('dow', transaction_date) IN (0, 6)
- CWHERE DAYOFWEEK(transaction_date) IN (6, 7)
- DWHERE WEEKDAY(transaction_date) IN (6, 7)
Show answer & explanationAnswer & explanation
Correct answer: A. WHERE DAYOFWEEK(transaction_date) IN (1, 7)
In Spark SQL (and by extension in Microsoft Fabric's SQL endpoint for Delta tables), `DAYOFWEEK()` returns the day of the week as an integer, where 1 is Sunday and 7 is Saturday. Therefore, `IN (1, 7)` correctly identifies weekends.
Why the other options are wrong
- B. While `DATE_PART('dow', ...)` is a common function in some SQL dialects, in Spark SQL `DAYOFWEEK()` is the direct function. Also, `dow` typically maps Sunday to 0 or 1, and Saturday to 6 or 7, but the specific values (0, 6) might not align with Spark's `DAYOFWEEK` (1, 7) or could be ambiguous without knowing the specific `DATE_PART` implementation in Spark.
- C. This would identify Friday (6) and Saturday (7), which is incorrect for typical weekend definitions (Saturday and Sunday).
- D. `WEEKDAY()` is not a standard Spark SQL function for this purpose, and its behavior (if it existed) might vary. `DAYOFWEEK` is the correct function.
Spark SQL DAYOFWEEK()
The `DAYOFWEEK()` function in Spark SQL extracts the day of the week from a date or timestamp expression, returning an integer value.
- Returns an integer from 1 to 7.
- 1 represents Sunday.
- 7 represents Saturday.
- Commonly used for filtering or grouping by day of the week.
Memory trick: Day of week, 1 is Sunday, 7 is Saturday, remember the start and end!