Follow

Follow

# Finding Top Customers Who Ordered Every Week

## SQL Challenges

Rajanand Ilangovan
Â·Apr 21, 2023Â·

### The Problem

You have been given a table `Orders` that contain the following columns:

• `OrderId` (int)

• `CustomerId` (int)

• `OrderDate` (datetime)

• `TotalAmount` (money)

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);
``````

The `Orders` table contains 15 rows of data. The table has four columns: `OrderId`, `CustomerId`, `OrderDate`, and `TotalAmount`. Each row represents an order made by a customer.

### Expected Output

Here's the expected output of the SQL query:

### Explanation

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:

1. Calculate the total amount spent by each customer in the last 30 days. You can do this by filtering the `Orders` table by `OrderDate` to only include orders made in the last 30 days, and then grouping the data by `CustomerId` and summing the `TotalAmount` column.

2. Determine which customers have made orders in each of the last 4 weeks. To do this, you can use a `DATEPART` function 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.

3. 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.

### Solution

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 `TotalAmount` column.

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.