Analyze TSV File for User Page Visits and Patterns

visits

+-----------+-----------+------+
| person_id | timestamp | page |
+-----------+-----------+------+
| 1         | 100       | A    |
| 1         | 110       | B    |
| 1         | 150       | C    |
| 2         | 100       | B    |
| 2         | 120       | C    |
+-----------+-----------+------+

Scenario

You receive a TSV file in which each line contains a user’s chronological page-visit history formatted as timestamp,page and separated by “/t”. Business wants insights on usage and performance optimizations.

Question

Parse the file and return the page with the highest total visit count. 2) For every visit, compute the residence time (current timestamp – next timestamp). Return the page with the greatest total residence time across all users. 3) Treat each user’s ordered page sequence as a path. Return the most frequent complete path (e.g., "A→B→C"). 4) #2 can be slow with explicit loops. Rewrite the residence-time computation so it can execute in parallel / vectorized form (e.g., time[i] – time[i-1]) and explain the performance benefit.

Hints

Load into pandas, sort by person_id & timestamp, use groupby + diff/shift, Counter or groupby agg, and vectorized numpy operations for parallelism.

Most Visited Pages

Return the page or pages with the highest total visit count. Output columns: page, visit_count.

Table visits

column	type	constraints
person_id	INTEGER	NOT NULL
timestamp	INTEGER	NOT NULL
page	VARCHAR(10)	NOT NULL

Sample data:

page	person_id	timestamp
A	1	100
B	1	110
C	1	150
B	2	100
C	2	120

Expected output

Pages B and C are the most visited with 2 visits each.

page	visit_count
B	2
C	2

Hints

1. GROUP BY page and COUNT(*)
2. Filter to rows matching the maximum count (ties allowed)

Max Residence Time by Page (Non-Window)

Residence time for a row is defined as next_timestamp - current_timestamp within the same person_id. Using a non-window approach (such as a self-join or correlated subquery), compute residence time per row, then sum residence times per page and return the page or pages with the greatest total residence time. Output columns: page, total_residence_time.

Table visits

column	type	constraints
person_id	INTEGER	NOT NULL
timestamp	INTEGER	NOT NULL
page	VARCHAR(10)	NOT NULL

Sample data:

page	person_id	timestamp
A	1	100
B	1	110
C	1	150
B	2	100
C	2	120

Expected output

Page B has the greatest total residence time: 60 (B: 40 from person 1 and 20 from person 2).

page	total_residence_time
B	60

Hints

1. Self-join each visit to the next later visit for the same person
2. Use MIN(timestamp) with a > condition to find the next event

Most Frequent User Paths

For each user, concatenate visited pages in chronological order using '→' as the separator to form the user's complete path. Count identical paths across users and return the path or paths with the highest frequency. Output columns: path, frequency.

Table visits

column	type	constraints
person_id	INTEGER	NOT NULL
timestamp	INTEGER	NOT NULL
page	VARCHAR(10)	NOT NULL

Sample data:

page	person_id	timestamp
A	1	100
B	1	110
C	1	150
B	2	100
C	2	120

Expected output

The most frequent complete paths tie: 'A→B→C' and 'B→C' appear once each.

frequency	path
1	A→B→C
1	B→C

Hints

1. Build per-user paths with STRING_AGG ordered by timestamp
2. Count identical paths and filter to the maximum frequency

Residence Time Using Window Functions

Using a window function, compute residence time per row as LEAD(timestamp) - timestamp within each person_id. Sum residence times per page and return the page or pages with the greatest total residence time. Output columns: page, total_residence_time.

Table visits

column	type	constraints
person_id	INTEGER	NOT NULL
timestamp	INTEGER	NOT NULL
page	VARCHAR(10)	NOT NULL

Sample data:

page	person_id	timestamp
A	1	100
B	1	110
C	1	150
B	2	100
C	2	120

Expected output

Using LEAD to compute per-row durations, page B has the greatest total residence time of 60.

page	total_residence_time
B	60

Hints

1. Use LEAD(timestamp) OVER (PARTITION BY person_id ORDER BY timestamp)
2. Exclude NULL durations (last event per user)