Identify products that were sold in all regions.

Query:

SELECT product_id 
FROM sales 
GROUP BY product_id 
HAVING COUNT(DISTINCT region_id) = (SELECT COUNT(*) FROM regions); 

Explanation:

We need to find products that have been sold in all regions. This involves comparing the distinct regions a product appears in with the total number of regions available. Here’s how the query works step-by-step:

1. Subquery: (SELECT COUNT(*) FROM regions)
   • This subquery calculates the total number of regions in the regions table.
   • For example, if there are 5 regions, this subquery will return 5.
2. Main Query:SELECT product_id FROM sales GROUP BY product_id
   • The sales table is grouped by product_id, so each group represents a single product and its associated sales records.
3. COUNT(DISTINCT region_id):
   • For each product_id, we count the distinct region_id values to determine how many unique regions that product has been sold in.
4. HAVING Clause: HAVING COUNT(DISTINCT region_id) = (SELECT COUNT(*) FROM regions)
   • The HAVING clause filters the results to include only those products for which the number of distinct regions matches the total number of regions from the subquery.
   • This ensures we only select products sold in all regions.

Key Points:

• The subquery provides the total count of regions.
• The main query groups the data by product and counts the distinct regions for each product.
• The HAVING clause ensures that only products sold in every region (i.e., the count of distinct regions equals the total number of regions) are included in the result.

Example:

Sales Table:

Regions Table:

Execution:

• Subquery: SELECT COUNT(*) FROM regions → Returns 3.
• Main Query Groups:
  • Product 1 → Distinct regions: 2.
  • Product 2 → Distinct regions: 3.
  • Product 3 → Distinct regions: 1.
• Only Product 2 is sold in all 3 regions, so the output is:

Result: Product 2 is sold in all regions.