Using [user_id] will only give you the user_id of the users profile. You'll need to use the below for example to give you the user_id of the viewing user.
[cb:userdata field="user_id" user="#me" /]
OR
[cb:userfield field="user_id" user="#me" /]
Example query as follows (important to surround in quotes, helps avoid query errors).
Code:
SELECT `cb_memberships` FROM `#__comprofiler` WHERE `id` = '[cb:userdata field="user_id" user="#me" /]'
krileon
