ORDER BY
Beginner
Alphabetical Employee Directory
Show employee last names, first names, and emails alphabetically by last name. Return columns: email, last_name, first_name. Order results by: last_name, first_name.
10 XPSolve challenge
Build practical SQL skills by solving real queries in the browser. Explore schemas, work with realistic data, run each answer, and progress from SELECT fundamentals to joins, CTEs and window functions.
Show employee last names, first names, and emails alphabetically by last name. Return columns: email, last_name, first_name. Order results by: last_name, first_name.
Calculate the average salary for each department. Return columns: avg_salary, department_id. Order results by: department_id.
Count how many orders have the status 'completed'. Return columns: completed_count.
Count the number of employees in each department. Return columns: department_id, employee_count. Order results by: department_id.
Calculate how many customers from each acquisition month (cohort) placed orders in their first month. Return columns: cohort_size, cohort_month, retention_rate, first_month_orders. Order results by: c.cohort_month.
Number each customer's orders chronologically with a window function and return the customer, order date, and sequence number.
Find customers who purchased products from both Software and Infrastructure. Return columns: customer_name, category_count. Order results by: c.name.
List customers who joined on or after January 1, 2021. Return columns: name, country, joined_date. Order results by: joined_date.
Find all customers who have never placed an order. Return columns: id, name, email.
Count customers in each country, sorted by the country name. Return columns: country, customer_count. Order results by: country.
Calculate the number of days between each customer's consecutive orders while retaining the current and previous order dates.
Use DENSE_RANK() to rank products by price inside each category while keeping equal prices at the same rank.
Show all departments and their employee count, including departments with no employees. Return columns: employee_count, department_name. Order results by: d.id.
Show each department name, employee count, and total payroll. Return columns: total_payroll, employee_count, department_name. Order results by: d.id.
Find departments where the average salary is greater than 75,000. Return columns: avg_salary, department_id. Order results by: department_id.
List each employee's full name alongside their department name. Use INNER JOIN. Return columns: last_name, first_name, department_name.
Extract the portion of each employee email before the @ symbol. Return columns: email, username. Order results by: id.
Create a full_name column by concatenating first_name and last_name with a space between them. Return columns: email, full_name.
Count how many employees were hired in each year, ordered by year ascending. Return columns: hire_year, employee_count. Order results by: hire_year ASC.
Use a self LEFT JOIN to list every employee with their direct manager's first name. Return columns: id, first_name, manager_name. Order results by: e.id.
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.
List each managed employee together with their manager's name. Return columns: manager, employee. Order results by: e.id.
Find employees whose salary is higher than the average salary of their department. Return columns: salary, last_name, first_name, department_id.
List employee names and salaries between 70,000 and 90,000, ordered by salary. Return columns: salary, last_name, first_name. Order results by: salary.
Find all employees in department_id = 1 (Engineering), showing their name and salary. Return columns: salary, last_name, first_name.
Find employees with a salary greater than 80,000 and show id, first_name, last_name, and salary. Return columns: id, salary, last_name, first_name.
Find all employees who do not have a manager (manager_id is NULL). Return columns: id, last_name, first_name.
Calculate inventory value for products with fewer than 200 units. Return columns: name, price, stock, inventory_value. Order results by: inventory_value DESC.
Summarize monthly order volume and revenue, then compare each month with the previous month using LAG().
Calculate completed-order revenue for each month. Return columns: month, revenue. Order results by: month.
List the top 3 most expensive products with their names and prices. Return columns: name, price. Order results by: price DESC.
Divide employees into four salary quartiles with NTILE(4), then return each employee's name, salary, and quartile.
Show the order count and total amount for each status. Return columns: status, order_count, total_amount. Order results by: status.
Find orders whose total amount is greater than the average order value. Return columns: id, customer_id, total_amount. Order results by: total_amount DESC.
List pending orders with their date and total amount. Return columns: id, order_date, total_amount. Order results by: order_date.
Compare every order amount with the previous chronological order using LAG(), while preserving the complete order sequence.
Show every product and its revenue, using zero when it has no sales. Return columns: revenue, product_name. Order results by: p.id.
For each product, show the total quantity sold and total revenue from order_items. Include product name. Return columns: product_name, total_revenue, total_quantity. Order results by: total_revenue DESC.
Find products with fewer than 200 units in stock, cheapest first. Return columns: name, price, stock, category. Order results by: price.
Rank employees by salary within each department using RANK(), then return each employee's department, salary, and position.
Find customers who placed more than one order. Return columns: order_count, customer_name. Order results by: order_count DESC, customer_name.
Calculate total revenue (sum of total_amount) per customer, showing customer name and total revenue, sorted by revenue descending. Return columns: customer_name, total_revenue. Order results by: total_revenue DESC.
Calculate a chronological running total of order amounts and return every order with its date, amount, and cumulative revenue.
Write a query to select all columns from the employees table. Return columns: id, email, salary, hire_date, last_name, first_name, manager_id, department_id.
Write a query to retrieve only the first_name and last_name of all employees. Return columns: last_name, first_name.
List all employees ordered by salary from highest to lowest. Return columns: salary, last_name, first_name. Order results by: salary DESC.
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.
Calculate the combined budget of all departments. Return columns: total_budget.
Show every product name in uppercase alongside its category. Return columns: category, product_name. Order results by: id.
List employee names and salaries above 60000, ordered by salary descending. Return columns: salary, last_name, first_name. Order results by: salary DESC, id.
List employee names and salaries above 65000, ordered by salary descending. Return columns: salary, last_name, first_name. Order results by: salary DESC, id.
List employee names and salaries above 70000, ordered by salary descending. Return columns: salary, last_name, first_name. Order results by: salary DESC, id.
List employee names and salaries above 75000, ordered by salary descending. Return columns: salary, last_name, first_name. Order results by: salary DESC, id.
List employee names and salaries above 80000, ordered by salary descending. Return columns: salary, last_name, first_name. Order results by: salary DESC, id.
List employee names and salaries above 85000, ordered by salary descending. Return columns: salary, last_name, first_name. Order results by: salary DESC, id.
List employee names and salaries above 90000, ordered by salary descending. Return columns: salary, last_name, first_name. Order results by: salary DESC, id.
List employee names and salaries above 100000, ordered by salary descending. Return columns: salary, last_name, first_name. Order results by: salary DESC, id.
Return employee names and salaries below 60000, lowest salary first. Return columns: salary, last_name, first_name. Order results by: salary, id.
Return employee names and salaries below 65000, lowest salary first. Return columns: salary, last_name, first_name. Order results by: salary, id.
Showing the first 60 of 250 challenges in this learning path.
Browse and filter all challenges in the Practice Arena