# Comprendre ce que nos utilisateurs publient

**URL:** <https://meta.discourse.org/t/understanding-what-our-users-are-posting-about/312382>\
**Category:** Data & reporting\
**Tags:** sql-query\
**Created:** [Juin 17, 2024, 1:35 UTC](https://meta.discourse.org/t/understanding-what-our-users-are-posting-about/312382 "2024-06-17T13:35:11Z")\
**Posts on this page:** 1\
**Showing post:** 10

<div class="post-metadata">

**Author:** ![JammyDodger](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/jammydodger/32/254611_2.png) [@JammyDodger](https://meta.discourse.org/u/JammyDodger)\
**Post date:** [Juin 20, 2024, 11:20 UTC](https://meta.discourse.org/t/understanding-what-our-users-are-posting-about/312382/10 "2024-06-20T11:20:32Z")

</div>

Ce n’est pas un problème. 🙂 Lorsque visualisés dans l’explorateur de données, `category_id` et `topic_id` (et bien d’autres) sont automatiquement convertis en liens internes utilisables, mais si vous exportez pour analyser ailleurs, vous pouvez utiliser les noms de catégories et les titres de sujets à la place :

```sql
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-12-31

WITH tag_names AS (

    SELECT
        t.id AS topic_id,
        string_agg(tags.name, ', ' ORDER BY tags.name) AS "tags"
    FROM topics t
      JOIN topic_tags tt ON tt.topic_id = t.id
      JOIN tags ON tags.id = tt.tag_id
    WHERE t.created_at BETWEEN :start_date AND :end_date
    GROUP BY t.id
    ),

user_count AS (

    SELECT
        p.topic_id,
        COUNT(DISTINCT p.user_id) AS users
    FROM posts p
      JOIN topics t ON t.id = p.topic_id
    WHERE t.created_at BETWEEN :start_date AND :end_date
      AND t.deleted_at IS NULL
      AND p.deleted_at IS NULL
      AND t.archetype = 'regular'
      AND p.post_type = 1
      AND p.user_id > 0
    GROUP BY p.topic_id

)

SELECT
    c.name AS category_name,
    t.title,
    tn.tags,
    t.posts_count,
    uc.users
FROM topics t
  JOIN tag_names tn ON tn.topic_id = t.id
  JOIN user_count uc ON uc.topic_id = t.id
  JOIN categories c ON c.id = t.category_id
WHERE t.created_at BETWEEN :start_date AND :end_date
  AND t.archetype = 'regular'
  AND t.deleted_at IS NULL
ORDER BY t.category_id, t.title

```

---

_[View the full topic](https://meta.discourse.org/t/understanding-what-our-users-are-posting-about/312382)._