I started messing around in Data Explorer, but I’m not certain the right fields exist on the database?.. The query so far: select u.username, t.id, t.name from users u left join user_options o on u.id = o.user_id left join themes t on o.theme_key_seq = t.id where o.theme_key_seq <> 0 This doesn’t yet work. (And I’m far from a group and a count) Something about the implementation I’m missing I suspect …

[image] merefield: select u.username, t.id, t.name from users You don’t really need to look at the users table here to display usernames. Data Explorer will automatically display avatars and username if the result of your query has a user_id column which the user_options table does have. :slight_smile: Anyway, this topic got me personally interested so I spent a few minutes playing with data explorer and I got this little query which shows you user-selectable themes with the number of users using each theme: WITH theme_stats AS ( SELECT theme_ids[1] AS theme_id, COUNT(*) FROM user_options WHERE array_length(theme_ids, 1) <> 0 GROUP BY theme_id ) SELECT count AS "Number of users", themes.name AS "Theme name" FROM theme_stats JOIN themes ON themes.id = theme_stats.theme_id WHERE themes.user_selectable ORDER BY "Number of users" DESC

Yep, ok, thanks! So here’s what I’ve got now with some more exploration. Seems like current default theme is stored in user options field theme_ids which is an ARRAY … and in the 1th element, so theme_ids[1]. select u.username, t.id, t.name from users u inner join user_options o on u.id = o.user_id inner join themes t on o.theme_ids[1] = t.id where theme_ids[1] <> 0 In my case it’s not even worth grouping and counting because generally users don’t seem to have selected a different default theme to the default, default one - it comes back with two users (out of a regular bunch of 300!), and one of them is … ME! :smiley: : I guess the issue is you can’t see what users have set on their local, by device, settings?

Now that was just showing off @Osama :wink: Brilliant, thanks! (But yeah, sigh, confirms that I only have one user that uses the feature aside from me :wink: )

Is there any way of telling how popular a Theme is?

Community Building Data & reporting

Mittineague (Mittineague) August 2, 2018, 8:00pm 2

Well, using LEFT JOIN might be returning a lot of NULL fields in the results. Try changing those to simple JOIN instead (INNER JOIN is the Postgres default for JOIN)

Topic		Replies	Views
Data Explorer - Query to determine user theme preferences Data & reporting sql-query	4	940	January 16, 2019
Stats on theme utilisation Product Support	3	592	December 16, 2022
What's the 'featured users' feature? Product Support	6	703	February 28, 2022
Previously selected user themes can override allowed themes Product Support	15	1578	May 19, 2019
Per-user theme selection Feature dark-theme	0	3330	August 24, 2015

Is there any way of telling how popular a Theme is?

Related topics