Window Functions
Advanced
PRO
Month-over-Month Order Growth
Summarize monthly order volume and revenue, then compare each month with the previous month using LAG().
50 XPSolve challenge
Practice RANK, DENSE_RANK, ROW_NUMBER, LAG, LEAD and analytical SQL. These 9 interactive challenges use real schemas and instant query validation.
Summarize monthly order volume and revenue, then compare each month with the previous month using LAG().
Divide employees into four salary quartiles with NTILE(4), then return each employee's name, salary, and quartile.
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 each employee's percentage share of their department payroll. Return columns: salary, first_name, department_id, payroll_share. Order results by: department_id, salary DESC.
Use DENSE_RANK() to rank products by price inside each category while keeping equal prices at the same rank.
Compare every order amount with the previous chronological order using LAG(), while preserving the complete order sequence.
Calculate a chronological running total of order amounts and return every order with its date, amount, and cumulative revenue.
Number each customer's orders chronologically with a window function and return the customer, order date, and sequence number.
Rank employees by salary within each department using RANK(), then return each employee's department, salary, and position.
Calculate the number of days between each customer's consecutive orders while retaining the current and previous order dates.
Both assign the same rank to ties. RANK leaves gaps after ties, while DENSE_RANK continues with the next consecutive rank.