Design a Data Model and SQL Queries | Dataford Interview Questions

Design a data model for a given business scenario, then write medium-to-hard SQL queries against it.

Asked in the Data modeling + SQL stage. Define a relational model for the supplied scenario, then demonstrate it with a query that summarizes completed transactional activity by customer and month.

Output

One row per customer with completed activity in a month.
Columns: order_month, customer_name, completed_orders, total_value, and monthly_rank.
Include only completed activity, rank customers within each month by total_value descending, share ranks for ties, and sort by month ascending then rank ascending.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer display name

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	Customer associated with the order
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Order lifecycle status

order_items

Column	Type	Description
order_item_idPK	INT	Unique order line identifier
order_id	INT	Order containing the line item
quantity	INT	Number of units purchased
unit_price	NUMERIC(10,2)	Price per unit

Your question is Design a Data Model and SQL Queries. 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