Data Explorer query for topics N+ days old, that are unsolved

data & reporting
1

I am trying to create a data explorer query that returns a list of topics, where:

  • The topic is unsolved
  • The topic is 7+ days old from the current date ran
1 Like
2

I eventually got unstuck! For anyone else looking for this. This query is for unsolved topics between 7 and 40 days old.

WITH solved_topics AS (

SELECT ua.target_topic_id AS topic_id,
       ua.user_id,
       ua.target_post_id AS post_id,
       p.created_at
FROM user_actions ua
JOIN posts p on p.id = ua.target_post_id
WHERE action_type = 15

)

SELECT t.id as topic_id,
       t.user_id AS question_user_id,
       t.created_at::date AS "Topic Posted On:",
       t.views
FROM topics t
LEFT JOIN solved_topics st ON t.id = st.topic_id
WHERE t.category_id = 37
AND t.created_at BETWEEN current_date - 40 AND current_date -7
AND t.deleted_at ISNULL
AND t.visible = TRUE
AND st.topic_id IS NULL
ORDER BY t.created_at
7 Likes
3

I’m glad you figured it out. :slight_smile:

It might not be very useful for just two numbers, but just in case you wouldn’t know that, you can add dynamic parameters to data explorer queries, which would allow you to easily select the range with inputs instead of directly editing the query: https://meta.discourse.org/t/discourse-data-explorer/32566#declaring-parameters-in-your-query-8

1 Like
4

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.