Checking integer lists under a column for each record to tag/classify each record (SQL) - reverse index match (or some other way)?

Safak Atakan Celik 0
Viewed 30

I have a problem I'm trying to resolve. I'd love to get your ideas or help with the method I have in my mind. I created a good analogy for it: I need to tag each record in a table if they're partial purchases or not. It is clearer if you can look at the example below:

I have products classified as Level 2 and Level 1. And each Level 1 product is associated with one or more level 2 products. I only need to check Level 1 purchases, because Level 2 ones can't be partial purchase.

Partial purchase: If a customer who purchased Level 1 product didn't purchase any of the associated Level 2 products, that purchase is partial.

It should be simple, but the columns I have hold the necessary information in a weird way.

Method I have in mind:

For each level 1 purchase

  1. Get all the Level 2 purchases from that customer with a cte
  2. Check the product dependency columns of those: if this level 1 product's product ID is in any of those lists(!) it's not a partial purchase. If not it's partial.

This should work, but I can't get it to work in SQL. My question:

  • How can I check the each element in each list in the entire column for all the Level 2 purchases from that customer? Because I don't have any way of associating these level 1 & level 2 purchases based on their product dependencies.

example image showing the columns etc. - first three records here should explain enough about the problem & the expected result

Any help is appreciated, I'm a bit slow on SQL What we have is PostgreSQL through Redash

redash sql postgresql
2 Answers

You can do it using unnest() function of postgresql. This function are converted array list to row for easily using. I don't know type of your product_dependency field. If this field is not a int array so you can convert this field to array using string_to_array() function and after then use unnest(). I wrote an example for you:

select * from your_table t1 
where 
    t1.product_level = 'Level 1' and 
    t1.product_id not in (select unnest(product_dep) from your_table where customer_id = t1.customer_id)

And result (gets only partial purchases):

id  customer_id product_id  product_level
3   B           1           Level 1
6   D           5           Level 1
Ramin Faracov0

I will write an alternative query for you: Example:

select 
    t1.id, t1.customer_id, t1.product_id, t1.product_level,  
    case when product_level = 'Level 1' then (t1.product_id not in (select unnest(product_dep) from your_table where customer_id = t1.customer_id)) else false end as partial_purchase
from your_table t1

Result: 
id  customer_id product_id  product_level   partial_purchase
1   A           1           Level 1         false
2   A           2           Level 2         false
3   B           1           Level 1         true
4   C           3           Level 2         false
5   C           4           Level 1         false
6   D           5           Level 1         true
7   D           6           Level 2         false

You can use this query in your subquery for easily filtering like as:

where partial_purchase = true 
or 
where partial_purchase = false  
or 
where product_id = <id>
Ramin Faracov0
Related