DAA-C01 Question 231
Single answerCreate and run SQL queriesA data analyst needs to build a monthly sales report in Snowflake. The source table SALES_RAW contains one row per transaction with the columns ORDER_ID, ORDER_TS, REGION, and AMOUNT. The analyst wants the report to show, for each region and month, the total sales and the previous month's total sales for that same region. The output must include only the last 12 complete calendar months and should avoid self-joins if possible for maintainability and performance. Which SQL query best meets these requirements?
- A
SELECT REGION, DATE_TRUNC('MONTH', ORDER_TS) AS SALES_MONTH, SUM(AMOUNT) AS TOTAL_SALES, LAG(SUM(AMOUNT)) OVER (PARTITION BY REGION ORDER BY DATE_TRUNC('MONTH', ORDER_TS)) AS PREV_MONTH_SALES FROM SALES_RAW WHERE ORDER_TS >= DATEADD(MONTH, -12, CURRENT_DATE()) GROUP BY REGION, DATE_TRUNC('MONTH', ORDER_TS);
- B
WITH MONTHLY_SALES AS ( SELECT REGION, DATE_TRUNC('MONTH', ORDER_TS) AS SALES_MONTH, SUM(AMOUNT) AS TOTAL_SALES FROM SALES_RAW WHERE ORDER_TS >= DATE_TRUNC('MONTH', DATEADD(MONTH, -12, CURRENT_DATE())) AND ORDER_TS < DATE_TRUNC('MONTH', CURRENT_DATE()) GROUP BY REGION, DATE_TRUNC('MONTH', ORDER_TS) ) SELECT REGION, SALES_MONTH, TOTAL_SALES, LAG(TOTAL_SALES) OVER (PARTITION BY REGION ORDER BY SALES_MONTH) AS PREV_MONTH_SALES FROM MONTHLY_SALES ORDER BY REGION, SALES_MONTH;
- C
SELECT REGION, MONTH(ORDER_TS) AS SALES_MONTH, SUM(AMOUNT) AS TOTAL_SALES, LAG(SUM(AMOUNT)) OVER (ORDER BY MONTH(ORDER_TS)) AS PREV_MONTH_SALES FROM SALES_RAW WHERE ORDER_TS BETWEEN DATEADD(MONTH, -12, CURRENT_DATE()) AND CURRENT_DATE() GROUP BY REGION, MONTH(ORDER_TS);
- D
WITH MONTHLY_SALES AS ( SELECT REGION, DATE_TRUNC('MONTH', ORDER_TS) AS SALES_MONTH, SUM(AMOUNT) AS TOTAL_SALES FROM SALES_RAW GROUP BY REGION, DATE_TRUNC('MONTH', ORDER_TS) ) SELECT A.REGION, A.SALES_MONTH, A.TOTAL_SALES, B.TOTAL_SALES AS PREV_MONTH_SALES FROM MONTHLY_SALES A LEFT JOIN MONTHLY_SALES B ON A.REGION = B.REGION AND A.SALES_MONTH = DATEADD(MONTH, 1, B.SALES_MONTH) WHERE A.SALES_MONTH >= DATEADD(MONTH, -12, CURRENT_DATE()) ORDER BY A.REGION, A.SALES_MONTH;
Show answer and explanation
Correct answer: B
Explanation
The best solution is to aggregate to the required reporting grain first and then apply a window function. In Snowflake, LAG is a standard analytic function used to access a prior row within a partition, making it ideal for month-over-month reporting by region. The query should partition by REGION and order by the truncated month value so that the previous month's total is calculated independently for each region. For time filtering, using DATE_TRUNC('MONTH', ...) boundaries is a best practice when the requirement is based on complete calendar months. Specifically, filtering from DATE_TRUNC('MONTH', DATEADD(MONTH, -12, CURRENT_DATE())) up to DATE_TRUNC('MONTH', CURRENT_DATE()) returns exactly the last 12 full months and excludes the current partial month. This approach aligns with Snowflake SQL best practices for analytic functions, date handling, and creating maintainable reporting queries.
- A. Incorrect.
This option is close, but it does not correctly guarantee the last 12 complete calendar months because the filter starts at DATEADD(MONTH, -12, CURRENT_DATE()), which is a rolling date boundary rather than the start of a month. It also attempts to use LAG(SUM(AMOUNT)) directly in the same SELECT as the aggregation. In Snowflake, the clearer and more maintainable pattern is to aggregate in a subquery or CTE first, then apply the window function to the aggregated result set.
- B. Correct.
This is correct. It first aggregates transactions to the month and region level in a CTE, which is the proper grain for the report. It then uses LAG(TOTAL_SALES) OVER (PARTITION BY REGION ORDER BY SALES_MONTH) to retrieve the previous month's sales within each region without a self-join. The date filter includes rows from the first day of the month 12 months ago up to, but not including, the first day of the current month, which correctly returns the last 12 complete calendar months.
- C. Incorrect.
This option is incorrect for several reasons. Using MONTH(ORDER_TS) loses the year, so January from different years would be grouped together incorrectly. The window function is not partitioned by REGION, so previous-month values could be taken from another region. The BETWEEN filter also includes the current partial month, which violates the requirement for complete calendar months only.
- D. Incorrect.
This option can produce previous-month values by self-joining the monthly aggregate, but it does not best meet the requirements. The scenario explicitly prefers avoiding self-joins for maintainability and performance when possible, and a window function is more appropriate here. In addition, the WHERE clause compares A.SALES_MONTH to DATEADD(MONTH, -12, CURRENT_DATE()), which is again a rolling date cutoff and may include part of the current month or exclude the intended month boundary depending on the current date.