1

I have a table about conversations. There are many conversation elements for unique conversation IDs

conv_id element_id author_type part_type
1 11 bot comment
1 12 bot comment
2 22 bot comment
2 23 bot comment
2 24 admin note
3 32 bot note

and I want to write a case when statement for each conversation id, -> if author_type is 'bot' and part_type is 'comment', then label it as 'bot' in a new column for all the rows of that conversation id.

So the result will look like this:

conv_id element_id author_type part_type tag
1 11 bot comment bot
1 12 bot comment bot
2 22 bot comment bot
2 23 bot comment bot
2 24 admin note bot
3 32 bot note

for example, the when conv_id is 2, it wrote 'bot' even if one of the rows didn't meet the criteria.

I tried this code, but it is not working, and the error message is 'over keyword must follow a function call'.

CASE
when
author_type = 'bot' and part_type = 'comment'
then 'bot'
over (partition by conversation_id)
end as tag

Thank you

I edited the post and changed one condition.

allamirope
  • 31
  • 4

1 Answers1

1

Consider below query.

if author_type is 'bot' and part_type is 'comment', then label it as 'bot' in a new column for all the rows of that conversation id.

SELECT *,
       MAX(IF(author_type = 'bot' AND part_type = 'comment', 'bot', NULL)) OVER (PARTITION BY conv_id) AS tag
  FROM sample_table
 ORDER BY conv_id

enter image description here

Jaytiger
  • 11,626
  • 2
  • 5
  • 15
  • Thanks a lot for your help! Counting the distinct number of author_types was a great idea! The real dataset is more complicated, (I have other author_types and part_types, not only 'bot' and 'comment'), but with the right number of distinct author_types, it works! The thing is that, with this code I can get the right tag only for that element_id, is there any way to have it for all the rows of that specific conv_id? Let me know if it wasn't clear. – allamirope Aug 18 '22 at 14:30
  • @alltatech, it's maybe due to my limited english, but not clear to me. would you be more specific on this or it would be nice if you provide some more sample and expected out for your requirements. someone in the community is willing to help solve your problem. – Jaytiger Aug 18 '22 at 14:45
  • I edited the post! so, with your code, I don't get "bot" for the row where element_id is 24. Do you know any way that I can get that tag for all elements of that conversation? In other words, if one element_id has that tag, how can I make the other elements of the same conversation also have it. Or if it is not possible to do it here, is there any other way to write another statement to have the same results for all the rows of a given conv_id? – allamirope Aug 18 '22 at 15:11
  • @allatech, would you check this out ? not sure I'm clearly understand your explanation. – Jaytiger Aug 18 '22 at 15:48