Calculate Weekly Revenue and Order Count for Standard Deliveries

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a Medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Technical Screen rounds at Instacart.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Instacart during technical interviews.

Question

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.

PracHub · Accepted Answer

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.

Quick Overview

Quick Overview