псіхобот

select
  count(x.count_all) as "Стартували Бот",
  sum(x.count_questionnaire) as "Вибрали тест емоційного стану",
  sum(x.count_questionnaire_talk) as "Відразу поговорити про ситуацію",
  sum(x.count_questionnaire_final) as "Пройшли тест емоційного стану",
  sum(x.count_category) as "Вибрали певну категорію",
  sum(x.count_stuff) as "Дійшли до корисних матеріалів",
  sum(x.count_choosed_conult) as "Вибрали психологічну консультацію",
  sum(x.count_complete_consult) as "Пройшли консультацію"
from (
  select
    count(cconv.*) as count_all,
    count(cconv.*) filter (where cmess.text ilike '%оцінити твій емоційний стан за допомогою маленького опитувальника%') as count_questionnaire,
    count(cconv.*) filter (where cmess.text = 'поговорити') as count_questionnaire_talk,
    count(cconv.*) filter (where cmess.text ilike '%Дякую за відповіді!%') as count_questionnaire_final,
    count(cconv.*) filter (where cmess.text ilike '%Скажи, будь ласка, що тебе турбує?%') as count_category,
    count(cconv.*) filter (where cmess.text = 'матеріали') as count_stuff,
    0 as count_choosed_conult,
    0 as count_complete_consult
  from chat.conversation cconv
    left join chat.message cmess on cmess.conversation_id = cconv.id
  where cconv.domain_id = $domain_id
    and cconv.created_at between $__timeFrom()::timestamptz and $__timeTo()::timestamptz
  group by cconv.id
) x