Practice RANK, DENSE_RANK, ROW_NUMBER, LAG, LEAD and analytical SQL. These 19 interactive challenges use real schemas and instant query validation.
Rank employees by salary within each department using RANK(), then return each employee's department, salary, and position. Return columns: first_name, last_name, department_id, salary, salary_rank. Order results by: salary DESC) AS salary_rank FROM employees ORDER BY department_id, salary_rank.
Concept guide
Window functions calculate across related rows without collapsing them into one row per group. They power ranking, running totals, moving comparisons and sequence analysis.
GROUP BY collapses rows into summaries. Window functions keep the original rows while adding calculations across a defined set of related rows.
Calculate a chronological running total of order amounts and return every order with its date, amount, and cumulative revenue. Return columns: id, order_date, total_amount, running_total. Order results by: order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM orders ORDER BY order_date.
Summarize monthly order volume and revenue, then compare each month with the previous month using LAG(). Return columns: month, orders, revenue, prev_revenue, revenue_change. Order results by: month) AS prev_revenue, revenue - LAG(revenue) OVER (ORDER BY month) AS revenue_change FROM monthly ORDER BY month.
Divide employees into four salary quartiles with NTILE(4), then return each employee's name, salary, and quartile. Return columns: first_name, salary, quartile. Order results by: salary DESC) AS quartile FROM employees ORDER BY salary DESC.
Use DENSE_RANK() to rank products by price inside each category while keeping equal prices at the same rank. Return columns: name, category, price, price_rank. Order results by: price DESC) AS price_rank FROM products ORDER BY category, price_rank, name.
Number each customer's orders chronologically with a window function and return the customer, order date, and sequence number. Return columns: customer_id, order_id, order_date, order_number. Order results by: order_date, id) AS order_number FROM orders ORDER BY customer_id, order_number.
Calculate each employee's percentage share of their department payroll. Return columns: first_name, department_id, salary, payroll_share. Order results by: department_id, salary DESC.
Compare every order amount with the previous chronological order using LAG(), while preserving the complete order sequence. Return columns: id, order_date, total_amount, previous_amount. Order results by: order_date, id) AS previous_amount FROM orders ORDER BY order_date, id.
Rank products within each category by total completed-sales revenue. Rank 1 is the highest distinct revenue; products tied at the third distinct revenue are all included. Return category, product_id, revenue and revenue_rank ordered by category, rank, product_id. Return columns: category, product_id, revenue, revenue_rank.
Return the latest status event for every shipment. Order events by status_at descending, then event_id descending so a higher event_id wins a timestamp tie. Return shipment_id, status, status_at and event_id. Return columns: shipment_id, status, status_at, event_id.
Calculate the running balance for every posted transaction using signed amount (credits positive, debits negative). Within an account, order by transacted_at then transaction_id; this ID rule controls same-time transactions. Return account_id, transaction_id, transacted_at, amount and running_balance. Return columns: account_id, transaction_id, transacted_at, amount, running_balance.
Return only actual product price changes, excluding each product's first event and unchanged consecutive events. Sequence by effective_at then price_event_id, so same-time corrections are deterministic. Return product_id, price_event_id, effective_at, previous_price and new_price. Return columns: product_id, price_event_id, effective_at, previous_price, new_price.
For completed outbound calls and emails, show the next meaningful contact for the same customer. Order by contacted_at then contact_id. The deadline is 7 days after the current contact, and flag whether the next contact missed it. The final contact has NULL next-contact fields. Return customer_id, contact_id, next_contact_id, next_contact_at, deadline_at and deadline_missed. Return columns: customer_id, contact_id, next_contact_id, next_contact_at, deadline_at, deadline_missed.
Calculate the number of days between each customer's consecutive orders while retaining the current and previous order dates. Return columns: customer_id, order_id, order_date, previous_order_date, days_since_previous. Order results by: order_date, id) AS previous_order_date FROM orders) SELECT customer_id, order_id, order_date, previous_order_date, CASE WHEN previous_order_date IS NULL THEN NULL ELSE CAST(JULIANDAY(order_date) - JULIANDAY(previous_order_date) AS INTEGER) END AS days_since_previous FROM sequenced ORDER BY customer_id, order_date.
Produce one row for every calendar date from the first through last sale date, filling missing days with zero revenue. Calculate revenue for that date and the inclusive last seven calendar days. Return revenue_date, daily_revenue and rolling_7_day_revenue. Return columns: revenue_date, daily_revenue, rolling_7_day_revenue.
Calculate the mathematical median salary per department using SQLite-compatible window positioning. For odd counts use the middle value; for even counts average the two middle values. Return department_id, employee_count and median_salary rounded to 2 decimals. Return columns: department_id, employee_count, median_salary.
Payments are duplicates only when merchant_id, external_reference and amount match. Keep one best record per duplicate group using status priority confirmed > pending > failed, then source reliability bank > gateway > import, then processed_at DESC, then payment_id DESC. NULL external references are independent and must not collapse. Return the retained payment rows. Return columns: payment_id, merchant_id, external_reference, amount, status, source, processed_at.
Treat multiple logins by the same user on one calendar date as one active day. Find every user's longest consecutive-day login streak. If multiple islands tie, keep the earliest starting island. Return user_id, streak_start, streak_end and streak_days; users with one active day have a one-day streak. Return columns: user_id, streak_start, streak_end, streak_days.
Aggregate completed revenue per partner, order contributors by revenue DESC then partner_id, and calculate each row's cumulative percentage of total revenue. Classify contributors as first_50, next_30 or remaining according to the cumulative percentage after including that contributor. Return partner_id, revenue, cumulative_revenue, cumulative_percent and contributor_band. Return columns: partner_id, revenue, cumulative_revenue, cumulative_percent, contributor_band.
Both assign the same rank to ties. RANK leaves gaps after ties, while DENSE_RANK continues with the next consecutive rank.