SQL Joins, Indexing, and CTEs | Dataford Interview Questions

Explain SQL join types, how indexing affects query performance, and how you use common table expressions (CTEs) in data engineering work.

Use the supplied tables to demonstrate the concepts with a PostgreSQL query. Address why each join type is appropriate, how indexes could improve the query, and how the CTE structures the transformation.

Output

One row per customer, including customers without qualifying orders
Columns: customer_id, customer_name, completed_order_count, completed_spend, latest_completed_order
Order by completed_spend descending, then customer_id ascending

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer display name
state	VARCHAR(2)	Two-letter customer state code

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	Customer associated with the order
product_id	INT	Product referenced by the order
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Current order status
amount	NUMERIC(10,2)	Order amount

products

Column	Type	Description
product_idPK	INT	Unique product identifier
product_name	VARCHAR(120)	Product name
category	VARCHAR(50)	Product category

Your question is SQL Joins, Indexing, and CTEs. 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