# Data Explorer - Query to determine user theme preferences
**URL:** https://meta.discourse.org/t/data-explorer-query-to-determine-user-theme-preferences/106718
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [January 16, 2019, 5:55pm UTC](https://meta.discourse.org/t/data-explorer-query-to-determine-user-theme-preferences/106718 "2019-01-16T17:55:37Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@RobMeade](https://meta.discourse.org/u/RobMeade)
#### Post date: [January 16, 2019, 5:55pm UTC](https://meta.discourse.org/t/data-explorer-query-to-determine-user-theme-preferences/106718/1 "2019-01-16T17:55:37Z")
Hi all,
Wondering if anyone can help me with this.
I’m trying to determine how many users are using either the _dark_ or _light_ themes, I’ve cobbled this together for the [Data Explorer](https://meta.discourse.org/t/32566?silent=true);
```plaintext
/* light theme */
SELECT COUNT(*)
FROM user_options
WHERE theme_ids[1] = 6
```
From what I can tell the ID of the theme is stored in the field `theme_ids` and in array element 1, could anyone confirm this?
In our set up the _dark_ theme appears as a value of 7, and the _light_ theme a value of 6.
However, when I run the queries I get 140 people using dark, 32 using light. As we have several thousand users this seems a bit out so I’m guessing my cobbled together query is either wrong - or - are these values only stored for users that have _specifically set_ a theme, where-as if they are just using the default this data isn’t stored?
Any info would be really appreciated.
---
### Author:  [@Johani](https://meta.discourse.org/u/Johani)
#### Post date: [January 16, 2019, 6:03pm UTC](https://meta.discourse.org/t/data-explorer-query-to-determine-user-theme-preferences/106718/2 "2019-01-16T18:03:28Z")
I believe @osama has already created a query that allows you to check a theme’s popularity. Have a look here
> [@Is there any way of telling how popular a Theme is?](https://meta.discourse.org/t/is-there-any-way-of-telling-how-popular-a-theme-is/93796/4):
>
> You don’t really need to look at the users table here to display usernames. [Data Explorer](https://meta.discourse.org/t/32566?silent=true) 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](https://meta.discourse.org/t/32566?silent=true) 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]…
---
### Author:  [@RobMeade](https://meta.discourse.org/u/RobMeade)
#### Post date: [January 16, 2019, 6:07pm UTC](https://meta.discourse.org/t/data-explorer-query-to-determine-user-theme-preferences/106718/3 "2019-01-16T18:07:03Z")
Hi Joe,
Many thanks for the link, and the swift response! I’m pleased to see that I was looking at the right fields.
Just to confirm, is the data that is returned for only the users that have _specifically set_ their theme, e.g. any users who haven’t set it wouldn’t be returned in the data set, so we could just assume that they are using the _default_ theme?
Perfectly fine if this is the case, just want the clarification please 🙂
---
### Author:  [@Johani](https://meta.discourse.org/u/Johani)
#### Post date: [January 16, 2019, 6:25pm UTC](https://meta.discourse.org/t/data-explorer-query-to-determine-user-theme-preferences/106718/4 "2019-01-16T18:25:29Z")
No worries Rob,
I checked with @Osama and indeed, the query would only show those who’ve set a theme in their preferences that’s different from the default on your site.
It also includes those who’ve changed back to the default theme after choosing some other non-default theme.
For all intents and purposes, all users that the query returns that use the default theme + any users that don’t show up can be counted as users that use the default theme.
The query will also return a breakdown of the number of users that chose any other user-selectable themes on your site.
---
### Author:  [@RobMeade](https://meta.discourse.org/u/RobMeade)
#### Post date: [January 16, 2019, 6:27pm UTC](https://meta.discourse.org/t/data-explorer-query-to-determine-user-theme-preferences/106718/5 "2019-01-16T18:27:21Z")
Hey Joe,
Awesome, many thanks for the confirmation and info and thanks for @Osama who’s query looks better than mine - albeit I got the same results - but I’ll pinch that one 😉
---
### Author:  [@Johani](https://meta.discourse.org/u/Johani)
#### Post date: [January 18, 2019, 10:00am UTC](https://meta.discourse.org/t/data-explorer-query-to-determine-user-theme-preferences/106718/6 "2019-01-18T10:00:11Z")
This topic was automatically closed after 39 hours. New replies are no longer allowed.