How to find Customers who only Purchased a specific Product using SQL

How to Identify the Single-Category Loyal Customers Using SQL

Scenario:

In today's data-driven business environment, understanding customer purchasing behavior is critical for driving revenue growth and improving customer engagement. 

One common analytical requirement is identifying customers who purchase exclusively from a single product category and have never explored other product offerings. 

These customers often represent highly loyal segments with distinct buying patterns, making them valuable candidates for targeted marketing and growth initiatives.

Consider a retailer offering multiple product categories such as Radios, Televisions, Routers, and Laptops. The business wants to identify customers who have purchased only Radio products throughout their transaction history and determine the revenue generated by this customer segment. 

By leveraging a simple yet highly optimized SQL aggregation technique, organizations can efficiently analyze large transaction datasets and uncover these loyal customers.

In this article, we will explore an SQL solution that identifies customers who have purchased only Radio products, calculates their revenue contribution, and demonstrates how a simple query can deliver meaningful business insights from transactional data.

-- Create Required Tables

CREATE TABLE dbo.dim_Customers

(

    Customer_ID     INT,

    Customer_Name   VARCHAR(50)

);

CREATE TABLE dbo.dim_Products

(

    Product_ID      INT,

    Product_Name    VARCHAR(50)

);

CREATE TABLE dbo.fact_Transactions

(

    Transact_ID  INT,

    Transact_Date   DATE,

    Customer_ID     INT,

    Product_ID      INT,

    Sales_Amount    DECIMAL(10,2)

);

-- Insert Data into Tables

INSERT INTO dbo.dim_Customers

(

    Customer_ID,

    Customer_Name

)

VALUES

(1,'Sun'),

(2,'Moon'),

(3,'Earth'),

(4,'Water'),

(5,'Sky');

SELECT * FROM dbo.dim_Customers ;

INSERT INTO dbo.dim_Products

(

    Product_ID,

    Product_Name

)

VALUES

(101,'Radio'),

(102,'Television'),

(103,'Router'),

(104,'Laptop');

SELECT * FROM dbo.dim_Products;

-- TRUNCATE TABLE dbo.fact_Transactions

INSERT INTO dbo.fact_Transactions

(

    Transact_ID,

    Transact_Date,

    Customer_ID,

    Product_ID,

    Sales_Amount

)

VALUES

-- Sun (Only Radio)

(1001,'2026-08-20',1,101,10000),

(1002,'2026-10-15',1,101,12000),

-- Moon (Radio + Television)

(1003,'2026-07-20',2,101,15000),

(1004,'2026-10-05',2,102,30000),

-- Earth (Only Radio)

(1005,'2026-02-10',3,101,8000),

-- Water (Only Laptop)

(1006,'2026-03-25',4,104,70000),

-- Sky (Radio + Router)

(1007,'2026-08-30',5,101,9000),

(1008,'2026-11-01',5,103,5000);

SELECT * FROM dbo.fact_Transactions

---------------------------------------------------------

Now we can use the below SQL Query to Identify the customers who have purchased only the Product is Radio and not any other Products:

SELECT

    c.Customer_ID,

    c.Customer_Name,

    MAX(p.Product_Name) AS Product_Name,

    SUM(f.Sales_Amount) AS Revenue

FROM dbo.fact_Transactions f

INNER JOIN dbo.dim_Customers c

    ON c.Customer_ID = f.Customer_ID

INNER JOIN dbo.dim_Products p

    ON p.Product_ID = f.Product_ID

GROUP BY

    c.Customer_ID,

    c.Customer_Name

HAVING

    MIN(f.Product_ID) = 101 -- 'Radio'

    AND MAX(f.Product_ID) = 101; 

#Result:

We can use the below query to Identify the customers who have purchased only the Product is Radio and not any other Products in last 90 days:

SELECT

    c.Customer_ID,

    c.Customer_Name,

    MAX(p.Product_Name) AS Product_Name,

    SUM(f.Sales_Amount) AS Revenue

FROM dbo.fact_Transactions f

INNER JOIN dbo.dim_Customers c

    ON c.Customer_ID = f.Customer_ID

INNER JOIN dbo.dim_Products p

    ON p.Product_ID = f.Product_ID

WHERE

    -- Option 1: Last 90 Days

    f.Transact_Date >= DATEADD(DAY, -90, GETDATE())

    -- Option 2: Last 3 Calendar Months

    -- f.Transact_Date >= DATEADD(MONTH, -3, GETDATE())

GROUP BY

    c.Customer_ID,

    c.Customer_Name

HAVING

    MIN(f.Product_ID) = 101 -- 'Radio'

    AND MAX(f.Product_ID) = 101; 

#Result:

Business Value Creation:

• Identify Product Loyalty: Discover customers who consistently purchase only one product category, providing valuable insights into customer preferences and product affinity.
• Increase Cross-Sell Opportunities: Target loyal customers with complementary products and services to encourage broader product adoption and increase revenue.
• Improve Marketing Effectiveness: Develop focused campaigns based on customer purchasing behavior rather than relying on generic audience targeting.
• Measure Revenue Contribution: Quantify the revenue generated by loyal customer segments and evaluate their business impact.
• Enhance Customer Segmentation: Build more accurate customer profiles and support personalized engagement strategies.
• Support Data-Driven Decision Making: Enable sales, marketing, and product teams to make informed decisions using actionable insights derived from transactional data.
• Increase Customer Lifetime Value (CLV): Identify opportunities to deepen customer relationships and maximize long-term business value through targeted recommendations and retention strategies.

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------