DAA-C01 Question 157
Single answerUse cartesian joins, sub-queries, CTEs, and union queriesA retail analytics team needs a daily report that shows every product-store combination, even when no sales occurred, so analysts can identify coverage gaps. The source tables are:
- PRODUCTS(PRODUCT_ID, CATEGORY)
- STORES(STORE_ID, REGION)
- SALES_DAILY(SALES_DATE, PRODUCT_ID, STORE_ID, UNITS_SOLD)
- RETURNS_DAILY(RETURN_DATE, PRODUCT_ID, STORE_ID, UNITS_RETURNED)
The team wants a query for a single reporting date that:
- generates all possible product-store combinations,
- combines sales and returns into one daily activity set without removing legitimate duplicate rows,
- aggregates the activity by product-store,
- returns combinations with zero activity as 0.
Which approach best satisfies the requirement in Snowflake?
- A
Use a CTE to CROSS JOIN PRODUCTS and STORES, use another CTE to UNION ALL sales and returns for the reporting date into a normalized activity set, aggregate that activity by PRODUCT_ID and STORE_ID, then LEFT JOIN the aggregate back to the product-store combinations and use COALESCE for missing activity.
- B
Use an INNER JOIN between PRODUCTS and STORES on PRODUCT_ID = STORE_ID, UNION sales and returns, then filter out NULL totals after aggregation.
- C
Use a subquery that UNIONs sales and returns with UNION instead of UNION ALL, CROSS JOIN the result to PRODUCTS and STORES, and aggregate after the join to ensure all combinations are preserved.
- D
Use a CTE to CROSS JOIN PRODUCTS and STORES, then RIGHT JOIN SALES_DAILY for the reporting date and ignore RETURNS_DAILY because returns can be derived from negative sales values.
- E
Use correlated subqueries in the SELECT list against SALES_DAILY and RETURNS_DAILY for each product-store pair, and combine them with UNION so that duplicate daily activity rows are eliminated before summing.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to separate the problem into logical stages using CTEs: first generate the complete product-store matrix with a CROSS JOIN, then build a normalized activity set for the reporting date from SALES_DAILY and RETURNS_DAILY using UNION ALL, aggregate that activity by PRODUCT_ID and STORE_ID, and finally LEFT JOIN the aggregate to the full matrix. This pattern is common in analytics when a report must include zero-activity combinations.
Key Snowflake SQL concepts being tested:
- Cartesian joins: CROSS JOIN is the correct way to intentionally generate every combination of rows from two tables.
- CTEs: useful for readability and for organizing multi-step transformations.
- UNION ALL vs UNION: UNION ALL preserves all rows, while UNION performs duplicate elimination. When combining transactional data where duplicates may be valid, UNION ALL is usually the right choice.
- Outer joins and NULL handling: LEFT JOIN preserves the full dimensional set, and COALESCE converts missing aggregate values to 0 for reporting.
This aligns with standard SQL and Snowflake best practices for constructing reporting datasets: aggregate facts before joining back to a dense dimensional framework, and use UNION ALL unless deduplication is explicitly required.
- A. Correct.
Correct. This approach matches all stated requirements. A CROSS JOIN between PRODUCTS and STORES generates the full cartesian set of product-store pairs. Using CTEs keeps the logic modular and readable. UNION ALL is appropriate because the requirement explicitly says not to remove legitimate duplicate rows; UNION would de-duplicate matching rows across the two inputs. Aggregating the normalized activity before joining avoids inflating counts from the cartesian set. Finally, a LEFT JOIN back to the full combination set preserves zero-activity combinations, and COALESCE converts NULL aggregates to 0.
- B. Incorrect.
Incorrect. An INNER JOIN between PRODUCTS and STORES on PRODUCT_ID = STORE_ID is not a valid business relationship for generating all product-store combinations; it would only return accidental key matches, not the cartesian set. Also, filtering out NULL totals contradicts the need to keep zero-activity combinations. This distractor targets the misconception that any join between dimension tables can replace a CROSS JOIN for matrix generation.
- C. Incorrect.
Incorrect. UNION removes duplicate rows, which violates the requirement to retain legitimate duplicate daily activity records. Also, CROSS JOINing the activity result to PRODUCTS and STORES would create massive row multiplication and incorrect aggregates unless carefully constrained. The correct pattern is to create the full product-store matrix once, aggregate activity separately, and then join the aggregate back.
- D. Incorrect.
Incorrect. While the initial CROSS JOIN is aligned with generating all combinations, RIGHT JOINing only SALES_DAILY would exclude combinations without sales unless the join direction and preservation logic were carefully reversed, and it still ignores RETURNS_DAILY entirely. Returns cannot be assumed to exist as negative sales unless the source model explicitly stores them that way, which this scenario does not indicate.
- E. Incorrect.
Incorrect. Correlated subqueries can sometimes produce the needed totals, but using UNION to combine results would de-duplicate rows and risk undercounting. In addition, per-row correlated subqueries are less clear and often less maintainable than building an aggregated activity set in a CTE and joining once. This option also confuses combining scalar subquery results with combining transactional rows.