You have been given a table
Orders that contain the following columns:
You need to find the top 2 customers who have spent the most money in the last 30 days. However, the catch is that the customer must have made at least one order in each of the last 4 weeks, otherwise, they should not be considered.
The Input Data
Here's the SQL script to create the
Orders table and insert some sample data into it:
USE challenges; GO DROP TABLE IF EXISTS Orders; CREATE TABLE Orders ( OrderId int PRIMARY KEY, CustomerId int, OrderDate datetime, TotalAmount money ); INSERT INTO Orders VALUES (1, 1, '2023-03-28 12:00:00', 100), (2, 1, '2023-04-01 09:30:00', 50), (3, 1, '2023-04-10 15:45:00', 200), (4, 1, '2023-04-18 08:00:00', 150), (5, 2, '2023-03-30 11:00:00', 75), (6, 2, '2023-04-05 14:30:00', 125), (7, 2, '2023-04-12 10:15:00', 50), (8, 2, '2023-04-20 16:00:00', 100), (9, 3, '2023-04-01 13:30:00', 250), (10, 3, '2023-04-14 09:00:00', 175), (11, 3, '2023-04-21 11:45:00', 200), (12, 4, '2023-03-28 15:15:00', 100), (13, 4, '2023-04-04 10:00:00', 100), (14, 4, '2023-04-11 12:30:00', 75), (15, 4, '2023-04-18 09:45:00', 125);
Orders table contains 15 rows of data. The table has four columns:
TotalAmount. Each row represents an order made by a customer.
Here's the expected output of the SQL query:
To solve this challenge, you need to find the top 5 customers who have spent the most money in the last 30 days, subject to the condition that they have made at least one order in each of the last 4 weeks.
To accomplish this, you need to perform the following steps:
Calculate the total amount spent by each customer in the last 30 days. You can do this by filtering the
OrderDateto only include orders made in the last 30 days, and then grouping the data by
CustomerIdand summing the
Determine which customers have made orders in each of the last 4 weeks. To do this, you can use a
DATEPARTfunction to find out the week number from the order date. The distinct count of the week number should be 4 to be considered as the customer has purchased each of the weeks. If a customer has not made an order in each of the last 4 weeks, they should be excluded from the result set.
Order the results by the total amount spent in descending order and return the top 2 rows.
The expected output shows the
CustomerId and the total amount spent in the last 30 days by each customer who meets the condition of having made at least one order in each of the last 4 weeks.
Here's the SQL script that solves the challenge:
WITH CTE AS ( SELECT CustomerId, SUM(TotalAmount) AS TotalSpentLast30Days FROM Orders WHERE OrderDate >= DATEADD(day, -30, GETDATE()) GROUP BY CustomerId ) SELECT CTE.CustomerId, CTE.TotalSpentLast30Days FROM CTE WHERE EXISTS ( SELECT 1 FROM Orders WHERE CustomerId = CTE.CustomerId GROUP BY CustomerId HAVING COUNT(DISTINCT DATEPART(week, OrderDate)) = 4 ) ORDER BY CTE.TotalSpentLast30Days DESC OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY;
The solution uses a Common Table Expression (CTE) to first calculate the total amount spent by each customer in the last 30 days. The
WHERE clause filters the
Orders table to only include orders made in the last 30 days, and the
GROUP BY clause groups the data by
CustomerId and sums the
The outer query then filters the results based on the condition that each customer has made at least one order in each of the last 4 weeks. This is done using a subquery that groups the
Orders table by
CustomerId. The subquery then checks if the number of distinct week numbers is equal to 4, indicating that the customer has made an order in each of the last 4 weeks.
The results are then ordered by the total amount spent in descending order and the top 2 rows are returned.
In this blog post, we tackled an advanced SQL challenge of finding the top customers who ordered every week for the last 4 weeks. This solution uses a combination of subqueries, CTEs, and aggregate functions to solve the challenge. It requires knowledge of SQL concepts such as filtering, grouping, and aggregating data, as well as subqueries and CTEs. By breaking down the problem into smaller, more manageable pieces, we were able to apply different SQL techniques to solve the challenge. Keep practising.