One of four declared variables in SQL query not working as intended

with users_having_connected as (
    select u.id as user_id,
           (a.connected_at is not null) has_connected
    from core_user u
    join core_profile p on u.id = p.user_id
    join core_conversation c on (c.profile1_id = p.id or c.profile2_id = p.id)
    join analytics_connection a on c.id = a.conversation_id
    group by u.id, (a.connected_at is not null)
)
select u.id as user_id,
       date_trunc('month', u.created at time zone 'UTC')::date as month,
       p.community_id,
       p.organization_id,
       p.profile_type_intention,
       (p.basic_account_completed and (p.is_mentor or p.is_entrepreneur)) as profile_is_completed,
       exists(select 1 from core_message where core_message.sender_id = p.id) as has_sent_a_message,
       exists(select 1 from core_admin_conversation_w_resp where initiator_id = p.id or responder_id = p.id) as has_received_a_response_to_first_message,
       exists(select 1 from users_having_connected where user_id = u.id) as has_connected
from core_user as u
join core_profile p on u.id = p.user_id
where
p.profile_type_intention is not null

In the above query, the variable has_received_a_response_to_first_message will display when all columns and fields are displayed, as in this SQL query. When I try to open this query in the editor, all the other variables are able to be modified, but has_received_a_response_to_first_message times out. Any thoughts as to why this could be happening or how I might fix this query?

Hi @Sarah_McRae
There's no Metabase variables in that query, so not sure what you are referring to?
https://www.metabase.com/docs/latest/questions/native-editor/sql-parameters

I use this query as the starting point by clicking "Explore results". From there, I join this query with "Core User" and "Core Profile" so I can see fields like "Is Mentor" in the Metabase user interface, etc. Does that clear up your question?

SELECT "source"."has_received_a_response_to_first_message" AS "has_received_a_response_to_first_message", count(*) AS "count"
FROM (with users_having_connected as (
    select u.id as user_id,
           (a.connected_at is not null) has_connected
    from core_user u
    join core_profile p on u.id = p.user_id
    join core_conversation c on (c.profile1_id = p.id or c.profile2_id = p.id)
    join analytics_connection a on c.id = a.conversation_id
    group by u.id, (a.connected_at is not null)
)
select u.id as user_id,
       date_trunc('month', u.created at time zone 'UTC')::date as month,
       p.community_id,
       p.organization_id,
       p.profile_type_intention,
       (p.basic_account_completed and (p.is_mentor or p.is_entrepreneur)) as profile_is_completed,
       exists(select 1 from core_message where core_message.sender_id = p.id) as has_sent_a_message,
       exists(select 1 from core_admin_conversation_w_resp where initiator_id = p.id or responder_id = p.id) as has_received_a_response_to_first_message,
       exists(select 1 from users_having_connected where user_id = u.id) as has_connected
from core_user as u
join core_profile p on u.id = p.user_id
where
p.profile_type_intention is not null) "source"
GROUP BY "source"."has_received_a_response_to_first_message"
ORDER BY "source"."has_received_a_response_to_first_message" ASC

This is the specific markdown that is erroring.

@Sarah_McRae Okay, so it has nothing to do with variables. You are just using a SQL question as the base when joining other questions in the GUI Notebook editor.

I don't think I understand what the problem is. If you are seeing timeouts with certain queries, then check your database query log to see if it is hinting at missing indexes or other optimizations.

@flamber I tried asking our dev team about the database query log and they weren't sure what I was talking about. As far as I can tell, the only line in this query that doesn't work right is this one:

exists(select 1 from core_admin_conversation_w_resp where initiator_id = p.id or responder_id = p.id) as has_received_a_response_to_first_message

and I am not sure why it is not working.

@Sarah_McRae Your database administrator can enable query logging on their database to see slow queries, and run EXPLAIN ANALYZE to understand where the problem comes from, which is likely because of missing indexes or an infinite lookup.
It is basically impossible for anyone else to know what the problem is, since it requires access to your data.

@flamber I found the error in the SQL query: that particular variable was missing another set of parentheses.