Calculate Monthly Revenue from Orders in 2023
AmazonData Scientist · Technical Screen
Last updated
This question evaluates proficiency in SQL-based data manipulation, including aggregation, date extraction, and combining transactional tables to produce business metrics, and falls under the Data Manipulation (SQL/Python) domain.
orders
| id | order_date | customer |
|---|---|---|
| 1 | 2023-01-03 | 101 |
| 2 | 2023-02-14 | 102 |
| 3 | 2023-02-20 | 101 |
order_items
| id | order_id | item | price |
|---|---|---|---|
| 1 | 1 | A | 25.00 |
| 2 | 1 | B | 15.00 |
| 3 | 2 | C | 35.00 |
Scenario
Online assessment: SQL section with order and order_item data.
Question
Given tables orders and order_items, write a SQL query that returns each calendar month of 2023 and the corresponding total revenue (sum of price).
Hints
JOIN the two tables, filter by year, GROUP BY month extracted from order_date, ORDER BY month.
Solution walkthrough is for members
Get the full walkthrough with reference code and complexity analysis, plus Kitty Coach at 300 messages a day. Hints, the code runner and community answers stay free.