Most common page paths per session

PySpark data engineering interview problem. Difficulty: intermediate. Pattern: Window Functions. About 16 minutes. Part of the Pro drill bank.

Find the most common consecutive 2-page transitions across user browsing sessions. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.

Given a DataFrame clicks with columns session_id, step, and page: Analyze user navigation transitions within each session: Within each session_id ordered by step, determine each transition from page to the immediate next page next_page. Exclude terminal steps where no subsequent page exists in the session. Count occurrences of each (page, next_page) transition across all sessions. Columns to return: page, next_page, count. Sort by count descending, then page, then next_page. Assign the final DataFrame to result.

Requirements

• Exclude terminal pages where next_page is null.
• Sort by count descending, then page, then next_page.

Constraints

• step is a positive integer sequential per session.

Examples

Input: clicks session_id | step | page s1 | 1 | home s1 | 2 | cart s1 | 3 | checkout s2 | 1 | home s2 | 2 | cart s3 | 1 | search s3 | 2 | item Output: page | next_page | count home | cart | 2 cart | checkout | 1 search | item | 1 home -> cart occurs twice, while cart -> checkout and search -> item appear once each.

Topics: lakebench, pyspark, lead, transitions.

More PySpark interview questions · All interview problems · Learn data engineering