Hone logo
Hone
Problems

Creating a Customer Order Summary View

You are tasked with creating a reusable data abstraction for your company's customer service team. This team frequently needs to see a summary of each customer's total order value and the number of orders they have placed. Creating a view will allow them to query this summarized data easily without needing to write complex JOIN and GROUP BY clauses every time.

Problem Description

Your goal is to create a SQL VIEW named CustomerOrderSummary. This view should display the following information for each customer:

  • CustomerID: The unique identifier for the customer.
  • TotalOrderValue: The sum of the Amount from all orders placed by that customer.
  • NumberOfOrders: The total count of orders placed by that customer.

The view should be based on two tables:

  • Customers: Contains customer information, including CustomerID and CustomerName.
  • Orders: Contains order details, including OrderID, CustomerID, and Amount.

Key Requirements:

  • Use the CREATE VIEW SQL statement.
  • The view should join the Customers and Orders tables on CustomerID.
  • The view should group the results by CustomerID to aggregate order information per customer.
  • The view should calculate the sum of Amount for each customer, aliased as TotalOrderValue.
  • The view should count the number of orders for each customer, aliased as NumberOfOrders.
  • The view should include the CustomerID in the output.

Expected Behavior:

When a SELECT * FROM CustomerOrderSummary query is executed, it should return a result set containing CustomerID, TotalOrderValue, and NumberOfOrders for every customer who has placed at least one order. Customers with no orders should not appear in the view.

Edge Cases to Consider:

  • Customers who have not placed any orders should not be included in the CustomerOrderSummary view.

Examples

Example 1:

Input Tables:

Customers Table:

CustomerIDCustomerName
101Alice Smith
102Bob Johnson
103Charlie Brown

Orders Table:

OrderIDCustomerIDAmount
1101150.00
2102200.50
310175.25
410350.00
5101100.00

Output View (if queried with SELECT * FROM CustomerOrderSummary):

CustomerIDTotalOrderValueNumberOfOrders
101325.253
102200.501
10350.001

Explanation:

  • Customer 101 has 3 orders with amounts 150.00, 75.25, and 100.00, summing to 325.25.
  • Customer 102 has 1 order with an amount of 200.50.
  • Customer 103 has 1 order with an amount of 50.00.
  • Customer 103, who exists in the Customers table but has no entries in the Orders table, is not included in the output.

Example 2:

Input Tables:

Customers Table:

CustomerIDCustomerName
201David Lee
202Eve Adams

Orders Table:

OrderIDCustomerIDAmount
6201300.00

Output View (if queried with SELECT * FROM CustomerOrderSummary):

CustomerIDTotalOrderValueNumberOfOrders
201300.001

Explanation:

  • Customer 201 has one order totaling 300.00.
  • Customer 202 has no orders and therefore is not present in the summary.

Constraints

  • The Customers table contains at least 1 customer record.
  • The Orders table may be empty or contain up to 10,000 order records.
  • The Amount in the Orders table will be a non-negative numeric value.
  • CustomerID will be a unique identifier in the Customers table and will be used as a foreign key in the Orders table.
  • The CREATE VIEW statement must be valid SQL.

Notes

  • Consider the appropriate SQL aggregate functions for summing amounts and counting records.
  • Ensure your JOIN condition correctly links customers to their orders.
  • The GROUP BY clause is crucial for aggregating results per customer.
  • The problem statement implicitly requires an INNER JOIN or equivalent logic to exclude customers without orders from the view.
Loading editor...
plaintext