Wordpress Users and UserMeta SQL.

wp_users_utils.sql

--- Date last logged in ---
SELECT u.ID, u.user_email
, MAX(CASE WHEN um.meta_key = 'first_name' then um.meta_value ELSE NULL END) as first_name 
, MAX(CASE WHEN um.meta_key = 'last_name' then um.meta_value ELSE NULL END) as last_name 
, MAX(CASE WHEN um.meta_key = '_last_login' then um.meta_value ELSE NULL END) as last_login
FROM wp_users u LEFT JOIN wp_usermeta um ON ( um.user_id = u.ID) 
WHERE um.meta_key in ('first_name', 'last_name','_last_login')
GROUP BY u.ID
ORDER BY last_login DESC
LIMIT 1000

--- Flatten meta to get FirstName/LastName and other custom fields stored in UserMeta ---
SELECT u.ID, u.user_email
, MAX(CASE WHEN um.meta_key = 'first_name' then um.meta_value ELSE NULL END) as first_name 
, MAX(CASE WHEN um.meta_key = 'last_name' then um.meta_value ELSE NULL END) as last_name 
, MAX(CASE WHEN um.meta_key = 'newsletterPermission' then um.meta_value ELSE NULL END) as newsletterPermission 
, MAX(CASE WHEN um.meta_key = 'emailPermission' then um.meta_value ELSE NULL END) as emailPermission 
FROM wp_users u LEFT JOIN wp_usermeta um ON ( um.user_id = u.ID) 
GROUP BY u.ID


--- Find registered users who do not have a First or Last Name (most likely bot registrations ---
SELECT *
FROM wp_users u 
LEFT OUTER JOIN wp_cause_users_assoc wcua ON u.ID = wcua.user_id
WHERE u.id in 
( 
  SELECT user_id 
  FROM wp_usermeta 
  WHERE (meta_key = 'first_name' and meta_value = '') 
  OR (meta_key = 'last_name' and meta_value = '') 
)
AND wcua.user_id IS NULL -- Those without a relationship to wcua


SELECT u.*
, MAX(CASE WHEN um.meta_key = 'first_name' then um.meta_value ELSE NULL END) as first_name 
, MAX(CASE WHEN um.meta_key = 'last_name' then um.meta_value ELSE NULL END) as last_name
FROM wp_users u LEFT JOIN wp_usermeta um ON ( um.user_id = u.id)
WHERE u.id in
  ( 
    SELECT user_id 
    FROM wp_usermeta 
    WHERE (meta_key = 'first_name' and meta_value = '') 
    OR (meta_key = 'last_name' and meta_value = '') 
  )
GROUP BY u.id