sqlbeginner10 minutes

Create a Function to Calculate the Total Sales for a Given Product

Write a SQL function that takes a product ID as input and returns the total sales amount for that product from the sales table.

Challenge prompt

You have a table named sales with columns: product_id (integer), quantity_sold (integer), and price_per_unit (decimal). Write a SQL function named get_total_sales that takes a product ID as input and returns the total sales amount for that product. The total sales amount is calculated by summing the product of quantity_sold and price_per_unit for all matching product IDs.

Guidance

• Define a SQL function with one input parameter for the product ID.
• Use an aggregate function to calculate the total sales amount within the function.
• Return the calculated total as the function's output.

Hints

• Use SUM(quantity_sold * price_per_unit) to calculate total sales.
• Filter the sales table using a WHERE clause on product_id.
• Remember to specify the return type of your function as numeric or decimal.

Starter code

CREATE FUNCTION get_total_sales(p_product_id INT) RETURNS DECIMAL AS $$
BEGIN
    -- Your code goes here
END;
$$ LANGUAGE plpgsql;

Expected output

For product_id = 101, if the sales records are: | product_id | quantity_sold | price_per_unit | |------------|---------------|----------------| | 101 | 3 | 10.00 | | 101 | 2 | 10.00 | The function should return: 50.00

Core concepts

SQL functionsAggregate functionsBasic SELECT queries

Challenge a Friend

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