DAA-C01 Question 263
Single answerIdentify correlations among variablesA retail analytics team stores daily store-level metrics in a Snowflake table named STORE_DAY_METRICS with columns: STORE_ID, SALES_AMOUNT, FOOT_TRAFFIC, PROMO_SPEND, AVG_DISCOUNT_PCT, and METRIC_DATE. The team wants to identify which numeric variables are most strongly correlated with SALES_AMOUNT over the last 12 months, while excluding rows where any of the compared values are NULL. Which query is the most appropriate?
- A
SELECT CORR(SALES_AMOUNT, FOOT_TRAFFIC) AS corr_traffic, CORR(SALES_AMOUNT, PROMO_SPEND) AS corr_promo, CORR(SALES_AMOUNT, AVG_DISCOUNT_PCT) AS corr_discount FROM STORE_DAY_METRICS WHERE METRIC_DATE >= DATEADD(month, -12, CURRENT_DATE());
- B
SELECT SALES_AMOUNT, FOOT_TRAFFIC, PROMO_SPEND, AVG_DISCOUNT_PCT FROM STORE_DAY_METRICS WHERE METRIC_DATE >= DATEADD(month, -12, CURRENT_DATE()) ORDER BY SALES_AMOUNT DESC;
- C
SELECT COVAR_SAMP(SALES_AMOUNT, FOOT_TRAFFIC) AS corr_traffic, COVAR_SAMP(SALES_AMOUNT, PROMO_SPEND) AS corr_promo, COVAR_SAMP(SALES_AMOUNT, AVG_DISCOUNT_PCT) AS corr_discount FROM STORE_DAY_METRICS WHERE METRIC_DATE >= DATEADD(month, -12, CURRENT_DATE());
- D
SELECT REGR_SLOPE(SALES_AMOUNT, FOOT_TRAFFIC) AS corr_traffic, REGR_SLOPE(SALES_AMOUNT, PROMO_SPEND) AS corr_promo, REGR_SLOPE(SALES_AMOUNT, AVG_DISCOUNT_PCT) AS corr_discount FROM STORE_DAY_METRICS WHERE METRIC_DATE >= DATEADD(month, -12, CURRENT_DATE());
Show answer and explanation
Correct answer: A
Explanation
To identify correlations among variables in Snowflake, the most direct and appropriate function is CORR(expr1, expr2). In this scenario, the analyst needs a comparable measure of linear association between SALES_AMOUNT and several numeric variables over a defined time period. CORR produces a normalized coefficient between -1 and 1, making it suitable for ranking which variables are most strongly related to sales. By contrast, COVAR_SAMP and REGR_SLOPE measure different statistical properties and are not interchangeable with correlation for this requirement. Filtering to the last 12 months with DATEADD is also appropriate. Per Snowflake SQL function behavior, aggregate statistical functions operate on non-NULL input pairs, so rows with NULL in either compared expression are excluded from that pair's calculation. This aligns with standard best practice for pairwise correlation analysis in Snowflake.
- A. Correct.
Correct. CORR is the Snowflake aggregate function designed to measure Pearson correlation between two numeric expressions. It returns a value from -1 to 1, which allows the analyst to compare the strength and direction of the relationship between SALES_AMOUNT and each candidate driver. This is also appropriate because Snowflake aggregate functions such as CORR ignore NULL pairs when computing the statistic, which aligns with the requirement to exclude rows where either value in the pair is NULL.
- B. Incorrect.
Incorrect. Sorting and inspecting raw values does not calculate correlation and does not provide a statistically meaningful measure of linear association. A user might choose this because high sales rows can appear near high traffic or promotion rows, but visual ordering alone is not a reliable way to quantify correlation across the full dataset.
- C. Incorrect.
Incorrect. COVAR_SAMP computes sample covariance, not correlation. Although covariance can indicate whether two variables tend to move together, its magnitude depends on the scale of the variables, so it is not suitable for directly comparing the strength of association across FOOT_TRAFFIC, PROMO_SPEND, and AVG_DISCOUNT_PCT. A candidate might confuse covariance with correlation because both describe relationships between variables.
- D. Incorrect.
Incorrect. REGR_SLOPE returns the slope of a simple linear regression line, not the correlation coefficient. Slope is influenced by the units and scale of the independent variable, so it does not provide a normalized measure for comparing relationship strength across different metrics. This is a common misconception because regression outputs are often used alongside correlation analysis.