Rolling 12 Month Data - SQL

Understanding the Problem

The problem at hand is to retrieve data from a database table that contains customer information and order history. The goal is to calculate the number of customers who have placed an order in a specific month and the total number of orders they have placed in that month, as well as the 11 months prior to that.

Background Information

To approach this problem, we need to understand some basic concepts related to SQL and data aggregation. In particular, we’ll be using window functions, which allow us to perform calculations across a set of rows that are related by some condition.

Window functions in SQL are used to manipulate data by applying a function to a set of rows that are related by some condition. There are several types of window functions, including:

• ROW_NUMBER: assigns a unique number to each row within a partition
• RANK: assigns a rank to each row within a partition based on the value of the function
• DENSE_RANK: assigns a dense rank to each row within a partition
• NTILE: divides the result into a specified number of groups

In this case, we’ll be using window functions like ROW_NUMBER and SUM to calculate the total orders for each customer in a specific month.

Assumptions

For the purpose of this example, let’s assume that:

• The table name is orders
• The column names are CustomerID, OrderID, and OrderDate
• The data type for OrderDate is DATE

We also assume that the database management system used is MySQL or PostgreSQL, as these are two popular systems that support window functions.

Data Structures

Let’s create a sample table to illustrate this problem:

CREATE TABLE orders (
  CustomerID INT,
  OrderID INT,
  OrderDate DATE
);

We’ll insert some sample data into the table:

INSERT INTO orders (CustomerID, OrderID, OrderDate)
VALUES
  (1234, 5678, '2016-12-01'),
  (1234, 5679, '2017-01-01'),
  (1235, 5680, '2017-01-15');

Solution

The solution to this problem involves using window functions like ROW_NUMBER and SUM to calculate the total orders for each customer in a specific month.

Here’s an example query that demonstrates how to achieve this:

SELECT 
  CustomerID,
  OrderDate,
  COUNT(*) AS CustomerCount,
  SUM(CASE WHEN ORDERDATE >= DATE_SUB(CURRENT_DATE, INTERVAL 12 MONTH) THEN 1 ELSE 0 END) AS TwelveMonthOrderCount,
  SUM(CASE WHEN ORDERDATE >= DATE_SUB(CURRENT_DATE, INTERVAL 11 MONTH) AND ORDERDATE < DATE_SUB(CURRENT_DATE, INTERVAL 12 MONTH) THEN 1 ELSE 0 END) AS ElevenMonthOrderCount
FROM 
  orders
GROUP BY 
  CustomerID, OrderDate;

This query calculates the total number of customers (CustomerCount), the number of orders placed by each customer in the current month (TwelveMonthOrderCount), and the number of orders placed by each customer in the previous month (ElevenMonthOrderCount).

How it Works

Here’s a step-by-step explanation of how this query works:

1. GROUP BY: The GROUP BY clause groups the rows into sets based on the values in the CustomerID and OrderDate columns.
2. COUNT*: The COUNT(*) function returns the total number of orders for each group, which corresponds to the number of customers who have placed an order in a specific month.
3. CASE Statements: The query uses two CASE statements to calculate the number of orders placed by each customer in the current and previous months.

• The first CASE statement checks if the order date is within the last 12 months from the current date. If it is, then it counts as an order placed in the current month.
• The second CASE statement checks if the order date is within the last 11 months but not in the last 12 months. If it is, then it counts as an order placed in the previous month.

4. SUM: Finally, the query uses the SUM function to calculate the total number of orders for each customer in the current and previous months.

Result

The result set will contain the following columns:

• CustomerID
• OrderDate
• CustomerCount: The total number of customers who have placed an order in a specific month.
• TwelveMonthOrderCount: The total number of orders placed by each customer in the current month.
• ElevenMonthOrderCount: The total number of orders placed by each customer in the previous month.

For example, for the sample data provided earlier:

This result shows that there is one customer who has placed an order in the current month, two customers who have placed orders in the previous month, and no customers who have placed orders in either of the months 12 or 11 months ago.

Last modified on 2024-07-04