Revenue by Month From Orders

EasySQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is Revenue by Month From Orders. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

You need to log in / sign up to run or submit.

Problem

Mutual of Omaha's customer portal reporting team needs a monthly revenue summary based only on orders associated with known customers. Write a PostgreSQL query that joins orders to customers and aggregates order amounts by calendar month.

Requirements

Join each order to its matching customer using customer_id.
Exclude orders without a matching customer or a valid order_date.
Return the month as YYYY-MM and total revenue for that month, ignoring NULL amounts.
Sort the results chronologically by month.

Schema

customers

Column	Type	Description
customer_idPK	INTEGER	Unique customer identifier
customer_name	VARCHAR(100)	Customer's full name
email	VARCHAR(150)	Customer email address

orders

Column	Type	Description
order_idPK	INTEGER	Unique order identifier
customer_id	INTEGER	References customers.customer_id
order_date	DATE	Date the order was placed
order_amount	NUMERIC(12,2)	Revenue amount for the order

Tablescustomersorders

Your solutionPostgreSQL

You need to log in / sign up to run or submit.

Run a query to see results