SQL Intermediate Practice Questions

Ready to take your SQL skills to the next level? These intermediate questions will help you practice real-world scenarios using GROUP BY, CASE WHEN, subqueries, CTEs, and more.

If you’ve already mastered SQL basics and feel comfortable writing simple queries, it’s time to level up. Intermediate SQL skills are key to handling real-world reporting, deeper analysis, and solving more complex data tasks that go beyond basic SELECT statements.

In this article, you’ll practice essential techniques like GROUP BY, CASE WHEN, subqueries, and Common Table Expressions (CTEs). These exercises are based on realistic scenarios, making them ideal for aspiring data analysts, developers, or anyone preparing for technical interviews. Try solving each challenge before checking the solution to sharpen your problem-solving skills.

If you’re looking to build on these concepts in a more structured way, check out the Creating Basic SQL Reports course on LearnSQL.com. It’s a hands-on, beginner-friendly course focused on turning raw data into meaningful reports – a perfect next step after completing this set of practice questions.

Database for Exercises

To solve the exercises in this article, you’ll use three tables: customer, product, and purchase.

• The customer table includes: customer_id, first_name, last_name, email, signup_date, city, and country.
• The product table contains: product_id, name, category, price, and launch_date.
• The purchase table records transactions and includes: purchase_id, customer_id, product_id, quantity, total_amount, and purchase_date.
  

Exercise 1: Monthly Revenue Summary

Exercise:
Break down the purchase data by year and month, and show the number of orders (orders) and total revenue (revenue) for each period.

Solution:

SELECT
	EXTRACT(YEAR FROM purchase_date) AS purchase_year,
	EXTRACT(MONTH FROM purchase_date) AS purchase_month,
	COUNT(*) AS orders,
	SUM(total_amount) AS revenue
FROM purchase
GROUP BY
	EXTRACT(YEAR FROM purchase_date),
	EXTRACT(MONTH FROM purchase_date)
ORDER BY
	purchase_year,
	purchase_month;

Explanation:

To solve this problem, we need to group purchases by both year and month so that each period (e.g., January 2023, February 2023) is treated separately. We start by extracting the year and month from the purchase_date using the EXTRACT function, which is part of the SQL standard and widely supported. Some databases may have their own alternatives, like DATEPART or TO_CHAR. Grouping by both purchase_year and purchase_month prevents all January purchases from different years from being combined into one group. We then count the number of orders and sum the total revenue for each period.

Exercise 2: Find Recent Orders

Exercise:

Find all purchases made in the last 30 days. Display all columns from the purchase table.

Solution:

SELECT *
FROM purchase
WHERE purchase_date >= CURRENT_DATE - INTERVAL 30 DAY;

Explanation:

To find purchases from the last 30 days, we filter the purchase table using a date condition. We compare purchase_date to the current date minus a 30-day interval. The CURRENT_DATE keyword gives today’s date, and INTERVAL 30 DAY is a standard way to subtract days in ANSI SQL. This ensures we only return rows where the purchase happened within the last 30 days. Some databases may use slightly different syntax for date intervals, but the logic remains the same.

Exercise 3: Products Priced Above Their Category’s Average

Exercise:

Return all products that are more expensive than the average price in their own category. Show all columns from the purchase table.

Solution:

SELECT *
FROM product p
WHERE price >
  	(SELECT AVG(price)
   	FROM product
   	WHERE category = p.category);

Explanation:

To solve this, we compare each product’s price to the average price of products within the same category. We use a subquery that calculates the average price for a given category, and then check if the current product’s price is higher than that average. This is a correlated subquery – it runs once for each row in the outer query, using p.category to match categories. We use SELECT * to return all columns from the product table for products that meet the condition.

Exercise 4: Price Segments: Budget, Middle, Premium

Exercise:

For each product, assign it to a price segment: 'budget' for under $20, 'middle' for $20 – 99.99, and 'premium' for $100 and above.

Solution:

SELECT
	product_id,
	name,
	price,
	CASE
    		WHEN price < 20 THEN 'budget'
    		WHEN price BETWEEN 20 AND 99.99 THEN 'middle'
    		ELSE 'premium'
	END AS price_segment
FROM product;

Explanation:

In this query, we use the CASE expression to assign each product to a price segment based on its price. The CASE syntax works like an if-else structure: it checks conditions in order and returns the corresponding value. Here, products under $20 are labeled 'budget', those between $20 and $99.99 are 'middle', and anything $100 or more is 'premium'. The result includes the original product details along with a new price_segment column.

Exercise 5: Grouping Customers by Region

Exercise:
Group customers into regions based on their country. Assume countries like 'USA', 'Canada', and 'Mexico' belong to 'North America', while all others fall under 'Other'. For each region, count how many customers are assigned to it. The result should include region and customer_count.

Solution:

SELECT
	CASE
    	WHEN country IN ('USA', 'Canada', 'Mexico') THEN 'North America'
    	ELSE 'Other'
	END AS region,
	COUNT(*) AS customer_count
FROM customer
GROUP BY
	CASE
    	WHEN country IN ('USA', 'Canada', 'Mexico') THEN 'North America'
    	ELSE 'Other'
	END;

Explanation:

This query uses the CASE expression to group countries into custom regions – 'North America' for 'USA', 'Canada', and 'Mexico', and 'Other' for the rest. CASE is often used to create your own categories based on column values. Once we define these custom regions, we can group by them to count how many customers fall into each one. This is a common pattern when working with geographical data, pricing tiers, or any custom classification logic.

Exercise 6: Customer Spending by Category

Exercise:

For each customer, calculate how much they’ve spent on products in the 'Electronics', 'Clothing', and 'Home' categories. The result should include customer_id, electronics_spend, clothing_spend, and home_spend as column names.

Solution:

SELECT
	customer_id,
	SUM(CASE 
WHEN category = 'Electronics' 
THEN total_amount ELSE 0 END) AS electronics_spend,
	SUM(CASE 
WHEN category = 'Clothing'	
THEN total_amount ELSE 0 END) AS clothing_spend,
	SUM(CASE 
WHEN category = 'Home'    	
THEN total_amount ELSE 0 END) AS home_spend
FROM purchase  p
JOIN product pr 
ON p.product_id = pr.product_id
GROUP BY customer_id;

Explanation:

In this query, we calculate how much each customer spent on products from specific categories by using CASE WHEN inside aggregate functions. CASE allows us to check the category of each product and return the total_amount only if it matches the one we're interested in – otherwise, it returns 0. We repeat this pattern for 'Electronics', 'Clothing', and 'Home', and wrap each in a SUM() to get the total spending per category. The result includes one row per customer_id with separate columns for spending in each category. This technique is useful for creating pivot-style summaries directly in SQL.

Exercise 7: Average Spending per Customer

Exercise:
Calculate the total amount spent by each customer, then return the average of these customer totals. The final result should include a single column: avg_customer_spend.

Solution:

WITH customer_spend AS (
	SELECT customer_id, SUM(total_amount) AS spend
	FROM purchase
	GROUP BY customer_id
)
SELECT AVG(spend) AS avg_customer_spend
FROM customer_spend;

Explanation:

To solve this, we first calculate how much each customer has spent in total by grouping the purchase data by customer_id and summing the total_amount. We do this in a Common Table Expression (CTE) named customer_spend. Then, in the main query, we calculate the average of these totals using AVG(spend) and return it as avg_customer_spend. This approach helps you separate intermediate calculations and keeps your query clean and readable.

Exercise 8: Above-Average Customers

Exercise:

Find the total amount spent by each customer and the average spend across all customers. Return only those customers whose total spend is above the average. The result should include columns customer_id and total_spend.

Solution:

WITH customer_spend AS ( -- total spend per customer
	SELECT
    	customer_id,
    		SUM(total_amount) AS total_spend
	FROM purchase
	GROUP BY customer_id
), avg_spend AS ( -- 2) average of those totals
	SELECT
    	AVG(total_spend) AS avg_total_spend
	FROM customer_spend
)
SELECT
	cs.customer_id,
	cs.total_spend
FROM customer_spend cs, avg_spend a -- join to include the average in each row
WHERE cs.total_spend > a.avg_total_spend
ORDER BY cs.total_spend DESC;

Explanation:

We begin by calculating the total spend per customer using a CTE called customer_spend. Then, in a second CTE called avg_spend, we calculate the average of those totals. In the main query, we compare each customer's total spend to the average by joining both CTEs. Customers whose total_spend is greater than the average are returned, along with their customer_id. Using two CTEs makes the logic easier to follow and keeps the query clean.

Exercise 9: Product Sales as Percent of Total

Exercise:

Calculate the total revenue for each product. Then, for every product, compute what percentage it contributes to the overall revenue. The result should include product_id, revenue, and pct_of_total.

Solution:

WITH product_sales AS (
	SELECT product_id, SUM(total_amount) AS revenue
	FROM purchase
	GROUP BY product_id
), total_revenue AS (
	SELECT SUM(revenue) AS total
	FROM product_sales
)
SELECT
	ps.product_id,
	ps.revenue,
	ROUND(100.0 * ps.revenue / tr.total, 2) AS pct_of_total
FROM product_sales ps
CROSS JOIN total_revenue tr
ORDER BY pct_of_total DESC;

Explanation:

We start by calculating the total revenue per product using a Common Table Expression (CTE) called product_sales. Next, we create another CTE named total_revenue to calculate the overall revenue from all products. In the final query, we join both CTEs using CROSS JOIN so that every product row has access to the total revenue value. We then compute the percentage contribution of each product by dividing its revenue by the total and multiplying by 100. The result includes product_id, revenue, and pct_of_total, sorted from highest to lowest percentage.

Exercise 10: Active but not in December

Exercise:

Find customers who placed more than one order but never made a purchase in December. The result should include customer_id, first_name, and last_name.

Solution:

WITH multi_order AS (
	SELECT customer_id
	FROM purchase
	GROUP BY customer_id
	HAVING COUNT(*) > 1
), december_buyers AS (
	SELECT DISTINCT customer_id
	FROM purchase
	WHERE EXTRACT(MONTH FROM purchase_date) = 12
)
SELECT
	c.customer_id,
	c.first_name,
	c.last_name
FROM customer c
JOIN multi_order mo 
ON c.customer_id = mo.customer_id
LEFT JOIN december_buyers d 
ON c.customer_id = d.customer_id
WHERE d.customer_id IS NULL;

Explanation:

This query finds customers who placed more than one order but none in December. First, the multi_order CTE identifies customers with more than one purchase by grouping by customer_id and using HAVING COUNT(*) > 1. The december_buyers CTE selects all customers who made at least one purchase in December by extracting the month from purchase_date. In the final query, we join the customer table with multi_order to get the relevant customers, and then use a LEFT JOIN with december_buyers to check who didn’t make a December purchase. We filter out those who did by checking WHERE d.customer_id IS NULL. The result includes only customer_id, first_name, and last_name.

Further SQL Practice

Practicing SQL regularly is one of the most effective ways to move from understanding the theory to feeling confident in real-world scenarios. The exercises in this article covered core intermediate topics like GROUP BY, CASE WHEN, subqueries, and CTEs – all skills that come up frequently in data analysis, reporting, and technical interviews. If you made it this far, you're well on your way to becoming proficient in SQL.

But don't stop here. Consistency is key when learning SQL, and the best way to keep improving is by solving more practical problems. If you're looking for structured, hands-on practice with instant feedback, we highly recommend the SQL Practice track on LearnSQL.com. It’s designed to help you strengthen your skills through real query challenges that build on one another – no fluff, just practice that matters.

Whether you're aiming to land a data job, automate reports, or work more effectively with databases, the SQL Practice track is the next step worth taking.