Scenario: You are a Data Analyst for a large online e-commerce store. Your task is to analyze customer purchase behavior.
The two tables we’ll be working with are “users” and “purchases”.
- “users” table represents users of the e-commerce platform. Each row represents a user, with columns for user_id, signup_date, and country.
- “purchases” table represents purchases made by the users. Each row represents a single purchase, with columns for purchase_id, user_id, purchase_date, and purchase_amount.
Challenge Questions
- Find the total purchase amount per country, and rank the countries based on the total purchase amount.
- Find the average time (in days) between sign-up date and the first purchase for each user.
- Find out the last purchase date for each user and label users as ‘Active’ if their last purchase was within the last 30 days from the latest date in the data, ‘Idle’ if their last purchase was between 31 and 90 days, and ‘Inactive’ if it was more than 90 days.
- Find out the highest single purchase amount for each country and the user who made that purchase.
- Calculate the 7-day running total purchase amount for each user.
- Identify the user who has the longest period of inactivity between two purchases.
- Identify users who have a total purchase amount greater than the average total purchase amount of all users.