DAA-C01 Question 47
Single answerPerform table joins between parent/child tablesA retail analytics team stores customer orders in a parent table ORDERS and line-item details in a child table ORDER_ITEMS. Analysts need a query that returns every order placed in the last 30 days, including orders that do not yet have any line items because ingestion of child records can be delayed. For each order, they want the total quantity ordered, showing 0 when no child rows exist yet. Which SQL approach best meets this requirement?
- A
Select from ORDERS o LEFT JOIN ORDER_ITEMS i ON o.ORDER_ID = i.ORDER_ID, filter ORDERS to the last 30 days, and aggregate with SUM(COALESCE(i.QUANTITY, 0)) grouped by order columns.
- B
Select from ORDERS o INNER JOIN ORDER_ITEMS i ON o.ORDER_ID = i.ORDER_ID, filter ORDERS to the last 30 days, and aggregate with SUM(i.QUANTITY) grouped by order columns.
- C
Select from ORDER_ITEMS i RIGHT JOIN ORDERS o ON o.ORDER_ID = i.ORDER_ID, filter ORDER_ITEMS to the last 30 days, and aggregate with SUM(i.QUANTITY) grouped by order columns.
- D
Select from ORDERS o CROSS JOIN ORDER_ITEMS i, filter ORDERS to the last 30 days and ORDER_ID equality in the WHERE clause, then aggregate with SUM(i.QUANTITY).
Show answer and explanation
Correct answer: A
Explanation
When joining parent and child tables in Snowflake, the key design choice is whether unmatched parent rows must be retained. In this scenario, ORDERS is the parent and ORDER_ITEMS is the child, and the requirement explicitly states that orders with delayed child ingestion must still appear. That makes a LEFT OUTER JOIN from parent to child the correct approach. To display 0 instead of NULL for missing child data, analysts typically use COALESCE (or a similar null-handling function) around the child measure before or after aggregation, depending on the expression. Filtering should be applied to the parent table's order date because the requested population is 'every order placed in the last 30 days,' not 'every line item created in the last 30 days.' This aligns with standard SQL join semantics documented by Snowflake for INNER, LEFT, RIGHT, and CROSS joins, and with common analytics best practices for preserving parent-level completeness in parent/child models.
- A. Correct.
Correct. A LEFT OUTER JOIN from the parent table ORDERS to the child table ORDER_ITEMS preserves all qualifying parent rows even when no matching child rows exist. Applying the 30-day filter to ORDERS ensures the required set of parent orders is returned. Using SUM(COALESCE(i.QUANTITY, 0)) supports returning 0 for unmatched child rows rather than NULL. This is the standard pattern when analysts need complete parent coverage with optional child detail.
- B. Incorrect.
Incorrect. An INNER JOIN returns only orders that have at least one matching line item. That would exclude valid parent rows whose child rows have not yet arrived, which directly violates the requirement to include every order placed in the last 30 days.
- C. Incorrect.
Incorrect. Although a RIGHT JOIN can be logically equivalent to a LEFT JOIN if written carefully, this option filters the child table ORDER_ITEMS to the last 30 days rather than filtering the parent ORDERS table. That changes the result set and can exclude orders based on child-row timing instead of order date, which is not the business requirement.
- D. Incorrect.
Incorrect. A CROSS JOIN followed by a join predicate in the WHERE clause behaves like an inner join in this pattern, so orders without matching line items would still be dropped. It is also less clear and is not the best-practice way to express a parent/child join.