Analyze TSV File for User Page Visits and Patterns
Company: Apple
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: This question evaluates skills in parsing and manipulating time-series user-event data, performing aggregations and path-frequency analysis, and understanding vectorized or parallel computations for performance.
Most Visited Pages
Return the page or pages with the highest total visit count. Output columns: page, visit_count.
Tables
visits(person_id INTEGER, timestamp INTEGER, page VARCHAR(10))
Hints
- GROUP BY page and COUNT(*)
- 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.
Tables
visits(person_id INTEGER, timestamp INTEGER, page VARCHAR(10))
Hints
- Self-join each visit to the next later visit for the same person
- 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.
Tables
visits(person_id INTEGER, timestamp INTEGER, page VARCHAR(10))
Hints
- Build per-user paths with STRING_AGG ordered by timestamp
- 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.
Tables
visits(person_id INTEGER, timestamp INTEGER, page VARCHAR(10))
Hints
- Use LEAD(timestamp) OVER (PARTITION BY person_id ORDER BY timestamp)
- Exclude NULL durations (last event per user)
Community answers
Answer by SS
import pandas as pd high = visits.groupby(['page']).agg(total_visits=('person_id','count')).reset_index()high['rank'] = high['total_visits'].rank(method = 'dense',ascending = False)high[high['rank'] == 1]
Answer by SS
Q2:
import pandas as pd
visits = visits.sort_values(
by=['person_id', 'timestamp']
)
next timestamp within each user
visits['next_timestamp'] = (
visits.groupby('person_id')['timestamp']
.shift(-1)
)
residence time
visits['residence_time'] = (
visits['next_timestamp'] - visits['timestamp']
)
aggregate per page
final = (
visits.groupby('page', as_index=False)
.agg(total_residence_time=('residence_time', 'sum'))
)
dense rank
final['rank'] = final['total_residence_time'].rank(
method='dense',
ascending=False
)
result = final[final['rank'] == 1][
['page', 'total_residence_time']
]