Interview Focus: Window FunctionsDifficulty: Medium

Detect Duplicate Transactions

Estimated duration: 15 mins

Lab Overview & Scenario

Identify duplicate transactions occurring within the same minute for the same account.

Business Scenario

The fraud department wants to flag duplicate transactions. Identify records where the same account has multiple charges for the same amount within the exact same minute.

1. Local Raw Mock Dataset

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

txn_id	account_id	timestamp	amount
1	101	2026-07-01 10:00:15	100.0
2	101	2026-07-01 10:00:45	100.0
3	101	2026-07-01 10:05:00	100.0
4	102	2026-07-01 10:02:10	250.0
5	102	2026-07-01 10:02:40	250.0

View Raw CSV Dataset

txn_id,account_id,timestamp,amount
1,101,2026-07-01 10:00:15,100.0
2,101,2026-07-01 10:00:45,100.0
3,101,2026-07-01 10:05:00,100.0
4,102,2026-07-01 10:02:10,250.0
5,102,2026-07-01 10:02:40,250.0

2. Your Tasks

Read the raw transaction logs.

Extract the minute from the timestamp column.

Use a Window function with row_number() to rank transactions within the same minute for each account and amount.

Filter and show only rows where row_number > 1 (duplicates).

3. Pipeline Skeleton Script

Copy this starter template to write the data transformation logic:

python

# starter.py - Detect Duplicate Transactions
from pyspark.sql import SparkSession
from pyspark.sql.window import Window
from pyspark.sql.functions import col, row_number, date_format

def detect_duplicates():
    spark = SparkSession.builder.appName("DuplicateDetection").getOrCreate()
    # TODO: Read dataset.csv, extract minute, apply row_number() over account/amount/minute
    pass

if __name__ == "__main__":
    detect_duplicates()

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

↓

Format timestamp to minute

↓

Define Window by account, amount, minute

↓

Apply row_number()

↓

Filter row_num > 1

↓

Display Duplicates

Step-by-Step Implementation Guide

Step 1

Load the transaction CSV files.

Step 2

Extract the minute interval from the timestamp using date_format(col("timestamp"), "yyyy-MM-dd HH:mm").

Step 3

Define the Window partition: group rows by account_id, amount, and the extracted txn_minute column, sorting by timestamp.

Step 4

Generate a sequential row index using row_number().over() and filter to return only rows where row_num > 1 to pinpoint duplicate charges.

Common Mistakes

Grouping by account_id and aggregate count, which tells you *that* duplicates exist but doesn't let you output the individual duplicate rows.
Using dense_rank() instead of row_number() which does not assign sequential unique numbers.

Interviewer's Expectations

Why this is asked:

Detecting events matching specific criteria within close time boundaries is a classic data validation question.

What they look for:

Clean application of Row Number window partition keys.

Common mistakes:

Using expensive self-joins instead of window partitions.

An excellent answer includes:

Explain that using row_number over window partitions avoids self-joins and completes in a single linear shuffle pass.

✔ What You Learned

✔Format dates to minutes granularity
✔Partition windows by multiple keys
✔Isolate duplicate transaction rows using row_number()

Continue Learning

Previous LabMonthly Sales Report Related Theory LessonWindow Functions: ROW_NUMBER/RANK Next LabCustomer Purchase Trends