По-моему приведенный запрос не будет работать, т.к. нет агрегатных функций возле группируемых параметров. Если не анализировать структуру БД и условия запроса, а только преобразовать его, то будет как-то так:
WITH check_user as (
SELECT :user_id IN (18,104,1,15) check_id FROM dual
),
main_query as (
SELECT
kpi.id,
GROUP_CONCAT(users_to_groups.user_id) AS test_1,
SUM(:user_id=users_to_groups.user_id) AS test_3
FROM kpi
LEFT JOIN kpi_watchers_groups ON kpi_watchers_groups.kpi_id = kpi.id
LEFT JOIN users_to_groups ON users_to_groups.data_id = kpi_watchers_groups.group_id
GROUP BY kpi.id
)
SELECT
main_query.*,
check_user.check_id test_2
FROM main_query
CROSS JOIN check_user