SQL
Rank Records Using Common Table Expressions (CTEs) and Window Functions
Utilize SQL Common Table Expressions (CTEs) and window functions like ROW_NUMBER() to rank records within groups, powerful for leaderboards or top N queries.
WITH UserOrderCounts AS (
SELECT
user_id,
COUNT(order_id) AS order_count,
SUM(total_amount) AS total_spent
FROM orders
GROUP BY user_id
),
RankedUsers AS (
SELECT
user_id,
order_count,
total_spent,
ROW_NUMBER() OVER (ORDER BY total_spent DESC) AS spend_rank
FROM UserOrderCounts
)
SELECT
user_id,
order_count,
total_spent,
spend_rank
FROM RankedUsers
WHERE spend_rank <= 5;
How it works: This snippet uses Common Table Expressions (CTEs) to first calculate the total orders and spending for each user, and then assigns a rank based on their total spending using the `ROW_NUMBER()` window function. The final `SELECT` statement then retrieves the top 5 users by spend, demonstrating a powerful way to perform multi-step data processing and ranking within SQL.