Analyze Monthly Prime vs Non-Prime Sales and Price Buckets

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.

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.

Table sales

column	type	constraints
order_id	INTEGER	PRIMARY KEY, NOT NULL
order_date	DATE	NOT NULL
is_prime	INTEGER	NOT NULL, CHECK (is_prime IN (0, 1))
price	DECIMAL(10,2)	NOT NULL, CHECK (price >= 0)

Sample data:

is_prime	order_date	order_id	price
1	2023-01-15	1001	25.99
0	2023-01-20	1002	18.5
1	2023-02-05	1003	45
0	2023-02-14	1004	12.99
1	2023-03-03	1005	33.25

Expected output

Three rows, one per month from 2023-01-01 to 2023-03-01, showing revenue split into prime_sales and non_prime_sales. Months with no orders in the source data would still appear with 0 values.

month	non_prime_sales	prime_sales
2023-01-01	18.5	25.99
2023-02-01	12.99	45
2023-03-01	0	33.25

Hints

1. Use generate_series to enumerate months between 2023-01-01 and 2023-03-01.
2. Use date_trunc('month', order_date) to group orders by month.
3. LEFT JOIN the generated months to the aggregated data and COALESCE NULLs to 0.

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%.

Table sales

column	type	constraints
order_id	INTEGER	PRIMARY KEY, NOT NULL
order_date	DATE	NOT NULL
is_prime	INTEGER	NOT NULL, CHECK (is_prime IN (0, 1))
price	DECIMAL(10,2)	NOT NULL, CHECK (price >= 0)

Sample data:

is_prime	order_date	order_id	price
1	2023-01-15	1001	25.99
0	2023-01-20	1002	18.5
1	2023-02-05	1003	45
0	2023-02-14	1004	12.99
1	2023-03-03	1005	33.25

Expected output

For each month from January to March 2023, return one row per price bucket present that month with the percentage of that month’s total sales contributed by that bucket. Percentages per month sum to 100%.

month	pct_sales	price_bucket
2023-01-01	41.58	[0,20)
2023-01-01	58.42	[20,40)
2023-02-01	77.6	>=40
2023-02-01	22.4	[0,20)
2023-03-01	100	[20,40)

Hints

1. Define price buckets with a CASE expression based on price.
2. First aggregate sales per month and price_bucket, then compute monthly totals.
3. Divide bucket_sales by total_sales and use ROUND(..., 2) to get pct_sales.