capstone_project_starbucks_9.py

query = """
        SELECT
            complete.offer_type,
            complete.complete_without_view_income,
            complete_view.complete_with_view_income
            
        FROM
            (SELECT
                offer_type,
                AVG(income) complete_without_view_income
            FROM
                complete_data
            WHERE
                viewed = 0
                AND completed = 1
            GROUP BY
                offer_type) complete
        
        JOIN
            (SELECT
                offer_type,
                AVG(income) complete_with_view_income
            FROM
                complete_data
            WHERE
                viewed = 1
                AND completed = 1
            GROUP BY
                offer_type) complete_view ON complete.offer_type = complete_view.offer_type
        """

income_differences = pdsql.sqldf(query)

# Start the visualization
plt.figure(figsize=(12,5))
sns.barplot(data=income_differences.melt(id_vars='offer_type'), 
            y='offer_type', 
            x='value', 
            hue='variable',
            palette=sns.color_palette("ch:2.5,-.2,dark=.05"))

plt.legend(loc="upper left", bbox_to_anchor=(1,1.02))
plt.title('Average Income per Completeness and Offer Type')
plt.xlabel('average income ($)')
plt.show()