capstone_project_starbucks_6.py

query = """
        SELECT
            complete.offer_id,
            complete.offer_type,
            total_completed,
            total_completed_without_view,
            ROUND(((1.0*total_completed_without_view) / (1.0*total_completed))*100, 2) as total_completed_without_view_ratio,
            100 - ROUND(((1.0*total_completed_without_view) / (1.0*total_completed))*100, 2) as total_completed_with_view_ratio,
            `loss ($)`
        FROM
            (SELECT
                offer_id,
                offer_type,
                COUNT(*) AS total_completed
            FROM
                complete_data
            WHERE
                completed = 1
            GROUP BY
                offer_id) complete
                
        JOIN
            (SELECT
                offer_id,
                offer_type,
                COUNT(*) AS total_completed_without_view,
                SUM(reward) AS `loss ($)`
            FROM
                complete_data
            WHERE
                viewed = 0
                AND completed = 1
            GROUP BY
                offer_id) complete_not_view ON complete.offer_id = complete_not_view.offer_id
                
        ORDER BY
            total_completed_without_view_ratio DESC
        """

completed_without_view = pdsql.sqldf(query)

# Start the visualization process
viz = completed_without_view[['offer_id', 'loss ($)', 'offer_type']].set_index('offer_id') \
                                                                    .sort_values('loss ($)')

colors = tuple(np.where(viz['offer_type'] == 'discount', '#C6E5CC', '#6fb08e'))

viz['loss ($)'].plot(kind='barh',
                     color=colors,
                     figsize=(10,5))

discount = mpatches.Patch(color='#C6E5CC', label='Discount')
bogo = mpatches.Patch(color='#6fb08e', label='BOGO')

plt.legend(handles=[bogo, discount])
plt.title('Total Loss for Each Offer Id')
plt.xlabel('Loss ($)')
plt.show()