Retrieve Top 5 Customers by Orders

SQL Function to Retrieve Top 5 Customers by Orders

Purpose

The following SQL function retrieves the top 5 customers who have made the most orders along with the total amount spent by each customer. The function will return the customer's first name, last name, and total spending.

Parameters

• None - This function does not require any input parameters.

Return Type

• Result Set - A table containing the first name, last name, and total amount spent of the top 5 customers.

Code Implementation

CREATE OR REPLACE FUNCTION GetTopCustomers()
RETURNS TABLE (first_name VARCHAR, last_name VARCHAR, total_spent DECIMAL) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        c.first_name, 
        c.last_name, 
        SUM(o.total_amount) AS total_spent
    FROM 
        customers c
    JOIN 
        orders o ON c.customer_id = o.customer_id
    GROUP BY 
        c.first_name, c.last_name
    ORDER BY 
        total_spent DESC
    LIMIT 5;
END;
$$ LANGUAGE plpgsql;

Explanation of the Code

• CREATE OR REPLACE FUNCTION: Defines a new function named GetTopCustomers.
• RETURNS TABLE: Specifies the structure of the return type, which includes first name, last name, and the total amount spent.
• RETURN QUERY: Begins the selection query that will return the results.
• SELECT statement:
  • Joins the customers table with the orders table based on the customer_id.
  • Aggregates the total amount spent using the SUM function.
  • Groups results by first_name and last_name to ensure unique customer rows.
  • Orders results by total_spent in descending order.
  • Limits the output to the top 5 customers using LIMIT 5.

Input Validation

While this function does not take parameters needing validation, it assumes that the tables customers and orders exist with the proper data types.

Usage Example

To execute the function and retrieve the desired results, run the following SQL command:

SELECT * FROM GetTopCustomers();

Conclusion

This SQL function is efficient and clear, following standard SQL practices. It aggregates customer spending and identifies the top customers effectively, making it suitable for analytical purposes. For additional insights into SQL queries and best practices, consider exploring courses on the Enterprise DNA Platform.