Turo Data Science 2nd Round: SQL & Pandas
Solutions
SQL Solutions (8)
1) Cohort Retention (30-day)
SELECT DATE_TRUNC('quarter', signup_ts) AS signup_qtr, COUNT(DISTINCT u.user_id) AS
total_users, COUNT(DISTINCT CASE WHEN t.user_id IS NOT NULL THEN u.user_id END) AS
retained_users, ROUND(100.0 * COUNT(DISTINCT CASE WHEN t.user_id IS NOT NULL THEN
u.user_id END) / COUNT(DISTINCT u.user_id), 2) AS retention_rate FROM users u LEFT JOIN trips t
ON u.user_id = t.guest_id AND [Link] = 'completed' AND t.start_ts <= u.signup_ts + INTERVAL '30
day' WHERE u.signup_ts BETWEEN '2025-04-01' AND '2025-06-30' GROUP BY signup_qtr; 2) Host
Utilization Leaderboard
SELECT h.host_id, COUNT(DISTINCT t.trip_id) AS trips_booked, SUM(DATE_PART('day', t.end_ts -
t.start_ts)) AS booked_days, COUNT(DISTINCT c.car_id) * 90 AS available_days, ROUND(100.0 *
SUM(DATE_PART('day', t.end_ts - t.start_ts)) / (COUNT(DISTINCT c.car_id)*90), 2) AS utilization_pct,
SUM(t.host_earnings_usd) AS total_earnings FROM cars c JOIN trips t ON c.car_id = t.car_id AND
[Link]='completed' JOIN users h ON c.host_id = h.user_id WHERE t.start_ts >= CURRENT_DATE -
INTERVAL '90 day' GROUP BY h.host_id ORDER BY utilization_pct DESC, total_earnings DESC
LIMIT 10; 3) Booking Funnel Drop-off
SELECT [Link], COUNT(DISTINCT s.search_id) AS searches, COUNT(DISTINCT t.trip_id) AS
created_bookings, COUNT(DISTINCT CASE WHEN [Link]='completed' THEN t.trip_id END) AS
completed_trips, ROUND(100.0 * COUNT(DISTINCT t.trip_id)/COUNT(DISTINCT s.search_id),2) AS
search_to_create_rate, ROUND(100.0 * COUNT(DISTINCT CASE WHEN [Link]='completed' THEN
t.trip_id END)/COUNT(DISTINCT t.trip_id),2) AS create_to_complete_rate FROM searches s LEFT
JOIN trips t ON s.booked_trip_id = t.trip_id WHERE s.search_ts >= CURRENT_DATE - INTERVAL '60
day' GROUP BY [Link]; 4) Repeat Guest Rate
WITH first_trip AS ( SELECT guest_id, MIN(start_ts) AS first_trip_ts FROM trips WHERE
status='completed' GROUP BY guest_id ) SELECT [Link], COUNT(DISTINCT f.guest_id) AS
first_guests, COUNT(DISTINCT CASE WHEN t.trip_id IS NOT NULL THEN f.guest_id END) AS
repeat_guests, ROUND(100.0 * COUNT(DISTINCT CASE WHEN t.trip_id IS NOT NULL THEN
f.guest_id END)/COUNT(DISTINCT f.guest_id),2) AS repeat_rate FROM first_trip f JOIN users u ON
f.guest_id = u.user_id LEFT JOIN trips t ON t.guest_id = f.guest_id AND t.start_ts BETWEEN
f.first_trip_ts AND f.first_trip_ts + INTERVAL '90 day' AND [Link]='completed' WHERE
EXTRACT(YEAR FROM f.first_trip_ts)=2025 GROUP BY [Link];
Pandas Solutions (8)
1) User 30-Day Activation Flag
users_df['signup_ts'] = pd.to_datetime(users_df['signup_ts']) trips_df['start_ts'] =
pd.to_datetime(trips_df['start_ts']) merged = users_df.merge(trips_df[trips_df['status']=='completed'],
left_on='user_id', right_on='guest_id', how='left') merged['activated_30d'] = (merged['start_ts'] -
merged['signup_ts']).[Link] <= 30 activation =
[Link]('marketing_source')['activated_30d'].mean().reset_index() 2) Host Reliability Metric
recent = trips_df[trips_df['created_ts'] >= [Link]() - [Link](days=180)] cancel_rate
= ([Link]('host_id')['status'] .apply(lambda x: (x=='canceled').mean())
.reset_index(name='cancel_rate')) cancel_rate = cancel_rate[cancel_rate['cancel_rate'] > 0.1] 3)
AB-like City Comparison
from [Link] import ttest_ind subset = trips_df[(trips_df['status']=='completed') &
(trips_df['city'].isin(['SF','LA']))] sf, la = subset[subset['city']=='SF']['price_usd'],
subset[subset['city']=='LA']['price_usd'] t_stat, p_val = ttest_ind(sf, la, equal_var=False) 4)
Search→Book Attribution
joined = searches_df.merge(trips_df[['trip_id','city']], left_on='booked_trip_id', right_on='trip_id',
how='left') metrics = [Link]('city').agg( searches=('search_id','count'),
booked=('booked_trip_id','count'), avg_results=('results_count','mean') ).reset_index()
metrics['book_rate'] = metrics['booked']/metrics['searches'] 5) Rolling Host Earnings
trips_df['date'] = pd.to_datetime(trips_df['start_ts']).[Link] daily =
trips_df.groupby(['host_id','date'])['host_earnings_usd'].sum().reset_index() daily['date'] =
pd.to_datetime(daily['date']) rolling = ([Link]('host_id') .apply(lambda g:
g.set_index('date')['host_earnings_usd'].rolling('30D').sum().max())
.reset_index(name='max_rolling_30d')) top10 = [Link](10,'max_rolling_30d') 6) Duration
Outlier Removal
trips_df['hours_booked'] = (trips_df['end_ts'] - trips_df['start_ts']).dt.total_seconds()/3600 q_low, q_hi =
trips_df['hours_booked'].quantile([0.01,0.99]) clean =
trips_df[(trips_df['hours_booked']>q_low)&(trips_df['hours_booked']48] 7) Rating Bias Check
merged = reviews_df.merge(trips_df[['trip_id','price_usd','city']], on='trip_id', how='left') corrs =
([Link]('city') .apply(lambda g: g['rating'].corr(np.log1p(g['price_usd'])))
.reset_index(name='corr')) corrs = corrs[(corrs['corr'].abs()>=0.2)] 8) Cold-Start Guest Features
merged = users_df.merge(trips_df, left_on='user_id', right_on='guest_id', how='left')
merged['days_since_signup'] = (merged['start_ts'] - merged['signup_ts']).[Link] window =
merged[merged['days_since_signup']<=30] features = [Link]('user_id').agg(
searches_30d=('search_id','count'), trips_30d=('trip_id','nunique'), avg_price_30d=('price_usd','mean'),
med_booking_lag=('days_since_signup','median') ).reset_index()