Data Explorer 查询 N 天以上未解决的主题

数据与报告
1

我正在尝试创建一个数据浏览器查询，该查询返回一个主题列表，其中：

  • 主题未解决
  • 主题比当前运行日期年长 7 天以上
1 个赞
2

我终于解决了！给其他需要的人。此查询用于查找 7 天到 40 天之间未解决的主题。

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 "主题发布于:",
       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
6 个赞
3

很高兴你解决了这个问题。 :slight_smile:

这对于只有两个数字可能没什么用，但万一你不知道，你可以在数据探索器查询中添加动态参数，这样就可以通过输入而不是直接编辑查询来轻松选择范围：https://meta.discourse.org/t/discourse-data-explorer/32566#declaring-parameters-in-your-query-8

1 个赞
4

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