Tricky SQL Questions with Pandas & NumPy

Solutions
A comprehensive collection of challenging SQL interview questions from DataLemur,
StrataScratch, and HackerRank with equivalent solutions in Python

Table of Contents
1. 15 Days of Learning SQL
2. User Session Activity
3. Symmetric Pairs
4. Y-on-Y Growth Rate
5. Top Percentile Fraud
6. Weather Observation Station
7. Tweets' Rolling Averages
8. Median Google Search Frequency
9. SQL Project Planning
10. Active User Retention
11. Host Popularity Rental Prices

1. 15 Days of Learning SQL

Platform: DataLemur
Difficulty: Hard
Description: Find total number of unique hackers who made at least 1 submission each day
(starting on the first day), and the hacker who made maximum submissions each day.

SQL Solution

WITH daily_submissions AS (
SELECT submission_date,
hacker_id,
COUNT(*) as submissions_count,
ROW_NUMBER() OVER (PARTITION BY submission_date ORDER BY COUNT(*) DESC, hacker_i
FROM submissions
GROUP BY submission_date, hacker_id
),
consecutive_hackers AS (
SELECT submission_date,
hacker_id,
ROW_NUMBER() OVER (PARTITION BY hacker_id ORDER BY submission_date) as consecuti
 FROM submissions
GROUP BY submission_date, hacker_id
)
SELECT s.submission_date,
COUNT(DISTINCT CASE WHEN DATEDIFF(s.submission_date, '2016-03-01') + 1 = [Link]
ds.hacker_id,
[Link]
FROM daily_submissions s
JOIN consecutive_hackers c ON s.submission_date = c.submission_date AND s.hacker_id = c.h
JOIN daily_submissions ds ON s.submission_date = ds.submission_date AND [Link] = 1
JOIN hackers h ON ds.hacker_id = h.hacker_id
GROUP BY s.submission_date, ds.hacker_id, [Link]
ORDER BY s.submission_date;

Pandas Solution

import pandas as pd
import numpy as np

# Calculate daily submissions for each hacker

daily_submissions = ([Link](['submission_date', 'hacker_id'])
.size()
.reset_index(name='submissions_count'))

# Find hacker with maximum submissions per day

max_submissions_per_day = (daily_submissions
.sort_values(['submission_date', 'submissions_count', 'hacker_i
ascending=[True, False, True])
.groupby('submission_date')
.first()
.reset_index())

# Find consecutive submission days for each hacker

submissions['days_from_start'] = (submissions['submission_date'] - pd.to_datetime('2016-0
hacker_consecutive = ([Link](['submission_date', 'hacker_id'])
.first()
.reset_index()
.sort_values(['hacker_id', 'submission_date'])
.groupby('hacker_id')
.apply(lambda x: [Link](consecutive_days=range(1, len(x)+1)))
.reset_index(drop=True))

# Count unique hackers who maintained consecutive submissions

unique_hackers_per_day = (hacker_consecutive[hacker_consecutive['days_from_start'] == hac
.groupby('submission_date')
.agg({'hacker_id': 'nunique'})
.rename(columns={'hacker_id': 'unique_hackers'}))

# Final result
result = (max_submissions_per_day
.merge(unique_hackers_per_day, on='submission_date')
.merge(hackers, on='hacker_id')
[['submission_date', 'unique_hackers', 'hacker_id', 'name']])
2. User Session Activity
Platform: StrataScratch
Difficulty: Medium
Description: Calculate average session time for each user from page_load and page_exit
events.

SQL Solution

WITH load_times AS (
SELECT user_id,
DATE(timestamp) as session_date,
MIN(timestamp) as load_time
FROM user_sessions
WHERE action = 'page_load'
GROUP BY user_id, DATE(timestamp)
),
exit_times AS (
SELECT user_id,
DATE(timestamp) as session_date,
MAX(timestamp) as exit_time
FROM user_sessions
WHERE action = 'page_exit'
GROUP BY user_id, DATE(timestamp)
)
SELECT l.user_id,
AVG(EXTRACT(EPOCH FROM (e.exit_time - l.load_time))) as avg_session_time
FROM load_times l
JOIN exit_times e ON l.user_id = e.user_id AND l.session_date = e.session_date
GROUP BY l.user_id;

Pandas Solution

import pandas as pd
import numpy as np

# Convert timestamp to datetime

user_sessions['timestamp'] = pd.to_datetime(user_sessions['timestamp'])
user_sessions['session_date'] = user_sessions['timestamp'].[Link]

# Separate load and exit events

load_events = (user_sessions[user_sessions['action'] == 'page_load']
.groupby(['user_id', 'session_date'])
.agg({'timestamp': 'min'})
.rename(columns={'timestamp': 'load_time'}))

exit_events = (user_sessions[user_sessions['action'] == 'page_exit']

.groupby(['user_id', 'session_date'])
.agg({'timestamp': 'max'})
.rename(columns={'timestamp': 'exit_time'}))

# Calculate session duration

session_durations = (load_events.merge(exit_events, left_index=True, right_index=True)
 .assign(session_time=lambda x: (x['exit_time'] - x['load_time']).dt.t

# Calculate average session time per user

avg_session_time = (session_durations
.groupby('user_id')
.agg({'session_time': 'mean'})
.rename(columns={'session_time': 'avg_session_time'}))

3. Symmetric Pairs
Platform: HackerRank
Difficulty: Hard
Description: Find all symmetric pairs (X1, Y1) and (X2, Y2) such that X1 = Y2 and X2 = Y1.

SQL Solution

SELECT DISTINCT f1.X, f1.Y

FROM Functions f1
INNER JOIN Functions f2 ON f1.X = f2.Y AND f1.Y = f2.X
WHERE f1.X <= f1.Y
ORDER BY f1.X;

Pandas Solution

import pandas as pd
import numpy as np

# Find symmetric pairs using merge

symmetric_pairs = ([Link](functions,
left_on=['X', 'Y'],
right_on=['Y', 'X'],
suffixes=('', '_sym'))
.query('X <= Y')
.drop_duplicates(['X', 'Y'])
[['X', 'Y']]
.sort_values('X'))

# Alternative using numpy for set operations

def find_symmetric_pairs_numpy(df):
pairs = df[['X', 'Y']].values
reversed_pairs = df[['Y', 'X']].values

# Find intersection using numpy

symmetric = np.intersect1d(
[tuple(pair) for pair in pairs],
[tuple(pair) for pair in reversed_pairs]
)

return [Link](list(symmetric), columns=['X', 'Y']).query('X <= Y')

4. Y-on-Y Growth Rate
Platform: DataLemur
Difficulty: Hard
Description: Calculate year-over-year growth rate for each product.

SQL Solution

WITH yearly_revenue AS (
SELECT
EXTRACT(YEAR FROM transaction_date) as year,
product_id,
SUM(spend) as revenue
FROM user_transactions
GROUP BY EXTRACT(YEAR FROM transaction_date), product_id
)
SELECT
current_year.year,
current_year.product_id,
current_year.revenue as curr_year_revenue,
prev_year.revenue as prev_year_revenue,
ROUND(
100.0 * (current_year.revenue - prev_year.revenue) / prev_year.revenue, 2
) as yoy_rate
FROM yearly_revenue current_year
LEFT JOIN yearly_revenue prev_year
ON current_year.product_id = prev_year.product_id
AND current_year.year = prev_year.year + 1
ORDER BY current_year.product_id, current_year.year;

Pandas Solution

import pandas as pd
import numpy as np

# Extract year and calculate yearly revenue

user_transactions['year'] = pd.to_datetime(user_transactions['transaction_date']).[Link]
yearly_revenue = (user_transactions.groupby(['year', 'product_id'])
.agg({'spend': 'sum'})
.rename(columns={'spend': 'revenue'})
.reset_index())

# Calculate YoY growth using shift within groups

yoy_growth = (yearly_revenue.sort_values(['product_id', 'year'])
.groupby('product_id')
.assign(
prev_year_revenue=lambda x: x['revenue'].shift(1),
yoy_rate=lambda x: [Link](100 * (x['revenue'] - x['revenue'].shift(1)
)
.dropna()
.rename(columns={'revenue': 'curr_year_revenue'}))

# Using numpy for vectorized operations

 def calculate_yoy_numpy(df):
df_sorted = df.sort_values(['product_id', 'year'])
groups = df_sorted.groupby('product_id')

yoy_rates = []
for name, group in groups:
revenues = group['revenue'].values
yoy_rate = [Link](revenues) / revenues[:-1] * 100
yoy_rates.extend(yoy_rate)

return yoy_rates

5. Top Percentile Fraud

Platform: StrataScratch
Difficulty: Hard
Description: Find users whose transaction amounts are in the top 5 percentile and flag potential
fraud.

SQL Solution

WITH percentile_data AS (
SELECT
user_id,
transaction_id,
amount,
PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY amount) OVER() as percentile_95
FROM transactions
),
flagged_users AS (
SELECT
user_id,
COUNT(*) as suspicious_transactions,
AVG(amount) as avg_amount
FROM percentile_data
WHERE amount >= percentile_95
GROUP BY user_id
HAVING COUNT(*) >= 3
)
SELECT
f.user_id,
f.suspicious_transactions,
f.avg_amount,
'HIGH_RISK' as fraud_flag
FROM flagged_users f
ORDER BY f.avg_amount DESC;
Pandas Solution

import pandas as pd
import numpy as np

# Calculate 95th percentile

percentile_95 = transactions['amount'].quantile(0.95)

# Find suspicious transactions

suspicious_transactions = transactions[transactions['amount'] >= percentile_95]

# Flag users with multiple suspicious transactions

flagged_users = (suspicious_transactions
.groupby('user_id')
.agg({
'transaction_id': 'count',
'amount': 'mean'
})
.rename(columns={
'transaction_id': 'suspicious_transactions',
'amount': 'avg_amount'
})
.query('suspicious_transactions >= 3')
.assign(fraud_flag='HIGH_RISK')
.sort_values('avg_amount', ascending=False))

# Using numpy percentile for more control

def flag_fraud_numpy(df):
amounts = df['amount'].values
threshold = [Link](amounts, 95)

suspicious_mask = amounts >= threshold

suspicious_df = df[suspicious_mask]

# Vectorized groupby alternative

user_counts = [Link](suspicious_df['user_id'])
flagged_users = [Link](user_counts >= 3)[0]

return flagged_users

6. Weather Observation Station

Platform: HackerRank
Difficulty: Medium
Description: Find the city with the longest and shortest city name, and their lengths.
SQL Solution

SELECT city, LENGTH(city) as name_length

FROM (
SELECT city, LENGTH(city),
ROW_NUMBER() OVER (ORDER BY LENGTH(city) ASC, city ASC) as rn_shortest,
ROW_NUMBER() OVER (ORDER BY LENGTH(city) DESC, city ASC) as rn_longest
FROM station
) ranked
WHERE rn_shortest = 1 OR rn_longest = 1
ORDER BY name_length, city;

Pandas Solution

import pandas as pd
import numpy as np

# Calculate city name lengths

station['name_length'] = station['city'].[Link]()

# Find shortest and longest city names

shortest_city = (station.sort_values(['name_length', 'city'])
.iloc[0:1]
[['city', 'name_length']])

longest_city = (station.sort_values(['name_length', 'city'], ascending=[False, True])

.iloc[0:1]
[['city', 'name_length']])

# Combine results
result = [Link]([shortest_city, longest_city]).sort_values(['name_length', 'city'])

# Using numpy for efficient string length calculation

def find_extreme_cities_numpy(df):
cities = df['city'].values
lengths = [Link]([len(city) for city in cities])

# Find indices of min and max length cities

min_idx = [Link]((cities, lengths))[0]
max_idx = [Link]((cities, -lengths))[0]

return [Link][[min_idx, max_idx]][['city', 'name_length']]

7. Tweets' Rolling Averages

Platform: DataLemur
Difficulty: Medium
Description: Calculate 3-day rolling average of tweet counts for each user.
SQL Solution

SELECT
user_id,
tweet_date,
tweet_count,
ROUND(
AVG(tweet_count) OVER (
PARTITION BY user_id
ORDER BY tweet_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
), 2
) as rolling_avg_3d
FROM tweets
ORDER BY user_id, tweet_date;

Pandas Solution

import pandas as pd
import numpy as np

# Ensure data is sorted by user and date

tweets_sorted = tweets.sort_values(['user_id', 'tweet_date'])

# Calculate 3-day rolling average using pandas rolling

tweets_with_rolling = (tweets_sorted
.groupby('user_id')
.apply(lambda x: [Link](
rolling_avg_3d=x['tweet_count'].rolling(window=3, min_periods=1
))
.reset_index(drop=True)
.sort_values(['user_id', 'tweet_date']))

# Using numpy for custom rolling average implementation

def rolling_average_numpy(arr, window=3):
"""Calculate rolling average using numpy convolution"""
if len(arr) < window:
return [Link](len(arr), [Link]())

# Use convolution for efficient rolling calculation

cumsum = [Link](arr)
result = np.zeros_like(arr, dtype=float)

# First window-1 elements

for i in range(min(window, len(arr))):
result[i] = cumsum[i] / (i + 1)

# Remaining elements
for i in range(window, len(arr)):
result[i] = (cumsum[i] - cumsum[i-window]) / window

return [Link](result, 2)
8. Median Google Search Frequency
Platform: StrataScratch
Difficulty: Hard
Description: Calculate median search frequency for each search term using window functions.

SQL Solution

WITH ranked_searches AS (
SELECT
search_term,
search_frequency,
ROW_NUMBER() OVER (PARTITION BY search_term ORDER BY search_frequency) as rn,
COUNT(*) OVER (PARTITION BY search_term) as total_count
FROM google_searches
),
median_calculation AS (
SELECT
search_term,
search_frequency,
rn,
total_count,
CASE
WHEN total_count % 2 = 1 THEN (total_count + 1) / 2
ELSE total_count / 2
END as median_pos_1,
CASE
WHEN total_count % 2 = 1 THEN (total_count + 1) / 2
ELSE total_count / 2 + 1
END as median_pos_2
FROM ranked_searches
)
SELECT
search_term,
AVG(search_frequency) as median_frequency
FROM median_calculation
WHERE rn IN (median_pos_1, median_pos_2)
GROUP BY search_term
ORDER BY search_term;

Pandas Solution

import pandas as pd
import numpy as np

# Simple approach using pandas built-in median

median_frequencies = (google_searches.groupby('search_term')
.agg({'search_frequency': 'median'})
.rename(columns={'search_frequency': 'median_frequency'})
.sort_index())

# Custom median calculation using numpy for educational purposes

def calculate_median_numpy(group):
 frequencies = [Link](group['search_frequency'].values)
n = len(frequencies)

if n % 2 == 1:
return frequencies[n // 2]
else:
return (frequencies[n // 2 - 1] + frequencies[n // 2]) / 2

median_frequencies_custom = (google_searches.groupby('search_term')
.apply(calculate_median_numpy)
.reset_index()
.rename(columns={0: 'median_frequency'}))

# Using numpy percentile for more control

median_frequencies_percentile = (google_searches.groupby('search_term')
.agg({'search_frequency': lambda x: [Link](x, 50)})
.rename(columns={'search_frequency': 'median_frequency'}))

9. SQL Project Planning

Platform: HackerRank
Difficulty: Hard
Description: Find projects with consecutive task IDs and calculate project duration.

SQL Solution

WITH task_groups AS (
SELECT
Start_Date,
End_Date,
Start_Date - ROW_NUMBER() OVER (ORDER BY Start_Date) * INTERVAL '1 day' as group_date
FROM Projects
ORDER BY Start_Date
),
project_periods AS (
SELECT
MIN(Start_Date) as project_start,
MAX(End_Date) as project_end,
DATEDIFF(MAX(End_Date), MIN(Start_Date)) + 1 as duration
FROM task_groups
GROUP BY group_date
)
SELECT
project_start,
project_end
FROM project_periods
ORDER BY duration, project_start;
Pandas Solution

import pandas as pd
import numpy as np
from datetime import timedelta

# Convert dates to datetime

projects['Start_Date'] = pd.to_datetime(projects['Start_Date'])
projects['End_Date'] = pd.to_datetime(projects['End_Date'])

# Sort by start date and create row numbers

projects_sorted = projects.sort_values('Start_Date').reset_index(drop=True)
projects_sorted['row_num'] = range(len(projects_sorted))

# Create grouping key for consecutive projects

projects_sorted['group_date'] = (projects_sorted['Start_Date'] -
pd.to_timedelta(projects_sorted['row_num'], unit='D'))

# Group consecutive projects and find start/end dates

project_periods = (projects_sorted.groupby('group_date')
.agg({
'Start_Date': 'min',
'End_Date': 'max'
})
.rename(columns={
'Start_Date': 'project_start',
'End_Date': 'project_end'
}))

# Calculate duration and sort

project_periods['duration'] = (project_periods['project_end'] - project_periods['project_
result = project_periods.sort_values(['duration', 'project_start'])[['project_start', 'pr

10. Active User Retention

Platform: DataLemur
Difficulty: Hard
Description: Calculate monthly user retention rates for active users.

SQL Solution

WITH monthly_users AS (
SELECT
user_id,
DATE_TRUNC('month', event_date) as activity_month
FROM user_actions
WHERE event_type = 'sign-in'
GROUP BY user_id, DATE_TRUNC('month', event_date)
),
user_next_month AS (
SELECT
curr.user_id,
 curr.activity_month,
CASE
WHEN next_month.user_id IS NOT NULL THEN 1
ELSE 0
END as retained
FROM monthly_users curr
LEFT JOIN monthly_users next_month
ON curr.user_id = next_month.user_id
AND curr.activity_month + INTERVAL '1 month' = next_month.activity_month
)
SELECT
activity_month,
COUNT(user_id) as total_users,
SUM(retained) as retained_users,
ROUND(100.0 * SUM(retained) / COUNT(user_id), 2) as retention_rate
FROM user_next_month
GROUP BY activity_month
ORDER BY activity_month;

Pandas Solution

import pandas as pd
import numpy as np

# Filter sign-in events and extract monthly activity

sign_ins = user_actions[user_actions['event_type'] == 'sign-in'].copy()
sign_ins['activity_month'] = pd.to_datetime(sign_ins['event_date']).dt.to_period('M')

# Get unique user-month combinations

monthly_users = sign_ins.groupby(['user_id', 'activity_month']).size().reset_index(name='

# Create next month column for joining

monthly_users['next_month'] = monthly_users['activity_month'] + 1

# Self-join to find retained users

retention_data = (monthly_users.merge(
monthly_users[['user_id', 'activity_month']],
left_on=['user_id', 'next_month'],
right_on=['user_id', 'activity_month'],
how='left',
suffixes=('', '_next')
))

# Calculate retention metrics

retention_rates = (retention_data.groupby('activity_month')
.agg({
'user_id': 'count',
'activity_month_next': lambda x: [Link]().sum()
})
.rename(columns={
'user_id': 'total_users',
'activity_month_next': 'retained_users'
}))
 retention_rates['retention_rate'] = [Link](100.0 * retention_rates['retained_users'] /

11. Host Popularity Rental Prices

Platform: StrataScratch
Difficulty: Hard
Description: Find correlation between host popularity and rental prices using complex joins.

SQL Solution

WITH host_stats AS (
SELECT
host_id,
COUNT(DISTINCT property_id) as total_properties,
AVG(price) as avg_price,
AVG(number_of_reviews) as avg_reviews,
CASE
WHEN AVG(number_of_reviews) >= 100 THEN 'High'
WHEN AVG(number_of_reviews) >= 50 THEN 'Medium'
ELSE 'Low'
END as popularity_tier
FROM airbnb_host_searches
GROUP BY host_id
),
price_stats AS (
SELECT
popularity_tier,
COUNT(*) as host_count,
AVG(avg_price) as tier_avg_price,
MIN(avg_price) as tier_min_price,
MAX(avg_price) as tier_max_price,
STDDEV(avg_price) as price_stddev
FROM host_stats
GROUP BY popularity_tier
)
SELECT
popularity_tier,
host_count,
ROUND(tier_avg_price, 2) as avg_price,
ROUND(tier_min_price, 2) as min_price,
ROUND(tier_max_price, 2) as max_price,
ROUND(price_stddev, 2) as price_stddev
FROM price_stats
ORDER BY
CASE popularity_tier
WHEN 'High' THEN 1
WHEN 'Medium' THEN 2
WHEN 'Low' THEN 3
END;
Pandas Solution

import pandas as pd
import numpy as np

# Calculate host statistics

host_stats = (airbnb_host_searches.groupby('host_id')
.agg({
'property_id': 'nunique',
'price': 'mean',
'number_of_reviews': 'mean'
})
.rename(columns={
'property_id': 'total_properties',
'price': 'avg_price',
'number_of_reviews': 'avg_reviews'
}))

# Create popularity tiers using cut or conditions

host_stats['popularity_tier'] = [Link](
host_stats['avg_reviews'],
bins=[-[Link], 50, 100, [Link]],
labels=['Low', 'Medium', 'High'],
right=False
)

# Calculate price statistics by tier

price_stats = (host_stats.groupby('popularity_tier')
.agg({
'avg_price': ['count', 'mean', 'min', 'max', 'std']
})
.round(2))

# Flatten column names and rename

price_stats.columns = ['host_count', 'avg_price', 'min_price', 'max_price', 'price_stddev

# Sort by tier priority

tier_order = {'High': 1, 'Medium': 2, 'Low': 3}
price_stats['sort_order'] = price_stats.[Link](tier_order)
price_stats_sorted = price_stats.sort_values('sort_order').drop('sort_order', axis=1)

Key Concepts Covered

SQL Concepts
Window Functions: ROW_NUMBER(), RANK(), AVG() OVER, COUNT() OVER
Common Table Expressions (CTEs): Complex multi-step queries
Self Joins: Finding relationships within same table
Date/Time Functions: DATE_TRUNC, EXTRACT, DATEDIFF
Aggregate Functions: GROUP BY, HAVING, statistical functions
 Subqueries: Correlated and non-correlated subqueries
Conditional Logic: CASE statements, complex WHERE clauses

Pandas Equivalents
GroupBy Operations: .groupby(), .agg(), .apply()
Window Functions: .rolling(), .shift(), .expanding()
Merging/Joining: .merge(), .join(), multiple join types
Date Operations: .dt accessor, period arithmetic
Filtering: .query(), boolean indexing
Statistical Functions: .quantile(), .describe(), .std()
String Operations: .str accessor for text processing

NumPy Techniques
Vectorized Operations: Efficient array computations
Statistical Functions: [Link](), [Link](), [Link]()
Array Operations: [Link](), [Link](), [Link]()
Set Operations: np.intersect1d(), [Link]()
Custom Functions: Implementing SQL-like logic efficiently

Tips for Converting SQL to Pandas/NumPy

1. Start with the SQL logic: Understand what each SQL component does
2. Break down complex queries: Use intermediate DataFrames for CTEs
3. Use method chaining: Create readable pandas pipelines
4. Leverage vectorization: Replace loops with numpy operations
5. Consider memory usage: Use appropriate data types and chunking
6. Test with sample data: Verify equivalence between SQL and pandas results
7. Use pandas query syntax: For complex filtering conditions
8. Group operations wisely: Choose between .groupby().apply() vs .groupby().agg()
This comprehensive collection provides practical examples for converting advanced SQL
interview questions into equivalent pandas and numpy solutions, helping bridge the gap
between SQL and Python data analysis workflows.