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.
Quick Answer: 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.