Time Between Two Events
HardGiven the schema shown below, write a function to identify the user who liked a post in the shortest time after logging in.
user_event +---------------+----------+ | user_id | int | | event | varchar | | timestamp | datetime | +---------------+----------+
Your function should output a result in the following format:
user_id | login | like | time_between --------+----------+----------+-------------- int | datetime | datetime | int
time_betweenshould be rounded to the nearest minute
The key of this solution is the use of a pivot table to get the earliest login and like timestamps for each user.
The use of a pivot table translates the data from this:
To this (after dropping unnecessary columns):
Then it's only a matter of computing the time between the two events:
Finally, we sort the results based on ascending time_between and return the first row, which is the row with the smallest time_between.
Pythondef find_fastest_like(log: pd.DataFrame) -> pd.DataFrame:
# Create pivot table to get the earliest login and like timestamps for each user_id
pivot_df = pd.pivot_table(
data=log,
index='user_id',
columns='event',
values='timestamp',
aggfunc='min'
).reset_index()
# Drop unnecessary columns
pivot_df.columns.name = None
pivot_df = pivot_df[['user_id', 'login', 'like']]
# Calculate the time difference in seconds and round to the nearest minute
pivot_df['time_between'] = ((pivot_df['like'] - pivot_df['login']).dt.total_seconds() / 60).round().astype(int)
# Sort values by time_between
pivot_df = pivot_df.sort_values(by = ['time_between'])
# Return the row with the shortest time_between
result = pivot_df.head(1)
return result