SQL questions.

64 sql questions from the bank, ready to practice out loud.

Learn the ideas first · free with an account

Joins that keep the right rows Why your query is slow: reading the plan Window functions: running totals, ranks and gaps

All 64 questions

Customer spend including non-buyersYou have an orders table and a customers table. Write a query that returns every customer along with their total spend, including customers who have never ordered.JoinsEntry–mid

Running total by userFrom a transactions table with user_id, ts, and amount, write a query that returns each user's running total ordered by time.Window functionsMid–senior

Multi-day login countYou have a logins table with user_id and login_ts. Write a query to return the number of users who logged in on at least three distinct days in the last week.AggregationMid–senior

Time between consecutive user eventsGiven a table of events with user_id and event_ts, write a query that returns the time between each event and the next one for the same user.Window functionsMid–senior

Customers with quarter-over-quarter growthFrom an orders table, write a query that returns customers who placed more orders this quarter than they did last quarter.SubqueriesMid–senior

Employee with manager nameYou have an employees table with manager_id pointing to another employee. Write a query that returns each employee with their manager's name.JoinsEntry–mid

User rank with tiebreakerGiven a leaderboard table with user_id and score, write a query that returns each user's rank, breaking ties by user_id.Window functionsEntry–mid

Median order value by regionWrite a query that returns the median order value per region from an orders table with region and total_cents.AggregationMid–senior

Users with overlapping sessionsGiven user_sessions with user_id, session_start, and session_end, write a query that returns the percentage of users whose sessions overlap with another of their own.Window functionsSenior–staff+

Slow billion-row daily aggregationA query that aggregates a billion-row events table by day runs in 40 seconds. The customer is unhappy. Walk me through how you'd investigate.Query planningSenior–staff+

Bought A but never BWrite a query that returns customers who bought product A but never product B, given an orders table with customer_id and product_id.JoinsMid–senior

Top three earners per departmentFrom a salaries table with department_id and salary, write a query that returns the top 3 earners in each department.SubqueriesMid–senior

Price at specific dateGiven a price-history table with product_id, valid_from, and price, write a query returning the price at exactly 2025-01-01 for every product.Window functionsMid–senior

Conversion rate by signup cohortFrom a sessions table with user_id and event_type, write a query that returns the conversion rate from 'view' to 'purchase', per user cohort by signup month.AggregationSenior–staff+

Nightly query degrading over timeA nightly query has been slowing down 5% per week as data grows. Walk me through where you'd look to fix it.Performance tuningSenior–staff+

Latest non-failed payment per userGiven a payments table with status that can be PENDING, COMPLETE, or FAILED, write a query that returns the latest non-failed payment per user.JoinsMid–senior

Largest daily balance dropFrom an account_balances table snapshotted daily, write a query that returns the largest single-day drop per account in the last 30 days.Window functionsMid–senior

Slow indexed string column queryA WHERE clause on a high-cardinality string column is slow. The column is already indexed. What might be going wrong?IndexesSenior–staff+

Top 10% session durationGiven a sessions table, write a query that returns users whose average session duration is in the top 10% of all users.SubqueriesSenior–staff+

Click-through rate by ad campaignYou have a clicks table and an impressions table, both keyed by user_id and timestamp. Write a query returning click-through rate per ad campaign over a given date range.JoinsMid–senior

7-day rolling revenue sumWrite a query that returns the 7-day rolling sum of revenue per region from a daily_revenue table.AggregationMid–senior

Signup to churn lifecycleGiven an events table with event_type and user_id, write a query that returns users whose first event was 'signup' and last event was 'churn'.Window functionsMid–senior

Second-most-recent order per customerGiven an orders table with customer_id and order_ts, write a query that returns the second-most-recent order per customer.SubqueriesMid–senior

Query locking under concurrent loadA query joining two large tables completes when run alone but locks under concurrency. Walk me through what to look at.Performance tuningSenior–staff+

Funnel completion with conversion ratesFrom an events table with event_type, write a query that returns funnel completion: signup → first action → purchase, with conversion at each step.AggregationSenior–staff+

Products not in active promotionsWrite a query that returns every product not currently in any active promotion. The promotions table holds product_id, start_ts, and end_ts.JoinsMid–senior

Customer net revenue after refundsGiven orders and refunds tables, write a query that returns each customer's net revenue.SubqueriesMid–senior

Transactions exceeding user medianWrite a query that flags every transaction that's more than 3x the median transaction amount for that user historically.Window functionsSenior–staff+

CTE causing query slowdownAn analyst's query runs fine but doubles execution time when wrapped in a CTE. Walk me through what's going on.Performance tuningSenior–staff+

New and active users dailyWrite a query that returns a daily count of new users and active users in one result set.AggregationMid–senior

Products with category names joinYou have a products table and a categories table with category_id as the foreign key. Write a query that returns all products along with their category name. What type of join would you use and why?JoinsEntry–mid

Product sales and percentage contributionGiven a sales table with sale_id, product_id, and amount, write a query that returns each product's total sales and the percentage that product contributes to overall sales.AggregationEntry–mid

Employee salary versus department averageFrom an employees table with employee_id, department_id, and salary, write a query using a window function that shows each employee alongside the average salary in their department.Window functionsEntry–mid

Orders above average order totalYou have an orders table. Write a query that returns all orders where the order total is greater than the average order total across all orders. Use a subquery in your solution.SubqueriesEntry–mid

Users with extreme rating contrastGiven a reviews table with user_id and rating, write a query that returns users who have given at least one 5-star rating and at least one 1-star rating.SubqueriesEntry–mid

Indexing date range queriesYou notice a query filtering on created_at between two dates is running slowly on a large table. The created_at column has no index. What would you recommend and why?IndexesEntry–mid

Previous page viewed per userFrom a page_views table with user_id, page, and view_ts, write a query that returns the previous page viewed by each user for every page view, ordered by timestamp.Window functionsEntry–mid

Using EXPLAIN for query analysisExplain what EXPLAIN or EXPLAIN ANALYZE does in SQL and when you would use it to troubleshoot a slow query.Query planningEntry–mid

Indexing strategy for aggregationsYou have a large transactions table and need to frequently query total transaction amounts by user. The query scans the full table each time. What indexing or table design approach might help performance?Performance tuningEntry–mid

Products with sales drop-offYou have a products table and a sales table with product_id, sale_date, and quantity. Write a query that returns products that had zero sales in the most recent month but had sales in the prior month.JoinsMid–senior

First three activities patternGiven a table of user_activity with user_id, activity_date, and activity_type, write a query that returns users whose first three activities were all of type 'view' rather than 'purchase' or 'click'.Window functionsMid–senior

Maximum consecutive winning streakFrom a trades table with trader_id, trade_ts, symbol, and profit_loss, write a query that returns the maximum consecutive winning streak (profitable trades) for each trader.Window functionsSenior–staff+

Referrers with qualified referralsYou have a referrals table with referrer_id and referred_id. Write a query that returns users who have referred at least 5 people, where at least 3 of those people also made a referral.SubqueriesMid–senior

Most common page navigation pathGiven a page_views table with user_id, page_url, and view_ts, write a query that returns the most common page-to-page navigation path (sequences of exactly two pages) across all users.Window functionsMid–senior

Monthly recurring revenue calculationFrom a subscriptions table with user_id, plan_type, start_date, and end_date, write a query that calculates monthly recurring revenue (MRR) for each month, accounting for upgrades, downgrades, and cancellations.AggregationSenior–staff+

Two-leg journey query with layoverYou have a flights table with origin, destination, departure_time, and arrival_time. Write a query to find all possible two-leg journeys from city A to city C (via any intermediate city B) where the layover is between 1 and 4 hours.JoinsSenior–staff+

Batch insert deadlock errorsA daily batch job that inserts 10 million rows into a fact table has started failing with deadlock errors. Walk me through your diagnostic approach and potential solutions.Performance tuningSenior–leadership

Month-over-month order value growthGiven an orders table with customer_id, order_date, and amount, write a query that returns customers whose average order value has increased in each of the last three months compared to the prior month.Window functionsSenior–staff+

A/B test conversion liftYou have a table of experiments with user_id, variant (A or B), and converted (boolean). Write a query that returns, for each variant, the number of users, the number who converted, and the conversion rate, plus the absolute lift of variant B over variant A.AggregationMid–senior

Detecting suspicious transaction activityFrom a transactions table with account_id, transaction_ts, and amount, write a query to detect accounts with suspicious activity: three or more transactions over $5000 within any 10-minute window.Window functionsMid–senior

Indexing strategy for production tableYou need to add an index to a 500GB table that's heavily used in production. Explain your strategy to minimize downtime and impact on active queries.IndexesSenior–leadership

Mutual message exchangesGiven a messages table with sender_id, recipient_id, and sent_at, write a query that returns pairs of users who have exchanged at least one message in both directions, along with the date of their first mutual exchange.JoinsMid–senior

Full table scan causeFrom a pageviews table partitioned by date, your query that filters on user_id and date range is doing a full table scan. What are the most likely causes and how would you fix it?Query planningSenior–staff+

Providers seeing same patient consecutivelyYou have a claims table with claim_id, patient_id, provider_id, service_date, and amount. Write a query that returns providers who saw the same patient on three or more consecutive days.Window functionsMid–senior

Top posts by nested engagementGiven a posts table with post_id, author_id, parent_post_id (for replies), and created_at, write a query that returns the top 10 posts by total engagement, where engagement includes the post itself plus all nested replies.SubqueriesSenior–staff+

Refinanced at lower rateFrom a loans table with borrower_id, loan_date, principal, and interest_rate, write a query that returns borrowers who refinanced (took a new loan within 60 days of a previous one) at a lower rate.JoinsMid–senior

Composite index column orderYour database has a composite index on (user_id, created_at). Explain when this index would be used, when it wouldn't, and how column order matters for query performance.IndexesMid–senior

Price percent change historyGiven a prices table with product_id, effective_date, and price, write a query that returns each product's price history with the percent change from its previous price.Window functionsEntry–mid

Drugs with positive trial outcomesFrom a clinical_trials table with trial_id, drug_id, start_date, end_date, and outcome, write a query that returns drugs tested in at least 5 trials where more than 60% had positive outcomes.AggregationMid–senior

WHERE clause slows indexed queryYou notice that adding a WHERE clause on an indexed column actually makes your query slower. Walk me through possible explanations and how you'd investigate.Query planningSenior–staff+

Stock imbalance across warehousesGiven an inventory table with warehouse_id, product_id, date, and quantity, write a query that identifies products that were out of stock in at least one warehouse while overstocked (>200% of average) in another on the same date.SubqueriesSenior–staff+

Distinct users at scale timeoutFrom an ad_impressions table with billions of rows, you need to count distinct users per campaign per day. The query times out. What are three different approaches you'd consider?Performance tuningSenior–leadership

Documents with consecutive single-user editsYou have a versions table tracking document edits with doc_id, version_number, edited_by, and edited_at. Write a query that returns documents where the same user made three consecutive edits with no other users editing in between.Window functionsSenior–staff+

Time-series index designGiven a time-series metrics table with millions of rows per day, explain how you'd design indexes to optimize both real-time dashboard queries (last hour) and historical reporting queries (monthly aggregates).IndexesStaff+–leadership