sqlintermediate10 minutes

Build a Function to Calculate User's Average Monthly Orders

Write an SQL function that calculates the average number of orders a user places per month based on an orders table with timestamps.

Challenge prompt

Create an SQL function named get_avg_monthly_orders(user_id INT) that returns the average number of orders placed by the specified user per month. Use the `orders` table which contains columns `order_id`, `user_id`, and `order_date` (DATE or TIMESTAMP). The function should consider all available order data for that user and calculate their monthly order average rounded to two decimal places.

Guidance

• Aggregate orders by month and user to get total orders per month.
• Calculate the average over the months where the user placed orders.
• Handle cases where the user has no orders by returning 0.

Hints

• Use SQL date functions like EXTRACT(YEAR FROM order_date) and EXTRACT(MONTH FROM order_date) to group by months.
• COUNT and AVG aggregate functions will be useful to calculate orders and their average.
• Consider using ROUND() to format the average value to two decimal places.

Starter code

CREATE FUNCTION get_avg_monthly_orders(user_id INT) RETURNS FLOAT AS $$
BEGIN
  -- Your code here
END;
$$ LANGUAGE plpgsql;

Expected output

For example, for user_id = 5, the function should return a float representing the average monthly orders, e.g. 3.42

Core concepts

aggregationsdate functionsuser-defined functions

Challenge a Friend

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