Write a SELECT statement that uses the OVER clause to return the sum and average of all order items final prices (ItemPrice minus DiscountAmount) and a count of all the order items. Partition the OVER clause by OrderDate from the Orders table for the sum, average, and count. Also include the product name, orderdate, and the final price for each item (without aggregation) using the formula (ItemPrice minus DiscountAmount). The select statement should include the following columns in the order below: The ProductName column from the Products table.
The OrderDate column from the Orders table.
A calculated column with alias "ItemSalesPrice" which is calculated as follows: ItemPrice minus the DiscountAmount from the OrderItems table.
OVER clause: sum of the ItemPrice minus the DiscountAmount from the OrderItems table partitioned by the OrderDate from the Orders table. Use alias "TotalSalesOnDate" for the calculated column.
OVER clause: average of the ItemPrice minus the DiscountAmount from the OrderItems table partitioned by the OrderDate from the Orders table. Use alias "AvgSalesOnDate" for the calculated column.
OVER clause: count of the ItemPrice from the OrderItems table partitioned by the OrderDate from the Orders table. Use alias "TotalItemsSoldOnDate" for the calculated column.
Sort the result set in ascending sequence by OrderDate and ProductName.