Skip to main content

Query Explanation - 3: Finding Customers Who Made a Purchase Every Month for the Last Six Months

 

In this blog post, we will explore how to write a query that identifies customers who have made at least one purchase in every month for the last six months. This type of query is useful in analyzing customer loyalty, engagement, and purchasing frequency over time.

Problem Statement:

We need to find all customers who have made at least one purchase in each of the last six months. This requires tracking customer orders over a rolling six-month period and ensuring that there is a purchase recorded for each month.

Example Schema:

Assume we have the following table:

  1. Orders:
    • OrderID (Primary Key)
    • CustomerID (Foreign Key, links to customer)
    • OrderDate (DateTime)

SQL Query:

To solve this, we will:

  1. Filter orders from the last six months.
  2. Group the results by CustomerID.
  3. Count the distinct months in which each customer has made a purchase.
  4. Use HAVING to ensure that the customer made a purchase in exactly six distinct months.

Here’s the query:

SELECT 
    CustomerID
FROM 
    Orders
WHERE 
    OrderDate >= DATEADD(MONTH, -6, GETDATE())  -- Only consider orders in the last 6 months
GROUP BY 
    CustomerID
HAVING 
    COUNT(DISTINCT DATEPART(MONTH, OrderDate)) = 6;  -- Ensure exactly 6 distinct months of purchases

Detailed Breakdown:

  1. Filtering the Last 6 Months of Data:

    • The WHERE clause filters the data to only include orders placed in the last six months. This is done using the DATEADD() function, which subtracts 6 months from the current date (GETDATE()).
    • DATEADD(MONTH, -6, GETDATE()) dynamically adjusts to always consider the previous 6 months from the current date.

  2. Grouping by Customer:

    • The GROUP BY clause is used to group the results by CustomerID. This ensures that we will be able to analyze each customer's orders independently.

  3. Counting Distinct Months:

    • DATEPART(MONTH, OrderDate) extracts the month part of the OrderDate. By using COUNT(DISTINCT DATEPART(MONTH, OrderDate)), we count how many distinct months the customer made purchases in.
    • The use of DISTINCT ensures that even if a customer made multiple purchases in a single month, it will only count once per month.

  4. HAVING Clause for Exact Month Count:

    • The HAVING clause ensures that the customer has made purchases in exactly six distinct months. If the count is 6, it means the customer made at least one purchase in every month for the last six months.

Example Data:

Let’s say we have the following data in the Orders table:



In this data:

  • CustomerID 101 made purchases in every month from April to September, so they qualify for the query.
  • CustomerID 102 did not make a purchase in all months (they missed some), so they don’t qualify.
  • CustomerID 103 also made purchases in five months but missed April, so they don't qualify.

Example Output:

After running the query, the result would look like this:


Only CustomerID 101 has made a purchase in every month for the last six months.

Key Takeaways:

  • DATEADD and DATEPART Functions: The DATEADD() function is used to calculate a rolling time period (in this case, 6 months), and the DATEPART() function is used to extract the month from a date for comparison.
  • GROUP BY and HAVING: These clauses work together to ensure that customers are grouped by their CustomerID, and the HAVING clause filters the results to only include those who made a purchase in all six months.
  • Counting Distinct Values: The COUNT(DISTINCT DATEPART(MONTH, OrderDate)) ensures that only distinct months are counted, avoiding multiple purchases within the same month from being double-counted.

By using these SQL techniques, we can accurately retrieve customers who have shown consistent purchasing behavior over a specific period.

Labels:

Comments

Post a Comment

Popular posts from this blog

Implementing and Integrating RabbitMQ in .NET Core Application: Shopping Cart and Order API

RabbitMQ is a robust message broker that enables communication between services in a decoupled, reliable manner. In this guide, we’ll implement RabbitMQ in a .NET Core application to connect two microservices: Shopping Cart API (Producer) and Order API (Consumer). 1. Prerequisites Install RabbitMQ locally or on a server. Default Management UI: http://localhost:15672 Default Credentials: guest/guest Install the RabbitMQ.Client package for .NET: dotnet add package RabbitMQ.Client 2. Architecture Overview Shopping Cart API (Producer): Sends a message when a user places an order. RabbitMQ : Acts as the broker to hold the message. Order API (Consumer): Receives the message and processes the order. 3. RabbitMQ Producer: Shopping Cart API Step 1: Install RabbitMQ.Client Ensure the RabbitMQ client library is installed: dotnet add package RabbitMQ.Client Step 2: Create the Producer Service Add a RabbitMQProducer class to send messages. RabbitMQProducer.cs : using RabbitMQ.Client; usin...
Post a Comment
Read more

Clean Architecture: What It Is and How It Differs from Microservices

In the tech world, buzzwords like   Clean Architecture   and   Microservices   often dominate discussions about building scalable, maintainable applications. But what exactly is Clean Architecture? How does it compare to Microservices? And most importantly, is it more efficient? Let’s break it all down, from understanding the core principles of Clean Architecture to comparing it with Microservices. By the end of this blog, you’ll know when to use each and why Clean Architecture might just be the silent hero your projects need. What is Clean Architecture? Clean Architecture  is a design paradigm introduced by Robert C. Martin (Uncle Bob) in his book  Clean Architecture: A Craftsman’s Guide to Software Structure and Design . It’s an evolution of layered architecture, focusing on organizing code in a way that makes it  flexible ,  testable , and  easy to maintain . Core Principles of Clean Architecture Dependency Inversion : High-level modules s...
Post a Comment
Read more

How Does My .NET Core Application Build Once and Run Everywhere?

One of the most powerful features of .NET Core is its cross-platform nature. Unlike the traditional .NET Framework, which was limited to Windows, .NET Core allows you to build your application once and run it on Windows , Linux , or macOS . This makes it an excellent choice for modern, scalable, and portable applications. In this blog, we’ll explore how .NET Core achieves this, the underlying architecture, and how you can leverage it to make your applications truly cross-platform. Key Features of .NET Core for Cross-Platform Development Platform Independence : .NET Core Runtime is available for multiple platforms (Windows, Linux, macOS). Applications can run seamlessly without platform-specific adjustments. Build Once, Run Anywhere : Compile your code once and deploy it on any OS with minimal effort. Self-Contained Deployment : .NET Core apps can include the runtime in the deployment package, making them independent of the host system's installed runtime. Standardized Libraries ...
Post a Comment
Read more