50 Advanced SQL Interview Questions

An enterprise with thousands of employees across multiple management levels. HR wants every employee reporting directly or indirectly to a particular manager, with their depth in the hierarchy.

EmployeeHierarchy(employee_id, employee_name, manager_id)

A logistics company maintains routes between warehouses and wants every possible path between two locations, without revisiting a location.

Routes(source, destination, distance)

An HR system stores leave requests that can overlap or sit back-to-back, and needs them combined into continuous leave periods per employee.

EmployeeLeaves(employee_id, start_date, end_date)

A financial application generates sequential invoice numbers, and the finance team needs every missing number for an audit.

Invoices(invoice_number)

Comparing median salary across departments instead of relying only on the average, which a handful of outlier salaries can skew.

Employees(employee_id, department_id, salary)

A cloud company logs daily server status, and operations wants the single longest unbroken UP streak per server.

ServerStatus(server_id, status_date, status)

A platform treats user activity as one session until the user is inactive for more than 30 minutes.

UserEvents(user_id, event_time)

A banking analytics system wants the largest combined value of any three consecutive transactions, per account.

AccountTransactions(account_id, transaction_id, transaction_time, amount)

A payment system wants the fewest coins, from a set of available denominations, that sum to a target amount.

CoinDenominations(denomination) — example: 1, 3, 4. Target: 6.

HR wants continuous integer salary bands actually represented by real employee salaries, with at least three distinct values in a row.

Employees(employee_id, salary)

An e-commerce platform has one laptop left, and two customers try to buy it at the same instant.

Inventory(product_id, available_quantity)

Moving ₹10,000 from account 101 to account 102, without ever leaving a negative balance or a half-finished transfer.

Accounts(account_id, balance)

20 background workers each need to independently claim one pending job, without two workers ever picking the same one.

Jobs(job_id, job_status, created_at, worker_id)

A SaaS API allows at most 100 requests per user in any rolling 60-second window, and needs to find who's over the limit.

ApiRequests(request_id, user_id, request_time)

Two admins open the same customer profile and both edit it. One save should never silently overwrite the other's unseen changes.

CustomerProfiles(customer_id, customer_name, email, version)

A hotel booking system needs to catch two reservations for the same room that overlap in time.

RoomBookings(booking_id, room_id, check_in, check_out)

A payment gateway receives the same payment request more than once, because a client retried after a network timeout.

Payments(payment_id, idempotency_key, customer_id, amount, payment_status)

Daily customer snapshots are kept, and the team needs to know exactly which records actually changed between yesterday's and today's.

CustomerSnapshotOld(customer_id, customer_name, email, city), CustomerSnapshotNew(same columns)

An orders table with 100 million rows, where OFFSET-based pagination gets slower and slower on deeper pages.

Orders(order_id, customer_id, order_date, total_amount)

Every salary change must be logged, for compliance, without relying on every caller remembering to write the audit row themselves.

Employees(employee_id, salary), SalaryAudit(audit_id, employee_id, old_salary, new_salary, changed_at)

Find carts holding products that never turned into an order within 24 hours of being added.

CartItems(cart_id, customer_id, product_id, added_at), Orders(order_id, customer_id, order_date)

What percentage of customers acquired in a given month are still active in later months.

CustomerActivity(customer_id, activity_date)

Classifying customers by Recency, Frequency, and Monetary value, the standard marketing-analytics segmentation model.

Orders(customer_id, order_date, total_amount)

Find subscriptions marked ACTIVE whose end_date has already passed, with no follow-up subscription covering the gap.

Subscriptions(subscription_id, customer_id, start_date, end_date, status)

Revenue reporting that actually nets out successful refunds against successful payments, per order.

Payments(payment_id, order_id, amount, status), Refunds(refund_id, order_id, refund_amount, status)

Flagging any salary change greater than 30% over the employee's own previous recorded salary, for internal review.

SalaryHistory(employee_id, effective_date, salary)

Find customers who have purchased at least one product from every single product category that exists, not just several.

Customers, Orders, OrderItems, Products(product_id, category)

A simpler, single-order version of the previous question: find orders whose items span at least three different categories.

Orders, OrderItems, Products(product_id, category)

For every customer and every month they transacted, find both their very first and very last transaction.

Transactions(customer_id, transaction_id, transaction_time, amount)

Detect customers with three failed payment attempts in a row, a common early-warning signal for payment risk.

PaymentAttempts(attempt_id, customer_id, attempt_time, status)

Find products whose actually-usable stock (available minus already-reserved) has dropped below its reorder threshold.

WarehouseInventory(warehouse_id, product_id, available_stock, reserved_stock, reorder_level)

How long tickets actually take to resolve, broken down by priority level.

SupportTickets(ticket_id, customer_id, created_at, resolved_at, priority)

Different priority levels have different resolution-time targets (Critical: 2h, High: 8h, Medium: 24h, Low: 72h); find every ticket that blew past its own.

SupportTickets(ticket_id, priority, created_at, resolved_at)

Find products whose monthly revenue has dropped for three consecutive calendar months in a row, a genuine early-warning signal.

Orders, OrderItems(order_id, product_id, quantity, unit_price)

Identify genuinely engaged customers: ones who bought something in at least five different calendar months over the last year.

Orders(customer_id, order_date, status, total_amount)

Customer data imported from multiple systems has duplicates; keep only the most recently updated version of each.

CustomerImport(record_id, customer_id, customer_name, email, updated_at)

A data warehouse must keep a customer's full address history, not overwrite it, when a customer moves.

DimCustomer(customer_id, city, valid_from, valid_to, is_current)

A source table has millions of rows; load only records modified since the last successful run, inserting new ones and updating changed ones.

SourceCustomers(customer_id, customer_name, email, updated_at), TargetCustomers(same columns)

After migrating transactions between two databases, find exactly what's missing or mismatched.

SourceTransactions(transaction_id, amount, status), TargetTransactions(same columns)

A reporting dashboard recomputes the same customer-revenue aggregation from hundreds of millions of orders, on every single page load.

Orders(customer_id, total_amount, status, order_date)

Package tracking events from multiple devices can arrive at the server in a different order than they actually happened.

TrackingEvents(package_id, event_id, event_time, received_at)

A fintech platform caps successful daily spending at ₹50,000 per customer; find the transactions that pushed a customer over it.

Transactions(transaction_id, customer_id, transaction_time, amount, status)

Find customer records nobody has touched in 180 days, candidates for archival.

CustomerAccessLog(customer_id, last_accessed_at)

Creating an order must also reliably publish an ORDER_CREATED event, but if the DB transaction succeeds and the message broker call fails separately, the event must not just vanish.

Orders(order_id, customer_id, total_amount), OutboxEvents(event_id, aggregate_id, event_type, payload, created_at, published_at)

A classic interview staple: find the 5th-highest distinct salary, without reaching for LIMIT/OFFSET or TOP at all.

Employees(employee_id, employee_name, salary)

Previously active customers who haven't completed a purchase in the last six months, a standard churn-risk flag.

Orders(customer_id, order_date, status)

A hospital needs to catch any employee scheduled for two shifts that overlap in time.

EmployeeShifts(shift_id, employee_id, shift_start, shift_end)

Comparing two landing-page variants (A and B) by what fraction of exposed users actually converted.

ExperimentEvents(user_id, variant, event_type, event_time)

A project management tool needs to catch task A depending on B depending on ... eventually depending back on A, before it causes a deadlock in actual task scheduling.

TaskDependencies(task_id, depends_on_task_id)

Rank customers by actual profit, not revenue: gross sales minus discounts, minus refunds, minus the cost of goods sold.

Orders(order_id, customer_id, discount_amount, status), OrderItems(order_id, product_id, quantity, unit_price), Products(product_id, cost_price), Refunds(order_id, refund_amount, status)