Create a SQL SELECT statement using a basic CTE(s) on the WorldWideImportersDW database.

Your query needs to have the following:

Easy to read

Could be a useful report/data feed

Contains an INNER JOIN, GROUP BY and an aggregate function

ALSO Find a case for and write a query with a recursive CTE for one of your databases.

BELOW IS THE TABLES/COLUMNS FROM WIDEWORLDIMPORTERSDW. PLEASE REFER TO THOSE WHILE ANSWERING

1.dimension.city City City Key Continent Country Latest Recorded Population Lineage Key Location Region Sales Territory State Province Subregion Valid From Valid To WWI City ID 2. dimension.customer Bill To Customer Buying Group Category Customer Customer Key Lineage Key Postal Code Primary Contact Valid From Valid To WWI Customer ID 3. dimension.date Calendar Month Label Calendar Month Number Calendar Year Calendar Year Label Date Day Day Number Fiscal Month Label Fiscal Month Number

4.dimension.employee Employee Employee Key Is Salesperson Lineage Key Photo Preferred Name Valid From Valid To WWI Employee ID 5.dimension.payment method Lineage Key Payment Method Payment Method Key Valid From Valid To WWI Payment Method ID 6.dimension.stock item Barcode Brand Buying Package Color Is Chiller Stock Lead Time Days Lineage Key Photo Quantity Per Outer Recommended Retail Price Selling Package Size Stock Item Stock Item Key Tax Rate Typical Weight Per Unit Unit Price Valid From Valid To WWI Stock Item ID

7.dimension.supplier Category Lineage Key Payment Days Postal Code Primary Contact Supplier Supplier Key Supplier Reference Valid From Valid To WWI Supplier ID 8.transaction type Lineage Key Transaction Type Transaction Type Key Valid From Valid To WWI Transaction Type ID 9. fact.movement Customer Key Date Key Lineage Key Movement Key Quantity Stock Item Key Supplier Key Transaction Type Key WWI Invoice ID WWI Purchase Order ID WWI Stock Item Transaction ID 11.fact.purchase Date Key Is Order Finalized Lineage Key Ordered Outers Ordered Quantity Package Purchase Key Received Outers Stock Item Key Supplier Key WWI Purchase Order ID

10.fact.order City Key Customer Key Description Lineage Key Order Date Key Order Key Package Picked Date Key Picker Key Quantity Salesperson Key Stock Item Key Tax Amount Tax Rate Total Excluding Tax Total Including Tax Unit Price WWI Backorder ID WWI Order ID 12. fact.sale Bill To Customer Key City Key Customer Key Delivery Date Key Description Invoice Date Key Lineage Key Package Profit Quantity Sale Key Salesperson Key Stock Item Key Tax Amount Tax Rate Total Chiller Items Total Dry Items Total Excluding Tax Total Including Tax Unit Price WWI Invoice ID 18.integration.etl cutoff Cutoff Time Table Name

13.fact.stock holding Bin Location Last Cost Price Last Stocktake Quantity Lineage Key Quantity On Hand Reorder Level Stock Holding Key Stock Item Key Target Stock Level 14.fact.transaction Bill To Customer Key Customer Key Date Key Is Finalized Lineage Key Outstanding Balance Payment Method Key Supplier Invoice Number Supplier Key Tax Amount Total Excluding Tax Total Including Tax Transaction Key Transaction Type Key WWI Customer Transaction ID WWI Invoice ID WWI Purchase Order ID WWI Supplier Transaction ID 15.integration.city_staging City City Staging Key Continent Country Latest Recorded Population Location Region Sales Territory State Province Subregion Valid From Valid To WWI City ID

16. integration.customer_staging Bill To Customer Buying Group Category Customer Customer Staging Key Postal Code Primary Contact Valid From Valid To WWI Customer ID 17. integration.employee_staging Employee Employee Staging Key Is Salesperson Photo Preferred Name Valid From Valid To WWI Employee ID 19. . integration.lineage Data Load Completed Data Load Started Lineage Key Source System Cutoff Time Table Name Was Successful 20. integration.movement_staging Customer Key Date Key Last Modifed When Movement Staging Key Quantity Stock Item Key Supplier Key Transaction Type Key WWI Customer ID WWI Invoice ID WWI Purchase Order ID WWI Stock Item ID WWI Stock Item Transaction ID WWI Supplier ID WWI Transaction Type ID 22.integration.stockitem_staging Barcode Brand Buying Package Color Is Chiller Stock Lead Time Days Photo Quantity Per Outer Recommended Retail Price Selling Package Size Stock Item Stock Item Staging Key Tax Rate Typical Weight Per Unit Unit Price Valid From Valid To WWI Stock Item ID 23. integration.supplier_staging Category Payment Days Postal Code Primary Contact Supplier Supplier Reference Supplier Staging Key Valid From Valid To WWI Supplier ID

17.integration.order_staging City Key Customer Key Description Last Modified When Lineage Key Order Date Key Order Staging Key Package Picked Date Key Picker Key Quantity Salesperson Key Stock Item Key Tax Amount Tax Rate Total Excluding Tax Total Including Tax Unit Price WWI Backorder ID WWI City ID WWI Customer ID WWI Order ID WWI Picker ID WWI Salesperson ID WWI Stock Item ID 18. integration.paymentMethod_staging Payment Method Payment Method Staging Key Valid From Valid To WWI Payment Method ID 21.. integration.stockHolding_staging Bin Location Last Cost Price Last Stocktake Quantity Quantity On Hand Reorder Level Stock Holding Staging Key Stock Item Key Target Stock Level WWI Stock Item ID 24.integration.transaction_staging Bill To Customer Key Customer Key Date Key Is Finalized Last Modified When Outstanding Balance Payment Method Key Supplier Invoice Number Supplier Key Tax Amount Total Excluding Tax Total Including Tax Transaction Staging Key Transaction Type Key WWI Bill To Customer ID WWI Customer ID WWI Customer Transaction ID WWI Invoice ID WWI Payment Method ID WWI Purchase Order ID WWI Supplier ID WWI Supplier Transaction ID WWI Transaction Type ID 25. integration.transactionType_staging Transaction Type Transaction Type Staging Key Valid From Valid To WWI Transaction Type ID

19..integration.purchase_staging Date Key Is Order Finalized Last Modified When Ordered Outers Ordered Quantity Package Purchase Staging Key Received Outers Stock Item Key Supplier Key WWI Purchase Order ID WWI Stock Item ID WWI Supplier ID 20.integration.sale_staging Bill To Customer Key City Key Customer Key Delivery Date Key Description Invoice Date Key Last Modified When Package Profit Quantity Sale Staging Key Salesperson Key Stock Item Key Tax Amount Tax Rate Total Chiller Items Total Dry Items Total Excluding Tax Total Including Tax Unit Price WWI Bill To Customer ID WWI City ID WWI Customer ID WWI Invoice ID WWI Salesperson ID WWI Stock Item ID