# PAID Data explorer challenge: chronological list of user topics started, replied and liked

**URL:** <https://meta.discourse.org/t/paid-data-explorer-challenge-chronological-list-of-user-topics-started-replied-and-liked/44407>\
**Category:** Marketplace\
**Tags:** sql-query\
**Created:** [2016年五月17日 20:39 UTC](https://meta.discourse.org/t/paid-data-explorer-challenge-chronological-list-of-user-topics-started-replied-and-liked/44407 "2016-05-17T20:39:40Z")\
**Posts on this page:** 1\
**Showing post:** 6

<div class="post-metadata">

**Author:** ![LeoMcA](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/leomca/32/87233_2.png) [@LeoMcA](https://meta.discourse.org/u/LeoMcA)\
**Post date:** [2016年八月4日 12:06 UTC](https://meta.discourse.org/t/paid-data-explorer-challenge-chronological-list-of-user-topics-started-replied-and-liked/44407/6 "2016-08-04T12:06:40Z")

</div>

> [@tobiaseigen](#):
>
> Another thought: it occurs to me that if it were possible to generate a list of all posts, likes and messages in a given timeframe I would have the data I need and would have lots more useful data besides. Has anyone done such a query? E.g. date, new topic/reply/like, username, topic title, topic URL

With my first exciting foray into the world of slightly complicated SQL queries, I present the query for this query! 🎉

### Parameters

- `date_from`
  - date to start the query from

- `date_to`
  - date to query up to (but not including)

- `guess_domain`
  - if true (the default) guesses the domain name of the Discourse instance for use in the `url` column
  - if false uses the value of the `domain` parameter

- `domain` **(optional)**
  - domain and protocol of the Discourse instance

### Columns

- `created_at`
  - date the action took place

- `action`
  - type of action (currently: _New Topic_, _Topic Reply_, _Message_, _Like_)

- `username`
  - username of user performing the action

- `topic_title`
  - title of topic or message created or in which the action was performed

- `category`
  - category in which the action was performed (NULL if a message)

- `parent_category`
  - parent category in which the action was performed (NULL if a message, or performed in a top level category)

- `url`
  - url to topic, post or message which was created or liked

### Query

```plaintext
-- [params]
-- date :date_from = 1970-01-01
-- date :date_to = 2038-01-19
-- boolean :guess_domain = true
-- string :domain = https://example.com

WITH ss AS (
  SELECT CASE
    WHEN :guess_domain = true THEN concat('https://', split_part(value, '@', 2))
    ELSE :domain
  END AS domain
  FROM site_settings
  WHERE name = 'notification_email'
)
SELECT
  ua.created_at,
  CASE
    WHEN ua.action_type = 1 THEN 'Like'
    WHEN ua.action_type = 4 THEN 'New Topic'
    WHEN ua.action_type = 5 THEN 'Topic Reply'
    WHEN ua.action_type = 12 THEN 'Message'
  END AS action,
  u.username,
  t.title AS topic_title,
  c.name AS category,
  pc.name AS parent_category,
  concat((SELECT domain from ss), '/t/', t.id, '/', (CASE WHEN p.post_number IS NOT NULL then p.post_number ELSE 1 END)) AS url
FROM user_actions AS ua
JOIN users AS u ON ua.user_id = u.id
JOIN topics AS t ON ua.target_topic_id = t.id
LEFT JOIN posts AS p ON ua.target_post_id = p.id
LEFT JOIN categories AS c ON t.category_id = c.id
LEFT JOIN categories AS pc ON c.parent_category_id = pc.id
WHERE
  ua.user_id != -1
  AND
  ua.action_type IN (1, 4, 5, 12)
  AND
  ua.created_at BETWEEN :date_from AND :date_to
ORDER BY created_at DESC

```

---

_[View the full topic](https://meta.discourse.org/t/paid-data-explorer-challenge-chronological-list-of-user-topics-started-replied-and-liked/44407)._