'SQL query to understand if customers are purchasing matching products
I am trying to figure out the most efficient way to structure a query where the goal is to determine whether or not customers are purchasing "matching" products in an order. Here is an example of data structure.
| order id | product id | product type | product colour |
|---|---|---|---|
| a | a1 | shirt | blue |
| a | a2 | shorts | blue |
| a | a3 | shirt | red |
| b | a4 | shirt | green |
| b | a5 | shorts | yellow |
| c | a6 | shirt | pink |
| c | a7 | shirt | pink |
So, in the example I would want to know if people who are purchasing both a shirt and shorts are matching the colour or those 2 products. Im sure this is a common cart analysis.
My initial plan is basically:
- determine the orders that purchased both a shirt and shorts
- create a temp table that stores the order id and the shirt colour
- do the same for shorts join these two tables on order id and compare the two colour columns
so essentially I would have:
| order id | shirt colour | shorts colour | match? |
|---|---|---|---|
| a | blue | blue | TRUE |
| b | green | yellow | FALSE |
BUT, this doesnt seem like a very efficient way to do it and also doesnt account for situations where customers purchase multiple products like in the example order 'a'.
Any suggestions to improve this?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
