Window Functions
Advanced
Customer Order Sequence
Number each customer's orders chronologically with a window function and return the customer, order date, and sequence number.
50 XPSolve challenge
Work through 50 advanced SQL exercises in a structured sequence. Each challenge includes a clear problem, schema context and an interactive query editor.
Number each customer's orders chronologically with a window function and return the customer, order date, and sequence number.
Use DENSE_RANK() to rank products by price inside each category while keeping equal prices at the same rank.
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.
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.
Compare every order amount with the previous chronological order using LAG(), while preserving the complete order sequence.
Rank employees by salary within each department using RANK(), then return each employee's department, salary, and position.
Calculate a chronological running total of order amounts and return every order with its date, amount, and cumulative revenue.
Using a CTE, find the highest-paid employee in each department. Return columns: last_name, first_name, max_salary, department_id. Order results by: e.department_id.
Use a CTE to return the three highest-paid employees.
Return customers who placed at least 1 orders. Return columns: order_count, customer_name. Order results by: order_count DESC, customer_name.
Return customers who placed at least 2 orders. Return columns: order_count, customer_name. Order results by: order_count DESC, customer_name.
Return customers who placed at least 3 orders. Return columns: order_count, customer_name. Order results by: order_count DESC, customer_name.
Return customers who placed at least 4 orders. Return columns: order_count, customer_name. Order results by: order_count DESC, customer_name.
Return customers who placed at least 5 orders. Return columns: order_count, customer_name. Order results by: order_count DESC, customer_name.
Return customers who placed at least 6 orders. Return columns: order_count, customer_name. Order results by: order_count DESC, customer_name.
Return customers who placed at least 7 orders. Return columns: order_count, customer_name. Order results by: order_count DESC, customer_name.
Return customers who placed at least 8 orders. Return columns: order_count, customer_name. Order results by: order_count DESC, customer_name.
Find customers whose total completed-order spend exceeds 250. Return columns: total_spend, customer_name. Order results by: total_spend DESC, customer_name.
Find customers whose total completed-order spend exceeds 500. Return columns: total_spend, customer_name. Order results by: total_spend DESC, customer_name.
Find customers whose total completed-order spend exceeds 750. Return columns: total_spend, customer_name. Order results by: total_spend DESC, customer_name.
Find customers whose total completed-order spend exceeds 900. Return columns: total_spend, customer_name. Order results by: total_spend DESC, customer_name.
Find customers whose total completed-order spend exceeds 1200. Return columns: total_spend, customer_name. Order results by: total_spend DESC, customer_name.
Find customers whose total completed-order spend exceeds 1500. Return columns: total_spend, customer_name. Order results by: total_spend DESC, customer_name.
Find customers whose total completed-order spend exceeds 1800. Return columns: total_spend, customer_name. Order results by: total_spend DESC, customer_name.
Find customers whose total completed-order spend exceeds 2500. Return columns: total_spend, customer_name. Order results by: total_spend DESC, customer_name.
List order items with quantity at least 1, including the product name. Return columns: order_id, quantity, unit_price, product_name. Order results by: oi.quantity DESC, oi.id.
List order items with quantity at least 2, including the product name. Return columns: order_id, quantity, unit_price, product_name. Order results by: oi.quantity DESC, oi.id.
List order items with quantity at least 3, including the product name. Return columns: order_id, quantity, unit_price, product_name. Order results by: oi.quantity DESC, oi.id.
List order items with quantity at least 4, including the product name. Return columns: order_id, quantity, unit_price, product_name. Order results by: oi.quantity DESC, oi.id.
List order items with quantity at least 5, including the product name. Return columns: order_id, quantity, unit_price, product_name. Order results by: oi.quantity DESC, oi.id.
List order items with quantity at least 6, including the product name. Return columns: order_id, quantity, unit_price, product_name. Order results by: oi.quantity DESC, oi.id.
List order items with quantity at least 7, including the product name. Return columns: order_id, quantity, unit_price, product_name. Order results by: oi.quantity DESC, oi.id.
List order items with quantity at least 8, including the product name. Return columns: order_id, quantity, unit_price, product_name. Order results by: oi.quantity DESC, oi.id.
Summarize order statuses whose total revenue is above 250. Return columns: status, order_count, total_revenue. Order results by: total_revenue DESC, status.
Summarize order statuses whose total revenue is above 500. Return columns: status, order_count, total_revenue. Order results by: total_revenue DESC, status.
Summarize order statuses whose total revenue is above 750. Return columns: status, order_count, total_revenue. Order results by: total_revenue DESC, status.
Summarize order statuses whose total revenue is above 900. Return columns: status, order_count, total_revenue. Order results by: total_revenue DESC, status.
Summarize order statuses whose total revenue is above 1200. Return columns: status, order_count, total_revenue. Order results by: total_revenue DESC, status.
Summarize order statuses whose total revenue is above 1500. Return columns: status, order_count, total_revenue. Order results by: total_revenue DESC, status.
Summarize order statuses whose total revenue is above 1800. Return columns: status, order_count, total_revenue. Order results by: total_revenue DESC, status.
Summarize order statuses whose total revenue is above 2500. Return columns: status, order_count, total_revenue. Order results by: total_revenue DESC, status.
Count each customer's orders placed on or after 2024-01-01. Return columns: order_count, customer_name. Order results by: order_count DESC, customer_name.
Count each customer's orders placed on or after 2024-01-12. Return columns: order_count, customer_name. Order results by: order_count DESC, customer_name.
Count each customer's orders placed on or after 2024-01-20. Return columns: order_count, customer_name. Order results by: order_count DESC, customer_name.
Count each customer's orders placed on or after 2024-02-01. Return columns: order_count, customer_name. Order results by: order_count DESC, customer_name.
Count each customer's orders placed on or after 2024-02-15. Return columns: order_count, customer_name. Order results by: order_count DESC, customer_name.
Count each customer's orders placed on or after 2024-03-01. Return columns: order_count, customer_name. Order results by: order_count DESC, customer_name.
Count each customer's orders placed on or after 2024-03-08. Return columns: order_count, customer_name. Order results by: order_count DESC, customer_name.
Count each customer's orders placed on or after 2024-03-12. Return columns: order_count, customer_name. Order results by: order_count DESC, customer_name.