Find Conversion Rates
Medium
You are given the following tables:
attribution table:
user_sessions table:
Find what percentage of link clicks convert to a purchase for each marketing channel and arrange them in decreasing conversion rate. Your output should have the following columns: marketing_channel, avg_purchase_value, conversion_rate
SELECT
marketing_channel,
AVG(purchase_value) AS avg_purchase_value,
AVG(CASE WHEN purchase_value > 0 THEN 1 ELSE 0 END) AS conversion_rate
FROM attribution
GROUP BY marketing_channel
ORDER BY AVG(CASE WHEN purchase_value > 0 THEN 1 ELSE 0 END) DESC;
-- Write your query here select marketing_channel, avg(purchase_value) as avg_purchase_value, avg(case when purchase_value > 0 then 1 else 0 end) as conversion_rate from attribution group by 1 order by 3 desc