SQL Stored Procedures | Dataford Interview Questions

Tell me about your experience using SQL stored procedures in data engineering or ETL workflows.

For a hands-on PostgreSQL response, implement a procedure that refreshes the customer order summary for January 2025. Include valid handling for customers without qualifying orders and ensure the result can be regenerated consistently.

Output

One row per customer with customer_id, customer_name, region, completed_order_count, and completed_order_total.
Include every customer, including those without qualifying orders.
Order by customer_id ascending.

Schema

customer_dim

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

source_orders

Column	Type	Description
order_idPK	INT	Unique source order identifier
customer_id	INT	Customer associated with the order
order_date	DATE	Date the order was placed
amount	NUMERIC(12,2)	Order monetary amount
status	VARCHAR(20)	Order processing status

customer_order_summary

Column	Type	Description
customer_idPK	INT	Customer identifier in the refreshed summary
customer_name	VARCHAR(100)	Customer display name
region	VARCHAR(50)	Customer sales region
completed_order_count	BIGINT	Number of completed January orders
completed_order_total	NUMERIC(12,2)	Total amount of completed January orders

Your question is SQL Stored Procedures. 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