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

Marketplace
6

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

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

-- [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
7 Likes