Unable to write a query which will return the data of date, products and assortment with same date-CodePudding

I have a table like

sales(product_number, assortment, date)

Here 1 assortment contains multiple products. For example 1 assortment i.e. chocolates contains product_number cadbury, 5 star, kitkat etc. Each and every product has date. I'm trying to check that all products in the same assortment have the same date. I'm trying to write a query which will return data where every product_number in every assortment has same date. For example, below is the sample data

product_number   assortment    date
cadbury          chocolate     2021-09-09
cadbury          chocolate     2021-09-09
kitkat           chocolate     2021-09-09
5 star           chocolate     2021-09-09
lays             chips         2022-01-02
chips            chips         2022-02-05 
bingo            chips         2022-01-02
bingo            chips         2022-01-02

In the above table there are 2 assortments, chocolate and chips. chocolate assortment has multiple products which has same date where as chips assortment has different dates. The output must be

product_number   assortment    date
cadbury          chocolate     2021-09-09
cadbury          chocolate     2021-09-09
kitkat           chocolate     2021-09-09
5 star           chocolate     2021-09-09

I wrote a SQL query which is below

SELECT *
FROM sales
WHERE date IN (SELECT date 
                      FROM sales
                      GROUP BY assortment, date
                      HAVING COUNT(DISTINCT product_number) = 1)
ORDER BY assortment, product_number, date

CodePudding user response：

I got the answer. Below is the query i used

SELECT 
  * 
FROM 
  sales 
WHERE 
  assortment IN (
    SELECT 
      assortment 
    FROM 
      sales 
    GROUP BY 
      assortment 
    HAVING 
      COUNT(DISTINCT date) = 1
  )

CodePudding user response：

Assuming you're using Snowflake, another alternative using qualify

select * 
from sales
qualify count(distinct date) over (partition by assortment) = 1;