Analyze Monthly Prime vs Non-Prime Sales and Price Buckets
Company: Amazon
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
sales
+-----------+------------+----------+-------+
| order_id | order_date | is_prime | price |
+-----------+------------+----------+-------+
| 1001 | 2023-01-15 | 1 | 25.99 |
| 1002 | 2023-01-20 | 0 | 18.50 |
| 1003 | 2023-02-05 | 1 | 45.00 |
| 1004 | 2023-02-14 | 0 | 12.99 |
| 1005 | 2023-03-03 | 1 | 33.25 |
+-----------+------------+----------+-------+
##### Scenario
E-commerce analytics team needs monthly insights on Prime vs non-Prime sales and price-bucket performance over the last year.
##### Question
Write an SQL query that returns, for each of the past 12 months, total sales split into Prime and non-Prime customers. Using Python (pandas), compute for each month the percentage of sales contributed by each dynamically defined price_bucket (you decide reasonable bucket boundaries).
##### Hints
Use month truncation, GROUP BY, window functions; create bins with pd.cut; ensure percentages sum to 100% per month.
Overview: This question evaluates data manipulation and analytics competencies, including SQL time-based aggregation and segmentation as well as practical use of Python (pandas) for dynamic price-bucketing and percentage calculations.
Monthly Prime vs Non-Prime Sales
Write an SQL query that returns monthly total revenue split into Prime and non-Prime customers for January through March 2023. Include all months in this range even if there are no orders (show 0 for those months). Output columns: month (first day of month), prime_sales, non_prime_sales.
Tables
sales(order_id INTEGER, order_date DATE, is_prime INTEGER, price DECIMAL(10,2))
Hints
- Use generate_series to enumerate months between 2023-01-01 and 2023-03-01.
- Use date_trunc('month', order_date) to group orders by month.
Monthly Price Bucket Percentages
Write an SQL query that, for January through March 2023, assigns each order to price buckets [0,20), [20,40), and >=40, then computes for each month the percentage of total sales contributed by each bucket. Output columns: month (first day of month), price_bucket, pct_sales (percentage of that month’s total revenue, rounded to 2 decimals). Ensure percentages per month sum to 100%.
Tables
sales(order_id INTEGER, order_date DATE, is_prime INTEGER, price DECIMAL(10,2))
Hints
- Define price buckets with a CASE expression based on price.
- First aggregate sales per month and price_bucket, then compute monthly totals.