Query Explanation - 5: Listing Products Sold in All Cities Where the Company Operates

 

In this blog post, we’ll go over how to write a query to find products that have been sold in every city where the company operates. This can be valuable for companies to identify popular or well-distributed products across all operational regions.

Problem Statement:

We want to find all products that have been sold in every city listed in the sales data. This requires identifying products that are available across all locations without any gaps.

Example Schema:

Assume we have a Sales table with the following columns:

1. Sales:
   • SaleID (Primary Key)
   • ProductID (Foreign Key, links to product)
   • City (Name of the city where the sale was made)

SQL Query:

To identify these products, we need to:

1. Group sales data by ProductID.
2. Count the unique cities (DISTINCT City) each product has been sold in.
3. Use HAVING to filter results where this count matches the total number of unique cities in the table.

Here’s the query:

SELECT 
    ProductID
FROM 
    Sales
GROUP BY 
    ProductID
HAVING 
    COUNT(DISTINCT City) = (SELECT COUNT(DISTINCT City) FROM Sales);

Detailed Breakdown:

1. Counting Unique Cities:

   • We use COUNT(DISTINCT City) in the main query to get the number of unique cities where each ProductID has sales.
   • In the subquery (SELECT COUNT(DISTINCT City) FROM Sales), we calculate the total count of unique cities represented in the Sales table. This total city count serves as a reference point, as we need products that have sales in exactly this number of cities.

2. Filtering Products Sold in All Cities:

   • The HAVING clause ensures that only products sold in all cities are included in the final results.
   • COUNT(DISTINCT City) = (SELECT COUNT(DISTINCT City) FROM Sales) checks if a product’s unique city count matches the total number of unique cities. If they match, it means that the product has been sold in every city where the company operates.

3. Grouping by ProductID:

   • The GROUP BY ProductID groups sales by each product, allowing us to calculate the unique city count for each product individually.

Example Data:

Let's assume the Sales table has the following data:

Assuming the company operates in three cities (New York, Los Angeles, and Chicago), this query will return products sold in all three cities.

Example Output:

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

Here’s the reasoning:

• Product A has sales records in New York, Los Angeles, and Chicago, covering all cities.
• Product C is also sold in all three cities.
• Product B is only sold in New York and Chicago, so it doesn’t meet the criteria.

Key Concepts:

• Using a Subquery to Count Distinct Cities: The subquery (SELECT COUNT(DISTINCT City) FROM Sales) helps calculate the total number of unique cities. By comparing each product’s city count to this total, we can filter for products that meet the requirement.
• GROUP BY and HAVING: The GROUP BY clause groups sales by ProductID, while the HAVING clause applies the filtering condition after aggregation.
• COUNT(DISTINCT): Using COUNT(DISTINCT City) ensures that duplicate city entries for a single product are not double-counted, which is important for getting accurate results.

Benefits of Identifying Widely Distributed Products:

1. Market Demand Analysis: This query helps identify products with widespread appeal or demand across all regions.
2. Targeted Marketing: Knowing which products are sold in all regions can guide targeted marketing and promotional efforts.
3. Operational Insights: Such data can inform inventory planning, distribution, and stocking strategies.