It’s great to have these all in one place, rather than spread across meta!
I see you’ve organised them into your fork of the discourse-data-explorer repository. Did you have any plans to improve the plugin to access the pre-made queries directly?
I think it would be really cool to have a “load from library” button in the plugin, which allows browsing these queries. Adding new queries to the library would then just need a PR. Would that be pr-welcome@team?
In terms of the data itself, I quite like how @SidV has stored them in their own folder, each in their own .sql file here
We would need to add some metadata to the top of the sql files. We already have [params], so maybe have something like
-- [title] Solved Answers
-- [description] Lists users with most solved topics
-- [source] https://meta.discourse.org/t/63981/4?u=sidv
-- [params]
-- string :interval = 1 year
SELECT ua.acting_user_id,
count(case t.user_id when ua.acting_user_id then 1 else null end) as is_op,
count(case t.user_id when ua.acting_user_id then null else 1 end) as not_op,
count(*) as total
FROM user_actions ua
LEFT JOIN topics t ON target_topic_id = t.id
WHERE action_type=15
AND ua.created_at >= CURRENT_DATE - INTERVAL :interval
GROUP BY ua.acting_user_id
I find the whole selection of queries from a combobox deeply flawed, and it breaks down big time when you have a lot of queries.
What we should simply do it display a full list of linkable queries on the front page, we can add an icon next to builtin to show that they are built in and not editable.
The whole “clone from library” workflow feels like busywork to me.
I was trying to keep within the existing structure of the UI, but rethinking it from scratch will probably give a better result. It does significantly increase the amount of work that this is though
I’m loving this initiative to make the best queries more readily available. But I wonder if it would not be safer and easier to just create a #queries category here on meta akin to the plugin with a topic per query. Safe, approved queries could be provided there and broken queries could be moved to a #broken-queries category.
The advantage is that we can discuss, ask questions about and refine the queries better right here on meta. It’s also much simpler to implement though of course takes some gardening.
I thought that the data-explorer plugin is inherently safe - it only has read-only access to the database, and there are timeouts in place to prevent performance issues.
Or are you simply talking about a query being working/not working?