sqladvanced15 minutes

Create a SQL Function to Calculate Customer Lifetime Value (CLV)

Write a SQL function that calculates the Customer Lifetime Value (CLV) based on customer transactions, applying discounting over time and accounting for customer churn rate.

Challenge prompt

You have a table named transactions with the following schema: transactions( customer_id INT, transaction_date DATE, amount DECIMAL(10,2) ) Write a SQL function named calculate_clv that calculates the discounted Customer Lifetime Value (CLV) for a given customer_id. The function should take these parameters: - customer_id (INT): the ID of the customer - churn_rate (DECIMAL): the monthly churn rate as a decimal (e.g., 0.05 for 5%) - discount_rate (DECIMAL): the monthly discount rate as a decimal (e.g., 0.01 for 1%) The CLV formula to apply to each transaction is: discounted_value = amount / POWER(1 + discount_rate, months_since_first_purchase) The function must: 1. Identify the first transaction date for the specified customer. 2. Compute the months elapsed since that first purchase for each transaction. 3. Calculate the discounted transaction amounts. 4. Sum the discounted amounts. 5. Apply the churn rate to estimate the expected future value beyond observed transactions, assuming a geometric series continuation with churn_rate. Return the final CLV as a DECIMAL. Example call: SELECT calculate_clv(123, 0.05, 0.01);

Guidance

• Use DATE_DIFF or equivalent to calculate months between transaction_date and first purchase.
• Apply the discount factor POWER(1 + discount_rate, months_elapsed) to each transaction amount.
• Incorporate the churn rate to extend CLV beyond observed transactions as a geometric series.

Hints

• Calculate the months_since_first_purchase as an integer count from first transaction date.
• The infinite sum for future values after observed transactions can be approximated as discounted_sum / (churn_rate + discount_rate).
• Test your function with different churn and discount rates to validate calculations.

Starter code

CREATE FUNCTION calculate_clv(
  input_customer_id INT,
  churn_rate DECIMAL(5,4),
  discount_rate DECIMAL(5,4)
) RETURNS DECIMAL(18,2) AS $$
DECLARE
  first_purchase DATE;
  clv DECIMAL(18,2) := 0;
BEGIN
  -- Your implementation here
  RETURN clv;
END;
$$ LANGUAGE plpgsql;

Expected output

A decimal value representing the calculated Customer Lifetime Value (CLV) for the given customer, e.g., 1250.75

Core concepts

window functionsdate/time calculationdiscounted cash flowuser-defined functionsgeometric series

Challenge a Friend

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