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

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:

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.

CodePudding user response：

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

CodePudding user response：

Try this query, please:

SELECT
  *,
  CASE WHEN 
  COUNT(1) OVER (PARTITION BY conv_id) = COUNT(
    CASE WHEN author_type = 'bot' AND part_type = 'comment' 
    THEN 1
    END ) OVER (PARTITION BY conv_id) 
  THEN 'bot'
  END AS tag
FROM your_table