Microsoft Certified: Fabric Analytics Engineer AssociateExplore and analyze data (15-20%)Medium
A data analyst needs to query a Delta Lake table named `sales_data` in Microsoft Fabric using SQL. The table contains a `transaction_date` column and a `revenue` column. They want to calculate the total `revenue` for each month in 2023. Which SQL query should they use?
- ASELECT FORMAT(transaction_date, 'yyyy-MM') AS sales_month, SUM(revenue) AS total_revenue FROM sales_data WHERE YEAR(transaction_date) = 2023 GROUP BY FORMAT(transaction_date, 'yyyy-MM') ORDER BY sales_month;
- BSELECT MONTH(transaction_date) AS sales_month, SUM(revenue) AS total_revenue FROM sales_data WHERE YEAR(transaction_date) = 2023 GROUP BY MONTH(transaction_date) ORDER BY sales_month;
- CSELECT EXTRACT(MONTH FROM transaction_date) AS sales_month, SUM(revenue) AS total_revenue FROM sales_data WHERE EXTRACT(YEAR FROM transaction_date) = 2023 GROUP BY EXTRACT(MONTH FROM transaction_date) ORDER BY sales_month;
- DSELECT DATE_TRUNC('month', transaction_date) AS sales_month, SUM(revenue) AS total_revenue FROM sales_data WHERE YEAR(transaction_date) = 2023 GROUP BY DATE_TRUNC('month', transaction_date) ORDER BY sales_month;
Show answer & explanationAnswer & explanation
Correct answer: C. SELECT EXTRACT(MONTH FROM transaction_date) AS sales_month, SUM(revenue) AS total_revenue FROM sales_data WHERE EXTRACT(YEAR FROM transaction_date) = 2023 GROUP BY EXTRACT(MONTH FROM transaction_date) ORDER BY sales_month;
The `EXTRACT(part FROM date)` function is a standard SQL function supported by Spark SQL (and thus Fabric Lakehouse SQL endpoint) for extracting parts of a date. It's used consistently for both filtering by year and grouping by month.
Why the other options are wrong
- A. `FORMAT()` is a formatting function, not typically used for grouping as it returns a string representation, which can be less efficient and potentially problematic for ordering compared to numerical month extraction. `YEAR()` is again an issue.
- B. `MONTH()` is a common SQL function but `YEAR()` is not standard SQL and often not directly supported in Spark SQL for filtering. `EXTRACT(YEAR FROM ...)` is preferred.
- D. `DATE_TRUNC()` is a valid function for truncating dates, but `YEAR()` in the WHERE clause is problematic. Grouping by `DATE_TRUNC('month', ...)` would also return full timestamps, not just month numbers, which might not be the desired output 'sales_month'.
Spark SQL Date Part Extraction
The `EXTRACT(part FROM date)` function in Spark SQL is used to retrieve specific components (e.g., year, month, day) from a date or timestamp expression.
- Standard SQL syntax for date part extraction.
- Supported by Spark SQL queries in Fabric Lakehouse.
- Useful for filtering and grouping by date components.
- Returns an integer for the specified date part.
Memory trick: Extract the parts, Truncate for periods, Format for display.