Find Conversion Rates

You are given the following tables:

Sample Data

attribution table:

user_sessions table:

Examples

Output:

marketing_channel | avg_purchase_value | conversion_rate
------------------|--------------------|-----------------
paid_search       | 60.00              | 50.00%
email             | 46.66              | 66.67%
social_media      | 17.50              | 50.00%

Explanation:

• email had 3 sessions. 2 resulted in a purchase (s001, s007). Conversion rate = 2/3 = 66.67%. Avg purchase value = (49.99 + 0 + 89.99) / 3 = 46.66
• paid_search had 2 sessions. 1 resulted in a purchase (s004). Conversion rate = 1/2 = 50.00%. Avg purchase value = (120.00 + 0) / 2 = 60.00
• social_media had 2 sessions. 1 resulted in a purchase (s005). Conversion rate = 1/2 = 50.00%. Avg purchase value = (35.00 + 0) / 2 = 17.50

Results are ordered by descending conversion rate.

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