I need some help cleaning my data. I have a table like below:

When session_id has both null and valid find_term, I want to keep the records that only have valid find_terms (eg: for session_id 11111, I want to exclude the first record when find_term is null and keep when find_term is candy and chocolate. For session_id 22811, 11145, I would want to keep all existed records because they only have null as find_term). I want the output to be:

So far I have tried giving same group of records row_num to distinct them, but that is not too helpful. Please share your thoughts! Thanks!

CodePudding user response：

Use below

select * from your_table
qualify ((find_term is null ) and countif(not find_term is null) over(partition by session_id) = 0)
or not find_term is null             

if applied to sample data in your question - output is