Window Function vs Self-Join | Dataford Interview Questions

Why would you choose a window function over a self-join here?

Asked in the technical interview 1 stage. Interviewer probing SQL choices.

Using the orders table, write a query that returns each order and the immediately preceding order date for the same customer. The result must preserve orders with no predecessor and use deterministic ordering for orders placed on the same date.

Output

One row per order, with order_id, customer_id, order_date, and previous_order_date
Include the earliest order for each customer with a NULL previous date
Order by customer_id ascending with NULL customer IDs last, then order_date and order_id ascending

Schema

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

Your question is Window Function vs Self-Join. Start with the requirements and the one table 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