Interview Focus: Window FunctionsDifficulty: Medium

Customer Purchase Trends

Estimated duration: 15 mins

Lab Overview & Scenario

Compare consecutive purchase values over time using lag functions.

Business Scenario

The customer retention division wants to track purchase trends. Calculate the difference in transaction amounts compared to the previous purchase for each customer.

1. Local Raw Mock Dataset

The processing pipeline will ingest the following local mock file structures (e.g. simulated as `dataset.csv`):

purchase_id	customer_id	date	amount
1	101	2026-07-01	150.0
2	101	2026-07-02	180.0
3	102	2026-07-01	200.0
4	101	2026-07-03	160.0
5	102	2026-07-04	220.0

View Raw CSV Dataset

purchase_id,customer_id,date,amount
1,101,2026-07-01,150.0
2,101,2026-07-02,180.0
3,102,2026-07-01,200.0
4,101,2026-07-03,160.0
5,102,2026-07-04,220.0

2. Your Tasks

Read the raw purchases dataset.

Create a window partitioned by customer_id and sorted by date.

Use lag() function to retrieve the previous order amount.

Calculate the amount difference (current_amount - previous_amount).

Display customer_id, date, amount, and the calculated trend value.

3. Pipeline Skeleton Script

Copy this starter template to write the data transformation logic:

python

# starter.py - Customer Purchase Trends
from pyspark.sql import SparkSession
from pyspark.sql.window import Window
from pyspark.sql.functions import col, lag

def calculate_trends():
    spark = SparkSession.builder.appName("PurchaseTrends").getOrCreate()
    # TODO: Read dataset.csv, configure lag window spec, find price diffs
    pass

if __name__ == "__main__":
    calculate_trends()

4. Interactive Solution & Execution Output

Reveal Solution Pipeline Code

Implement the correct transformations using PySpark to achieve the aggregated values metrics output.

Pipeline Flow

dataset.csv

↓

Read CSV

↓

Window spec by customer sorted by date

↓

Apply lag(amount, 1) over window

↓

Subtract prev from current

↓

Display Output

Step-by-Step Implementation Guide

Step 1

Load the purchases CSV dataset.

Step 2

Define a Window partitioned by customer_id and sorted chronologically by date.

Step 3

Add the prev_amount column using lag(col("amount"), 1).over(windowSpec) to fetch the previous row's amount.

Step 4

Calculate amount_diff by subtracting prev_amount from the current amount. Select reporting fields and print output.

Common Mistakes

Forgetting to sort the window by date, which yields random comparisons.
Not handling the null values on the first rows in downstream calculations if required.

Interviewer's Expectations

Why this is asked:

Tests consecutive record comparisons (trends, sessions duration tracking) using lag and lead functions.

What they look for:

Clean application of lag() over ordered partition windows.

Common mistakes:

Forgetting chronological order sorting in the window spec.

An excellent answer includes:

Explain that lag and lead require sorting, which triggers a shuffle to group partition records sequentially.

✔ What You Learned

✔Use lag() to look back at previous rows
✔Sort chronological user profiles
✔Calculate transaction values changes over time

Continue Learning

Previous LabDetect Duplicate Transactions Related Theory LessonWindow Functions: LEAD/LAG Next LabOptimize a Slow Spark Job