Calculate Weekly Revenue and Order Count for Standard Deliveries

instacart_orders

+----------+---------+---------+------------+---------+--------------+
| order_id | user_id | revenue | created_at | geo     | delivery_type|
+----------+---------+---------+------------+---------+--------------+
| 1        | 101     | 45.80   | 2023-07-03 | Miami   | standard      |
| 2        | 102     | 23.50   | 2023-07-04 | Miami   | ultrafast     |
| 3        | 103     | 67.20   | 2023-07-05 | Seattle | standard      |
| 4        | 101     | 15.00   | 2023-07-10 | Miami   | standard      |
| 5        | 104     | 52.30   | 2023-07-10 | Boston  | ultrafast     |
+----------+---------+---------+------------+---------+--------------+

Scenario

You must calculate weekly revenue and other summaries from Instacart’s order-level table.

Question

Write a SQL query that returns, for the last 8 full calendar weeks, total revenue and order count, filtered to standard delivery orders only.

Hints

Use DATE_TRUNC, WHERE, GROUP BY, ORDER BY.

Weekly standard-delivery revenue for last 8 full weeks

You are given an Instacart order-level table. For each full Monday–Sunday calendar week in the 8-week period from 2025-03-31 (Monday) through 2025-05-25 (Sunday), return the total revenue and number of orders, restricted to standard delivery orders only. Output one row per week, with the week’s start date, total revenue, and order count.

Table instacart_orders

column	type	constraints
order_id	INTEGER	PRIMARY KEY, NOT NULL
user_id	INTEGER	NOT NULL
revenue	DECIMAL(10,2)	NOT NULL, CHECK (revenue >= 0)
created_at	DATE	NOT NULL
geo	VARCHAR(50)	NOT NULL
delivery_type	VARCHAR(20)	NOT NULL

Sample data:

created_at	delivery_type	geo	order_id	revenue	user_id
2025-04-07	standard	Miami	1	45.8	101
2025-04-08	ultrafast	Miami	2	23.5	102
2025-04-09	standard	Seattle	3	67.2	103
2025-04-14	standard	Miami	4	15	101
2025-04-14	ultrafast	Boston	5	52.3	104

Expected output

One row per full calendar week (Monday start, Sunday end) between 2025-03-31 and 2025-05-25 inclusive, limited to standard deliveries. Each row shows the week start date, total revenue, and number of orders. Based on the sample data (which has activity in weeks starting 2025-04-07 and 2025-04-14), the result would be:

order_count	total_revenue	week_start
2	113	2025-04-07
1	15	2025-04-14

Hints

1. Use DATE_TRUNC('week', created_at) to bucket orders into Monday-start calendar weeks.
2. Filter with WHERE delivery_type = 'standard'.
3. Limit to dates between 2025-03-31 and 2025-05-25 (e.g., created_at >= '2025-03-31' AND created_at < '2025-05-26').
4. GROUP BY the truncated week_start and ORDER BY it.