Skip to content

Latest commit

 

History

History
157 lines (137 loc) · 14.7 KB

querys.md

File metadata and controls

157 lines (137 loc) · 14.7 KB
Error in user YAML: (<unknown>): mapping values are not allowed in this context at line 4 column 10
---
This plugin allows admins to run SQL queries against the live Discourse database, 
including parameterized queries and formatting for several common column types.

More info: https://meta.discourse.org/t/data-explorer-plugin/32566
---

List of Queries

  • Fetch top 10 posts by likes received in the last month - SQL
  • Users Last Seen - SQL
    • Since N Weeks Ago
    • Since N Days Ago
  • Banner Stats - SQL
  • Top Quality Users - SQL
  • Posts Read (Daily) - SQL
  • Posts Read Percentiles - SQL
  • Active Readers - SQL
    • Active Readers (Past Month)
    • Active Readers (Since N Days Ago)
  • Topic Participation - SQL
  • Count new TL1, TL2, TL3 users past 12 months - SQL
  • Likes from the team - SQL
  • Recently Read Topics by User - SQL
  • Member Uploads - SQL
  • Users active in last x days - SQL
  • Who has been sending more personal messages in the last week (PM)? - SQL
    • Who has been sending more personal messages (PM)? With params - SQL
  • User’s directory - SQL
  • User's stats - SQL
  • Who is making SOLVED - SQL
  • Solved stats per user - SQL
  • Most Common Likers - SQL
  • Export a list of people who replied to a specific topic - SQL
  • Pinned Topics - SQL
  • Get a list of topics in a category read by a specific user - SQL
  • User participation - SQL
  • Participation Histograms (Output from these used to make graphs shown in this post):
    • Posts Read User Counts - SQL
    • Posts Created User Counts - SQL
  • Top 50 Posters - SQL
  • Top 50 Likers - SQL
  • List of Score's Threads by interval - SQL
  • Tracking Referrals - SQL
  • List for invisible topics - SQL
  • Tracking User Edits - SQL
  • List of new users for copy-paste - SQL
  • List of cleared pinned topics - SQL
  • List of users who have “liked” the most flagged posts, where the flags have been “agreed” by a moderator - SQL
  • Lists all new topics created with a given month, ordered by category and creation_date. The query accepts a "months_ago" parameter. It defaults to 0 to give you the stats for the current month. SQL
  • List of the top 50 active topics per month. It’s based on the number of replies created for a topic in a given month. The query accepts a ‘months_ago’ parameter, defaults to 0 to give results for the current month. SQL
  • Count of topics solved by regular members (defaults to last month) SQL
  • Count of topics solved by staff members (defaults to last month) - SQL
  • Lists topics solved by regular users over a given monthly period, ordered by solution_date. - SQL
  • Time to respond by staff (monthly): Average time to first staff response for topics created by regular users in a given time period for a hard-coded array of categories. The categories array can be changed by altering this line: AND t.category_id = ANY ('{46,25,43,40,44,35,22,7,20,17,6,12}'::int[]). It’s possible to alter the query so that the category array can be supplied as a string parameter, written in the form {1, 2, 3}. - SQL
  • Time to Response - - SQL
  • Who is following the tags - - SQL - Notification Levels: muted: 0, regular: 1, tracking: 2, watching: 3, watching_first_post: 4 (source)
  • Checking user profiles URL field - SQL
  • Checking default avatar's user - SQL
  • Last 7 days of topics (created) - SQL
  • How to check the uniqueness of users in “hot link” badge - SQL
  • How do I see Facebook signups? - SQL
  • Search a "Term" into PM (personal messages) - SQL
  • List of users that excludes the members of a group - SQL
  • Categories solved by members or staff - SQL
  • Give me all the users from group VIP where badge Certified does not exist - SQL
  • List of emails from users who open the topics (by id) - SQL
  • Latest uploads with URLs - SQL
  • Top 10 referrers over the last month - SQL
  • Displaying user custom fields in data explorer query results - SQL
  • How to query a confirmation-type custom user field? - SQL
  • Established users with trust-level locked to 0, 1 or 2 - SQL
  • Return a list of usernames and the number of topics each of those usernames has created that is now tagged - SQL
  • The number of posts by a user on topics where the user is not the OP - SQL
  • SQL to display a list of users (top 10 maybe), ordered by the total number of Badges they have? - SQL v1
    • SQL v2
    • How can I filter a specific badge? I need to count how many times they achieved it - SQL v3
  • Number of active users per login method - SQL
  • How many members open the Welcome PM? - SQL
    • How many members open the Welcome PM, but just for the last X months? - SQL
  • Topics's list where I’ve been mentioned… but haven’t responded after being mentioned - SQL
  • Users (In Specific Group) Last Seen Since N Days Ago - SQL
  • Users In Specific Group(s) BUT NOT In Other Group(s) - SQL
  • Users who’ve become active: calculate what % of users, who joined within a certain timeframe, have posted X times, within a certain timeframe - SQL
  • Count Post Created for period - SQL
  • Users with first post within period - SQL
  • Non-PM topics that have not received a staff reply (with limit and category_id exclution) - SQL
    • Non-PM topics that have not received a staff reply (version 2) - SQL
  • Query for most valuable users in the given/chosen period of time - SQL
  • Some of stats from the admin report dashboard — but I’ve found them helpful to provide access to non-admin users - SQL
  • Who (logged in user) view specific topic - SQL
  • Last "x" topic view by user - SQL

Declaring Parameters

-- [params]
-- null string_list :words
-- null string_list :categories
-- user_id :user_id
-- int :limit = 150

WITH words AS (
 SELECT unnest(string_to_array( :words, ',' )) word
),
cats1 AS (
 SELECT unnest(string_to_array( :categories, ',')) cat
),
-- ...
AND p.user_id = :user_id
-- ...
LIMIT :limit

Desc or Asc order

But I’m interested in, is it possible to pass the asc or desc parameter to ORDER BY?

I don’t think it’s possible to pass a keyword as a parameter, but a boolean :desc parameter could be used in a CASE expression.

Example:

--[params]
-- boolean :desc = false
SELECT
*
FROM generate_series(1, 10) AS num
ORDER BY
  CASE WHEN :desc THEN num END DESC,
  CASE WHEN NOT :desc THEN num END ASC

Read more here

Errors

⚠️ For new errors please open a issue here and PUT the URL from meta discussion from discourse.org

Note! There are some strange problem with int parameter -reported here:

If you have that error, please delete the value declaration for int parameters.

For example, for -- int :limit = 150 ➡️ -- int :limit

And do not forget to save query, and complete the parameter before execute it.