Possibility of example SQL script for CB Query

If you do not have a ton of usergroups I suggest simply using substitutions in a Custom HTML field as follows to avoid extra resource overhead of doing a query.

[cb:if usergroup includes "8"]
Super Users
[cb:elseif usergroup includes "7"]Administrator[/cb:elseif]
[cb:elseif usergroup includes "6"]Manager[/cb:elseif]
[cb:elseif usergroup includes "5"]Publisher[/cb:elseif]
[cb:elseif usergroup includes "4"]Editor[/cb:elseif]
[cb:elseif usergroup includes "3"]Author[/cb:elseif]
[cb:elseif usergroup includes "2"]Registered[/cb:elseif]
[/cb:if]

The above if specifically checking the group id, which you can find in Users > Groups from Joomla backend. Note support for ELSEIF substitution usage was just recently added. See the below blog for further details.

www.joomlapolis.com/blog/kyle/18708-substitution-power-up

I'll be updating the below substitution tutorial soon with the new functionality.

www.joomlapolis.com/documentation/279-community-builder/tutorials/18353-using-substitutions-throughout-cb

So I tried to copy/paste yours as is, and it doesn't seem to be working. The output on my profile (I am a super user, will display as:

So it seems to read the if statement, but the elseif statements are getting printed as text. For all the other users the field is just blank. I do have the newest 2.0.15 version of CB.

You need the latest nightly build, which is 2.0.15+build.2016.11.14.14.09.35.fac6068a1. I've tested the exact substitution supplied to you as working fine.