You need to populate the CustomersWithProductScore table. How should you complete the stored procedure? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is…

Question

Explanation

This question tests your ability to write or complete a T-SQL stored procedure that calculates and persists a product score per customer into a staging or summary table. The core skill is correctly constructing the INSERT...SELECT pattern (or MERGE) with appropriate aggregation or scoring logic.

Approach. The correct approach is to use an INSERT INTO ... SELECT statement inside the stored procedure, joining the relevant source tables (e.g., Customers, Orders, Products) and applying aggregate or window functions (such as SUM, AVG, or a weighted expression) to derive the product score per customer. You should TRUNCATE or DELETE the target table first if this is a full refresh, or use a MERGE statement if you need upsert semantics. Proper use of GROUP BY is required when collapsing rows to one score per customer, and aliasing computed columns to match the target table's schema is essential for the INSERT to succeed.

Concept tested. Writing stored procedures in T-SQL to populate summary/aggregate tables - specifically the INSERT INTO...SELECT pattern, use of aggregate/window functions for scoring, and table refresh strategies (TRUNCATE + INSERT vs. MERGE).

Reference. Microsoft Docs: CREATE PROCEDURE (T-SQL), INSERT (T-SQL), MERGE (T-SQL) - docs.microsoft.com/sql/t-sql/statements

No community discussion yet for this question.