Top 2 Most-Watched Videos Per User

Medium
Window Functions
Top-K
Pinterest
Instacart

Top 2 Most-Watched Videos Per User

Given watchtime(video_id, user_id, minutes), return the top 2 rows for each user_id by minutes descending (ties broken by video_id ascending). Project user_id, video_id, minutes. Sort by user_id asc, minutes desc, video_id asc.

Sample input

video_iduser_idminutes
1497
2136
3447
4468
53109
6134
Browser Engine: Evaluates PySpark DataFrame & SQL logic locally in Pyodide WebAssembly.

Input data

1 DataFrame

These DataFrames are passed to solve(spark, dfs). Access one with dfs['name'].

df.watchtime12 rows
video_id (int)user_id (int)minutes (int)
1497
2136
3447
4468
53109
6134
7499
816
9346
10334
112114
121113

Return these columns

user_id(int)
video_id(int)
minutes(int)

Column names and order must match. Sort order is specified in the question.

Browser engine
Loading Python editor…
Click Run to try the sample cases, or Submit to check the hidden cases. First run caches the browser Python engine (~8–15s once); all code stays on your device.