Dev Duel
SQL Challenges
Train with filtering, joins, grouping, subqueries, and window functions.
Filter challenges
Narrow down by difficulty or category.
All challenges
Practice concepts, improve problem-solving, and build confidence.
Find the Second Highest Salary
Return the second highest salary from an employees table.
Calculate Total Sales Per Employee Using SQL GROUP BY
Learn how to aggregate sales data by employee using the SQL GROUP BY clause and aggregate functions in this beginner-friendly challenge.
Calculate Total Sales per Product Using SQL Aggregate Functions
Write an SQL query to find the total sales for each product using aggregate functions.
Find the Top 3 Customers by Total Purchase Amount
Write an SQL query to identify the top 3 customers who have spent the most in total purchases, using a sample sales database.
Find Customers Who Made Purchases Above $100 Using SQL
Practice writing a SQL query to identify customers who have made purchases greater than $100 from a sales table.
SQL Beginner Challenge: Calculate Total Sales per Customer
Practice basic SQL aggregation by calculating the total sales amount for each customer in a sales database.
Find Employees with Salaries Above Department Average
Write an SQL query to find employees whose salaries are higher than the average salary of their respective departments.
Fix the Broken SELECT Query to Retrieve Employee Names
A simple SQL query intended to retrieve all employee names is not working correctly. Your task is to identify and fix the syntax errors so the query runs successfully and returns the correct results.
Build a SQL Function to Calculate Running Median Over a Dynamic Window
In this challenge, you will create a SQL function that calculates the running median value of a numeric column over a dynamic sliding window based on timestamps. This requires advanced use of window functions, array manipulation, and median calculation within SQL.
Design and Query a Library Management Data Model
Create a SQL data model for a library management system and write queries to retrieve meaningful information about books, authors, borrowers, and loans.
Fix Bug in Complex SQL Query Summarizing Sales Data
Debug and fix the provided SQL query that intends to summarize monthly sales data by product category but produces incorrect results due to logical errors and misused joins.
Fix the Bug in SQL Query to Filter Active Users
Identify and fix the bug in the provided SQL query that is supposed to retrieve only active users from the users table.
Fix Incorrect Join and Aggregation in Sales Report Query
A SQL query intended to generate a monthly sales report per product category uses incorrect join syntax and aggregation logic. Fix the query to produce accurate total sales per category per month.
Fix the Incorrect SQL Query Filtering by Date
Identify and fix the bug in the provided SQL query that aims to filter orders placed after January 1, 2023. The original query incorrectly returns all rows due to a syntax mistake in the WHERE clause.
Refactor SQL Query for Sales Aggregation Optimization
Improve the given SQL query that calculates total and average sales per product category. The original query uses multiple subqueries and redundant joins, causing inefficiencies. Refactor it to be cleaner and more performant while preserving the exact results.
Predict the Output of a Recursive CTE with Aggregations and Window Functions
Analyze the output of an advanced SQL query using recursive common table expressions (CTEs), aggregations, and window functions. Predict the final result set given the schema and sample data.
Predict the Output of an SQL Query with Subqueries and Joins
Analyze the given SQL query involving JOINs and a correlated subquery, and predict the final output based on a provided sample database schema and data.
Design and Implement a Scalable E-Commerce Order Tracking Data Model
Build a comprehensive SQL data model supporting order tracking, including customers, products, orders, shipments, and status history, ensuring efficient querying for complex business insights.
Fix the SQL Query to Correctly Calculate Average Salary
This challenge requires you to debug a simple SQL query that attempts to calculate the average salary from an employees table, but the query contains syntax errors that prevent it from running correctly.
Create a SQL Function to Calculate Running Sales Total per Customer
Build a SQL function that calculates the running total of sales for each customer ordered by the sale date.
Refactor Complex SQL Query for Improved Performance and Readability
You are given a large and complex SQL query that fetches sales summary data with multiple subqueries and redundant joins. Refactor the query to improve its readability and optimize performance while ensuring the output remains exactly the same.
Create a SQL Function to Calculate Moving Average Sales Over Dynamic Time Windows
Build a SQL function that calculates the moving average of daily sales for a specified product over a dynamic number of previous days. The function should handle gaps in dates and provide efficient performance for large sales datasets.
Fix the Aggregation Bug in Employee Salary Report Query
An SQL query intended to generate a report summarizing total salaries by department and job title has incorrect aggregation logic causing wrong results. Your task is to identify and fix the bugs in the query to produce accurate aggregation.
Predict the Output of a Simple SQL SELECT with WHERE Clause
Analyze a basic SQL query that filters data using a WHERE clause and predict what the output will be based on the given data.
Refactor and Optimize Complex Sales Reporting SQL Query
Optimize a poorly written, inefficient SQL query that aggregates sales data by region and product category while filtering on multiple conditions. Improve readability, performance and ensure the output remains correct.
Predict the Output of a Complex Window Function and CTE SQL Query
Analyze the given SQL query involving multiple CTEs and window functions to determine the exact output produced by the final SELECT statement.
Fix the Incorrect JOIN Logic in Customer Orders Query
Debug and fix a SQL query that attempts to retrieve customer names alongside their total order counts, but currently returns incorrect results due to improper JOIN usage and grouping.
Create a SQL Function to Calculate Running Median by Group
Build an advanced SQL function that calculates the running median of a numeric column grouped by a category column over a dynamic order, using window functions and optimized querying.
Refactor a Repetitive SQL Query to Use Better Aggregation
Improve a basic SQL query by refactoring repetitive SELECT statements with better use of aggregation and GROUP BY to optimize the code while maintaining the original result.
Fix a Bug in an SQL Query for Aggregated Sales Performance with Window Functions
Identify and fix the bug in a complex SQL query that attempts to calculate monthly sales performance rankings by salesperson, using window functions and joins.
Refactor SQL Query to Optimize Simple SELECT with CASE
Improve the readability and efficiency of a beginner-level SQL query that uses multiple CASE statements to classify product sales data without changing its behavior.
Fix the SQL Query to Retrieve Employees with Salary Over 50000
In this challenge, you are given a broken SQL query intended to select all employees earning more than 50000. Your task is to identify and fix the bugs so that the query returns the correct results.
Debugging an Inefficient Employee Salary Ranking Query
Fix a broken SQL query that attempts to rank employees by salary within each department but produces incorrect rankings and poor performance due to improper use of window functions and filters.
Design and Optimize a Sales Database Schema with Advanced Queries
Create a normalized database schema for a retail sales system that handles customers, products, orders, and shipments. Then, write optimized SQL queries to retrieve key business insights including sales trends, customer segmentation, and shipment statuses.
Refactor SQL Query to Remove Redundant Subquery
Optimize a basic SQL query by refactoring it to remove an unnecessary subquery, improving readability and performance without changing the output.
Design and Query a Multi-Level Employee Management Hierarchy
Create a relational data model to represent an organization's employee hierarchy with multiple reporting levels and implement complex SQL queries to retrieve hierarchical reports and aggregate statistics.
Create a SQL Function to Return the Total Number of Orders for a Customer
Write a simple SQL function that takes a customer ID as input and returns the total count of orders placed by that customer.
Design and Query a Movie Rental Database Schema
Build a normalized relational schema for a movie rental system including customers, movies, rentals, and payments, then write SQL queries to retrieve key business insights.
Create a SQL Function to Calculate Employee Tenure in Years
Write a SQL function that calculates the tenure of an employee in years based on their hire date and an optional end date. The function should return the number of full years the employee has worked.
Design and Query an Employee Hierarchy Data Model
Create a normalized data model to represent an employee hierarchy within a company, then write an advanced SQL query to retrieve hierarchical reports and aggregated information.
Create a Simple Employee Department Summary Query
Build a SQL query to summarize employee data by department with basic aggregation and grouping techniques.
Create a SQL Function to Calculate Customer Lifetime Value (CLV)
Write a SQL function that calculates the Customer Lifetime Value (CLV) based on customer transactions, applying discounting over time and accounting for customer churn rate.
Create a Simple Student Grades Table and Query
Build a beginner-level SQL project to create a basic data model for student grades. Define a table, insert sample data, and write a query to retrieve student names with their corresponding grades.
Refactor SQL Query for Optimized Employee Sales Report
Improve the given SQL query for generating a monthly sales report by employee to increase readability and performance without changing the output.
Fix the Recursive Sales Totals Query for Hierarchical Employees
You are given a broken SQL query intended to calculate total sales made by each employee and their subordinates in a hierarchical company structure. The query uses a recursive CTE but produces incorrect or incomplete totals. Your task is to identify and fix the bugs so that the query accurately computes cumulative sales totals including all levels of subordinates.
Design and Query an Employee-Project Assignment Schema
Create a normalized database schema to manage employees, projects, and their assignments. Write SQL queries to retrieve project involvement and employee workloads.
Refactor Complex Sales Report Query for Performance Optimization
Improve a verbose and inefficient SQL query that generates a monthly sales report by refactoring it into a cleaner, optimized form while maintaining its original behavior.
Create a Simple Employee and Department Tables with Basic Queries
Build and query basic employee and department tables to practice simple SQL data modeling and retrieval.
Create a Function to Get Employee Names by Department
Write a SQL function that returns a list of employee names for a given department from an employees table.
Refactor a SQL Query to Use Simple Aggregation Instead of Subqueries
Improve the readability and performance of a SQL query that calculates total sales per customer by refactoring it from using multiple subqueries to a clean aggregation using GROUP BY.
Write a SQL Function to Calculate the Average Salary from Employee Table
Create a SQL function that calculates the average salary of all employees in a given department using a simple SELECT and AVG aggregate function.
Fix the Incorrect JOIN Condition in Employee Salary Query
Identify and fix the bug in the provided SQL query that calculates the total salary by department. The current query returns incorrect totals due to a faulty JOIN condition.
Refactor to Optimize a Basic Employee Salary Query
Improve the given SQL query that retrieves employee names and salaries, by refactoring it to be more efficient while keeping the result unchanged.
Create a Simple Library Database Schema with Tables and Queries
Build a basic SQL schema for a library system and write queries to retrieve information about books and authors.
Create a Function to Calculate the Total Sales for a Given Product
Write a SQL function that takes a product ID as input and returns the total sales amount for that product from the sales table.
Refactor SQL Query for Retrieving Active Users
Optimize and refactor a simple SQL query to improve readability and performance while maintaining the same output.
Refactor a SQL Query to Improve Readability and Efficiency
You are given a simple SQL query written with redundant expressions and unnecessary complexity. Refactor the query to improve its readability and optimize performance while keeping the same output.
Create a Simple Customer Orders Summary View
Build a SQL query to create a summary view that lists each customer alongside the total number of orders they have placed and the total amount spent.
Refactor SQL Query for Sales Data Aggregation to Improve Performance
Optimize a given SQL query that aggregates sales data by product category and month, making it cleaner and more efficient while preserving its functionality.
Fix the SQL Syntax Error in a Basic SELECT Query
A simple SQL query has a syntax error that causes it to fail. Your task is to identify and fix the bug so that the query returns the correct results from the Employees table.
Refactor SQL Query to Use WHERE Instead of HAVING for Simple Conditions
Improve the given SQL query by refactoring it to replace the HAVING clause with a WHERE clause where appropriate, optimizing performance without changing the result.
Fix the Bug in SQL Query Calculating Running Totals with Incorrect Window Frames
Identify and fix the logical error in the given SQL query that attempts to calculate running totals of sales per customer, but returns incorrect results due to improper window frame specification.
Refactor SQL Query for Improved Performance Using Index-Friendly Joins
You are given a SQL query that fetches customer orders with detailed product information, but the current query is slow and inefficient. Refactor the query to optimize performance while keeping the same result set.
Write a SQL Function to Calculate Total Sales by Product
Create a SQL function that calculates the total sales amount for a given product ID from a sales table.
Create a Basic Employee Directory Table and Query
Build a simple employee directory table and write a query to retrieve filtered information about employees, practicing basic SQL data modeling and SELECT queries.
Refactor a Repetitive SQL Query Using Aliases and Simplified WHERE Clause
Optimize a beginner-level SQL query by refactoring repetitive conditions and using table aliases for clarity. The query fetches all employees from the 'employees' table who belong to certain departments and have a salary above a threshold.
Fix Bug in SQL Query Aggregating Monthly Sales with Incorrect Joins
You are provided with a buggy SQL query that attempts to calculate the total monthly sales per product category. The query currently returns incorrect totals due to join and aggregation mistakes. Your task is to identify and fix these bugs so the query outputs the correct aggregated sales for each category per month.
Build a SQL Function to Find Top Customers by Total Spend
Create a SQL scalar function that calculates the top N customers based on their total purchase amount from sales data.
Design and Query a Scalable E-commerce Order Management Schema
Build a normalized SQL schema to efficiently model orders, customers, products, and payments for a growing e-commerce system. Write an optimized SQL query to report monthly revenue per product category, including handling edge cases such as refunds and partial payments.
Create a Function to Calculate Average Salary by Department
Write an SQL function that calculates the average salary for a given department from an employee table.
Fix the Bug in SQL Query Filtering Customers by Age
A beginner-level debugging challenge where you must identify and fix the error in a SQL query that aims to select customers age 18 and older.
Refactor and Optimize a Complex Customer Sales Ranking Query
Improve an existing SQL query that ranks customers by total sales across multiple regions, enhancing its readability and performance without changing the result.
Refactor a SQL Query for Efficient Employee Sales Aggregation
Improve a provided SQL query that calculates total sales per employee by refactoring it for better readability and optimization, while preserving its original behavior and output.
Build a Simple Employees and Departments Table with Basic Queries
Create two related tables, Employees and Departments, and write SQL queries to retrieve basic information such as employee names, their departments, and departments with employee counts.
Refactor a Query to Find Top Customers Efficiently
Improve an existing SQL query that fetches the top 5 customers based on total purchase amount by refactoring it for better readability and performance.
Design and Query a Normalized E-Commerce Sales Data Model
Create a relational schema to model an e-commerce sales environment and write advanced SQL queries to retrieve complex insights on sales performance and customer behavior.
Design and Query an Employee Shift Scheduling System
Create a normalized SQL data model and implement complex queries to manage and analyze employee shift schedules for a medium-sized company.
Refactor SQL Query to Optimize SELECT and WHERE Clauses
Improve a given SQL query by refactoring it for better readability and performance while maintaining the same result. This challenge focuses on optimizing basic SELECT and WHERE statements for beginners.
Fix the Performance Bug in Recursive Employee Reporting Hierarchy Query
You are given a recursive CTE query designed to retrieve the full reporting hierarchy of employees in an organization, but the query runs very slowly and returns incorrect results with duplicated rows. Your task is to identify and fix the bug(s) in the query to ensure it runs efficiently and returns the correct hierarchical employee reporting structure without duplicates.
Build a SQL Function to Generate a Recursive Hierarchical Employee Path
Create a SQL function that, given an employee ID, returns the full management chain from the employee up to the top-level manager as a single concatenated string.
Predict the Output of a Complex SQL JOIN with Aggregation and Filtering
Analyze the given SQL query that involves multiple JOINs, aggregation, and HAVING clauses, and predict the final output.
Fix the Incorrect JOIN Conditions in SQL Sales Report Query
This challenge presents a SQL query intended to generate a monthly sales summary by joining the sales and products tables. However, the query contains incorrect join logic that leads to incorrect results or excessive rows. Your task is to fix the join conditions and any other issues to produce an accurate sales report.
Fix the Join Logic to Correctly Aggregate Sales by Customer
This challenge asks you to debug a broken SQL query intended to calculate total sales per customer by joining sales and customers tables. The existing query returns incorrect aggregates due to an erroneous join and grouping logic.
Build a Function to Calculate User's Average Monthly Orders
Write an SQL function that calculates the average number of orders a user places per month based on an orders table with timestamps.
Refactor a SQL Query to Simplify and Optimize Filtering
Improve a given SQL query by refactoring it for clarity and better performance while maintaining the same output. This challenge focuses on rewriting a query that uses unnecessary subqueries and redundant filtering.
Fix Bug in SQL Query for Customer Lifetime Value Calculation
Identify and fix the bug in the provided SQL query that attempts to calculate the Customer Lifetime Value (CLV) by aggregating total purchases over all transactions per customer, but currently returns incorrect results due to improper GROUP BY and JOIN logic.
Fix the Bug in SQL Query to Calculate Average Order Value Per Customer
You are given a broken SQL query that attempts to calculate the average order value for each customer from an orders table. The query returns incorrect results due to logical and syntactical bugs. Your task is to identify and fix these bugs so the query produces the correct average order value per customer.
Create a Sales Performance Summary Report Using Window Functions
Build a SQL query that summarizes monthly sales performance per salesperson, including cumulative sales, ranking within each month, and percentage contribution to total sales. This intermediate mini-project involves data modeling concepts such as aggregation, window functions, and joining tables.
Fix Bug in SQL Query for Calculating Monthly Sales Totals
You are given a SQL query that is supposed to calculate the total sales amount per month from a sales table. However, the current query returns incorrect results. Identify and fix the bug so the query correctly aggregates sales by month.
Advanced SQL Output Prediction: Recursive CTE with Window Functions
Analyze the given SQL query utilizing recursive Common Table Expressions (CTEs) combined with window functions, and predict the exact output it will generate based on the provided data.
Create a Simple Employee-Department Relationship Table
Build SQL tables to model employees and departments, then write queries to retrieve employee details along with their department names.
Refactor a Slow Sales Summary Query for Better Performance
Improve the efficiency of a SQL query that summarizes total sales by product category and month by simplifying its logic and optimizing joins and aggregations.
Create a Simple Customer Orders Report Using SQL
Build a SQL query that models a basic data scenario by joining customer and order data to produce a simple report showing total order count and total amount spent per customer.
Fix the JOIN Logic to Retrieve Customers With Recent Orders
You are given a SQL query intended to list all customers who have placed orders in the last 30 days. However, the query returns incorrect results, including customers without recent orders or missing some who should appear. Your task is to identify and fix the bugs related to the JOIN and WHERE conditions so that only customers with orders in the last 30 days are retrieved correctly.
Fix the SQL Query to Correctly Filter Recent Orders
A simple SQL query intended to fetch orders placed in the last 7 days is not returning correct results. Find and fix the bug to make it functional.
Predict the Result of a Multi-Table Join with GROUP BY and HAVING
Analyze the given SQL query with multiple joins, aggregation, and filtering to determine the final output.
Build a Recursive SQL Function to Calculate Employee Management Hierarchy Depth
Create a user-defined SQL function that calculates the depth of management hierarchy for a given employee in an organizational chart stored in a single table. This advanced task requires knowledge of recursive Common Table Expressions (CTEs) and efficient query design.
Build a SQL Function to Calculate the Total Price of an Order
Write a SQL function that takes an order ID and returns the total price of that order by summing up the prices of its items.
Refactor and Optimize a Complex SQL Query Using Window Functions
Given a complex SQL query with multiple nested subqueries and joins, refactor it to improve readability, performance, and maintainability while preserving the original output.
Fix the Bug in SQL Query to Correctly Calculate Employee Department Averages
You are given a SQL query that attempts to calculate the average salary per department along with the department name. However, the query is not returning correct results due to a bug in the JOIN and GROUP BY clauses. Your task is to identify and fix the bug to ensure the query returns each department's name along with the average salary of its employees.