RANK and DENSE_RANK

Lesson content

RANK and DENSE_RANK RANK and DENSE_RANK is a practical SQL concept used to design, query, secure, operate, or analyze relational data correctly. Example SELECT name, RANK() OVER (ORDER BY score DESC), DENSE_RANK() OVER (ORDER BY score DESC) FROM results; Key point Use the smallest correct statement, test it with representative data, and verify constraints and performance before production use. Real-life example A sales manager needs a monthly report showing revenue, order counts, rankings, and changes over time without exporting raw data to a spreadsheet. Advanced example SELECT customer_id, ordered_at, total, SUM(total) OVER ( PARTITION BY customer_id ORDER BY ordered_at, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS customer_running_total FROM orders; Expected result The schema or operation satisfies the stated rule, rejects invalid data, and can be verified with a repeatable query. Production check Test with empty, duplicate, null, and boundary values. Use a transaction for related writes. Inspect the execution plan before adding an index. Use parameterized queries for application input. Continue with the PicoStore database This lesson reuses picostore . Relevant tables: customers, products, orders, order_items . Keep the starter rows from the Introduction lesson so results remain comparable. Another practical example SELECT order_id, customer_id, ordered_at, total, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at) AS customer_order_number, SUM(total) OVER (PARTITION BY customer_id ORDER BY ordered_at, order_id) AS running_spend FROM orders; Check the result Run the verification query, compare the returned rows with the starter data, and explain why every included or excluded row is correct. Easy example Start with a small customer table and retrieve active customers in a predictable order. SELECT customer_id, name, email FROM customers WHERE status = 'active' ORDER BY name; How to verify the easy example Run it with representative input. Confirm the expected output. Try one missing, invalid, or boundary value. Advanced example Use a CTE and a window function to rank customer revenue while keeping the query readable and testable. WITH customer_revenue AS ( SELECT customer_id, SUM(total_amount) AS revenue FROM orders WHERE order_status = 'completed' GROUP BY customer_id ) SELECT customer_id, revenue, DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank FROM customer_revenue ORDER BY revenue_rank, customer_id; Advanced review Explain the tradeoffs and assumptions. Test failure, scale, security, and recovery behavior. Capture evidence from tests, execution plans, logs, or review output. Additional practical guidance RANK and DENSE_RANK: MySQL and PostgreSQL Modern MySQL and PostgreSQL support the main window-function syntax, but advanced grouping and pivot techniques differ. Always make the window ORDER BY deterministic. Required verification Run the simple case. Test a NULL, duplicate, empty, or boundary case where relevant. Confirm the affected rows or query result. Use EXPLAIN for performance-sensitive queries.