SQL questions.
64 sqlquestions from the bank — open to read. Pick one and practice it out loud; a coach note comes back in seconds.
Learn the ideas first
All 64 questions
You 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.From a transactions table with user_id, ts, and amount, write a query that returns each user's running total ordered by time.You 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.Given 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.From an orders table, write a query that returns customers who placed more orders this quarter than they did last quarter.You have an employees table with manager_id pointing to another employee. Write a query that returns each employee with their manager's name.Given a leaderboard table with user_id and score, write a query that returns each user's rank, breaking ties by user_id.Write a query that returns the median order value per region from an orders table with region and total_cents.Given 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.A 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.Write a query that returns customers who bought product A but never product B, given an orders table with customer_id and product_id.From a salaries table with department_id and salary, write a query that returns the top 3 earners in each department.Given 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.From 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.A nightly query has been slowing down 5% per week as data grows. Walk me through where you'd look to fix it.Given a payments table with status that can be PENDING, COMPLETE, or FAILED, write a query that returns the latest non-failed payment per user.From an account_balances table snapshotted daily, write a query that returns the largest single-day drop per account in the last 30 days.A WHERE clause on a high-cardinality string column is slow. The column is already indexed. What might be going wrong?Given a sessions table, write a query that returns users whose average session duration is in the top 10% of all users.You 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.Write a query that returns the 7-day rolling sum of revenue per region from a daily_revenue table.Given 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'.Given an orders table with customer_id and order_ts, write a query that returns the second-most-recent order per customer.A query joining two large tables completes when run alone but locks under concurrency. Walk me through what to look at.From an events table with event_type, write a query that returns funnel completion: signup → first action → purchase, with conversion at each step.Write a query that returns every product not currently in any active promotion. The promotions table holds product_id, start_ts, and end_ts.Given orders and refunds tables, write a query that returns each customer's net revenue.Write a query that flags every transaction that's more than 3x the median transaction amount for that user historically.An analyst's query runs fine but doubles execution time when wrapped in a CTE. Walk me through what's going on.Write a query that returns a daily count of new users and active users in one result set.You 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?Given 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.From 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.You 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.Given 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.You 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?From 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.Explain what EXPLAIN or EXPLAIN ANALYZE does in SQL and when you would use it to troubleshoot a slow query.You 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?You 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.Given 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'.From 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.You 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.Given 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.From 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.You 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.A 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.Given 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.You 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.From 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.You 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.Given 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.From 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?You 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.Given 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.From 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.Your 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.Given 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.From 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.You 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.Given 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.From 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?You 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.Given 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).