Scenario

You work for a retail store that tracks customer transactions and wants to understand repeat purchasing patterns.

Task

Write a Snowflake SQL query that:

1. For each row in {{ ref("transactions") }}, adds a previous_product column containing the product_id of the same customer's most recent prior transaction (ordered by date), or NULL if there is no prior transaction
2. Adds a date_and_product column that concatenates date and previous_product separated by a space, substituting the string 'None' when previous_product is NULL
3. Returns all six columns: customer_id, product_id, quantity, date, previous_product, date_and_product

Schema

transactions

Example

transactions:

Expected Output:

Note: previous_product resets per customer. C2 and C3 each have only one transaction, so their previous_product is NULL. The date_and_product column uses the literal string 'None' when previous_product is NULL.