Users are discussing ways to determine the popularity of themes. merefield started by creating a query in Data Explorer, but struggled to get the desired results. Mittineague suggested changing the LEFT JOIN to a simple JOIN to avoid returning NULL fields.
merefield updated the query, discovering that the current default theme is stored in the theme_ids array in the user_options field. However, the query revealed that only two users, including merefield themselves, had selected a different default theme.
Osama provided a more efficient query that displays the number of users using each theme, without the need to join the users table. The query uses a WITH clause to calculate the theme statistics and joins the themes table to display the theme names.
The final query confirmed merefield’s suspicion that only one user, aside from themselves, uses the theme selection feature.
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 …
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)
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! 
:
I guess the issue is you can’t see what users have set on their local, by device, settings?
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. 
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
Now that was just showing off @Osama 
Brilliant, thanks!
(But yeah, sigh, confirms that I only have one user that uses the feature aside from me )