sqlintermediate15 minutes

Build a SQL Function to Find Top Customers by Total Spend

Create a SQL scalar function that calculates the top N customers based on their total purchase amount from sales data.

Challenge prompt

Write a SQL function called get_top_customers that accepts one integer parameter 'top_n'. This function should return a result set of the top 'top_n' customers sorted by their total spending in descending order. The function will use two tables: 'customers' (with columns customer_id, name) and 'orders' (with columns order_id, customer_id, order_amount). Your function should join these tables and aggregate the total spent by each customer.

Guidance

• Use JOIN to combine the customers and orders tables.
• Aggregate order_amount using SUM grouped by customer_id.
• Order the results by the total spending in descending order and limit to 'top_n'.

Hints

• Remember to use GROUP BY customer_id in your query to calculate sums correctly.
• Use ORDER BY and LIMIT clauses to restrict output to the top N customers.
• Consider using appropriate window functions if your SQL dialect supports them.

Starter code

CREATE FUNCTION get_top_customers(top_n INT)
RETURNS TABLE(
   customer_id INT,
   customer_name VARCHAR(255),
   total_spent DECIMAL(10,2)
)
AS
RETURN (
   -- Fill your query here
);

Expected output

A table listing customer_id, customer_name, and total_spent for the top N customers by total spending, ordered descending by total_spent.

Core concepts

JOINGROUP BYAGGREGATIONFUNCTION CREATION

Challenge a Friend

Send this duel to someone else and see if they can solve it.