Find Customer Lifetime Value (LTV)
MediumPremium
You are given the following tables:
attribution table:
user_sessions table:
Find the customer lifetime value (LTV) for each user and order the results from most valuable customer to least valuable. Your output should contain the following columns: user_id, LTV
You can find the customer lifetime value of a user by taking the sum of all the purchases made by that user
SELECT
u.user_id,
SUM(a.purchase_value) AS LTV
FROM user_sessions AS u
INNER JOIN attribution AS a
ON a.session_id = u.session_id
GROUP BY u.user_id
ORDER BY SUM(a.purchase_value) DESC;
-- LTV = Sum of all purchases made by that user -- order the results by desc on LTV select u.user_id, sum(a.purchase_value) as LTV from user_sessions u join attribution a on u.session_id = a.session_id group by u.user_id order by sum(a.purchase_value) desc