Module 5 Lab: Window Functions

Window Functions elevate you from writing simple application queries to writing serious analytical queries. You now have the power to rank data, compare rows to each other, and calculate running totals.

The Scenario

The marketing team at our E-Commerce company wants to analyze user retention and purchasing habits. Specifically, they want to track how a user’s spending changes over time as they become more loyal to the platform.

To do this, they need to identify which order is a user’s 1st purchase, which is their 2nd purchase, which is their 3rd, and so on.

You have been given the orders table. It contains:

id (The order ID)
user_id (The customer who bought it)
order_date (When it was purchased)
total_amount (How much they spent)

Building the Query

Your task is to write a query that returns the user_id, the order_date, the total_amount, and a new column calculated via a Window Function called purchase_number.

purchase_number should be 1 for a user’s very first order.
It should be 2 for their second order, and so on.
The numbering must reset back to 1 when looking at a different user_id.

Think about how to construct the OVER() clause:

What function assigns sequential numbers?
How do you tell the window to reset the numbering for each specific user? (PARTITION BY)
How do you ensure the oldest order gets number 1? (ORDER BY)

[!TIP] If you master this, you can wrap this entire query in a CTE and do WHERE purchase_number = 1 to instantly find the date of every user’s first-ever purchase!

Solve the lab to complete Module 5. Next up, we will finally learn how to modify the data in the database in Module 6: DML!