Hard to find a title for that question:
We have a database that is structured like this:
Timestamp - ProductID - Status Flag (a, b)- .... (other fields)
We have AWS Quicksight, but the question is generic enough I think ...
We would now like to get information on (per given timeframe; e.g. weekly)
* How many (unique/distinct) product IDs are there in the DB?
* How many (unique) productIDs with Status Flag "a"?
* How many (distinct) productIDs with Status Flag "b"?
* Now the challenge: How many productIDs with BOTH a and b?
Theoretically, the answer to the last question would be: No. of ProductIDs with Status Flag a + No. of ProductIDs with Status Flag b - No. of ProductIDs.
But is this really the best way? Or is there something better that wouldn't break if there comes a flag "c" ?
You may think of Status Flags as "is in Warehouse A"; "is in Warehouse B". We want to see items that went through both warehouses, as well as those that only went to "A" or "B".