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?
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
@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
@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.