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.