Calculate Monthly Revenue from Orders in 2023
Company: Amazon
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: 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.
You are given two tables, orders and order_items. Write a SQL query that returns each calendar month of 2023 (months 1 through 12) and the corresponding total revenue for that month, where revenue is defined as the sum of price from order_items joined to orders. The result should include months with zero revenue and be ordered by month ascending.
Tables
orders(id INTEGER, order_date DATE, customer INTEGER)
order_items(id INTEGER, order_id INTEGER, item VARCHAR(50), price DECIMAL(10,2))
Hints
- JOIN orders to order_items on orders.id = order_items.order_id.
- Filter rows to year 2023 using the order_date column.