SQL Joins Practice: Inner, Left, Self and Anti Joins
Joins are where most interview answers go wrong: rows disappear, duplicate or turn NULL. Work through inner and outer joins, self joins on the same table and anti joins that find rows with no match.
20 questions · 8 Easy · 8 Medium · 4 Hard · about 3.3 hours · MySQL & PostgreSQL
What you'll practise
- INNER JOIN vs LEFT JOIN
- Self joins
- Anti joins and NOT EXISTS
- Join conditions on ranges
- Avoiding join fan-out
Learn the concepts first
Questions in this plan
Users With Zero Searches
Find registered account profiles that have never submitted a search query using an outer join.
Ad Click-Through Rate (CTR)
Calculate the percentage CTR per ad campaign rounded to 2 decimals, safely handling campaigns with zero impressions.
Google Maps: High-Impact Local Guides
Find verified reviewers who submitted at least 2 photo reviews in San Francisco or Mountain View.
Google Drive: External Sensitive File Shares
Audit sensitive files shared with non-Google email addresses to identify potential data exposure risks.
Employees Earning More Than Their Managers
Join the employee table with itself to find staff members whose salary exceeds their direct manager.
Customers Who Never Order
Identify all customer records that have no matching entries in the Orders table.
Rising Temperature
Find all dates where the temperature was higher than the previous day using DATEDIFF.
Amazon: Category Revenue Report
Calculate total sales revenue for each product category, listing categories with zero sales as 0, sorted from highest revenue to lowest.
Google Workspace: Storage Quota Alerts
Calculate total Drive storage per organization and return those exceeding their quota, showing total used and excess GB.
YouTube: Breakout Videos Dominating Channels
Find videos that account for at least 50% of their channel total views, calculating each video view percentage.
Google Play: High-Value Repeat Spenders
Identify key monetization users in target regions who executed 3+ in-app purchases totaling over $100.
Restaurants With Most Orders
Aggregate total order volume per merchant and return the top 5 partner restaurants.
Amazon: Marketplace Seller Return Rate
Identify marketplace merchants with at least 5 fulfilled orders whose customer return rate exceeds 10%.
Amazon: Above City Average Spend
Find customers whose total cumulative purchase spend exceeds the average customer spend in their respective city.
Users Who Heard an Entire Playlist
Identify listeners who played every track of a given playlist at least once.
Frequently Bought Together Pairs
Find product pairs that appear in the same order across multiple orders.
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.
Next-Month Cohort Retention
For each signup-month cohort, measure how many users were active in the following calendar month.