Join and Aggregate by Month | Dataford Interview Questions

How would you use SQL to join multiple tables and aggregate results by month?

Use the provided shipment, customer, and terminal tables. Include completed shipments from January through March 2025, including records with missing customer or terminal matches.

Output

One row per month, customer, and origin terminal.
Columns: shipment_month, customer_name, terminal_name, shipment_count, and total_freight_charge.
Use Unknown for missing customer or terminal names, show months chronologically, then sort by customer and terminal name.

Schema

shipments

Column	Type	Description
shipment_idPK	INT	Unique shipment identifier
customer_id	INT	Customer associated with the shipment
origin_terminal_id	INT	Origin terminal for the shipment
shipped_at	DATE	Shipment date
status	VARCHAR(20)	Shipment processing status
freight_charge	NUMERIC(12,2)	Freight charge for the shipment

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer name
customer_type	VARCHAR(30)	Customer classification

terminals

Column	Type	Description
terminal_idPK	INT	Unique terminal identifier
terminal_name	VARCHAR(100)	Origin terminal name
state_code	VARCHAR(2)	Terminal state code

Your question is Join and Aggregate by Month. Start with the requirements and the three tables in the Question tab.

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

Problem