Calculate Weekly Revenue and Order Count for Standard Deliveries
Company: Instacart
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: This question evaluates proficiency in time-based aggregations and filtering on order-level transactional data to compute weekly revenue and order counts specifically for standard delivery orders.
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.
Tables
instacart_orders(order_id INTEGER, user_id INTEGER, revenue DECIMAL(10,2), created_at DATE, geo VARCHAR(50), delivery_type VARCHAR(20))
Hints
- Use DATE_TRUNC('week', created_at) to bucket orders into Monday-start calendar weeks.
- Filter with WHERE delivery_type = 'standard'.