Hard SQL Interview Questions
Ready for senior-level rounds? These questions combine several techniques at once: window functions inside CTEs, gaps and islands, sessionization and multi-step funnel and retention analysis.
20 questions · 20 Hard · about 6 hours · MySQL & PostgreSQL
What you'll practise
- Combining CTEs with window functions
- Gaps and islands
- Sessionization
- Funnel analysis
- Edge cases: ties, NULLs and empty groups
Questions in this plan
User Consecutive 3-Day Login Streak
Detect users who logged into Google services on at least three consecutive calendar days.
Google Ads: Top 2 Performing Ads per Category
Use DENSE_RANK() window function to rank and filter the top 2 highest click-through rate ads within each advertising category.
Department Top Three Salaries
Use DENSE_RANK() window function to find high earners in each separate department.
Trips and Users Cancellation Rate
Calculate unbanned driver and client cancellation rate during peak holiday weekend dates.
Products Never Purchased Together
Cross-reference product catalog against order item baskets to detect inventory pair gaps.
Amazon: Frequently Bought Together
Identify product pairs purchased together in the same order at least 3 times without duplicate pairs or reflexive matches.
Amazon: Prime Monthly Churn Rate
Calculate monthly Prime subscriber churn rates using calendar intervals and conditional status aggregations.
Amazon: Monthly Cohort Retention
Analyze customer retention cohorts across 1-month and 2-month subsequent repurchase cycles.
Longest Login Streaks
Find users who logged in on at least three consecutive days and report their best streak.
User Session Reconstruction
Group raw click events into sessions using a 30-minute inactivity rule.
Median Order Value by Region
Compute the true median order value per region without a built-in median function.
Next-Month Cohort Retention
For each signup-month cohort, measure how many users were active in the following calendar month.
People You May Know
Recommend non-friends who share at least two mutual friends.
Failed Payment Recovery Time
Pair each failed payment with the customer's next successful payment and measure the recovery time.
Monthly Net Revenue by Customer
Net payments, refunds and chargebacks into the month each money movement happened.
Assign the Active Promo to Each Trip
Match each trip to the promotion valid at request time, resolving overlaps deterministically.
Bulk Buyers on First and Last Day
Find customers who bought at least three books on both their first and their last purchase day.
Shows at #1 for Three Weeks
Find TV shows that held the weekly #1 spot for at least three consecutive weeks.
Average Vacant Nights in 2025
Compute the average number of unbooked nights in 2025 across active listings.
Stadium Crowd Streaks
Find days that belong to a run of three or more consecutive busy days.