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.