conditions site/user

clickio

Mar 15th, 2022 (edited)

Never

Add comment

Not a member of Pastebin yet? Sign Up, it unlocks many cool features!

text 0.80 KB | None | 0 0

raw download clone embed print report

SELECT
TRIM('/' FROM REPLACE(REPLACE(REPLACE(s.url,'/www.','/'),'http://',''),'https://','')) AS url,
COUNT(DISTINCT u.user_id) AS count_users,
GROUP_CONCAT(DISTINCT CONCAT(u.user_id," (",u.registration_date,")") SEPARATOR ", ") AS users
FROM rabota_db.`user` u
LEFT JOIN `cmf_user_role_link` l ON u.`user_id` = l.`user_id` AND l.cmf_role_id = 29
LEFT JOIN rabota_db.`site` s ON u.`user_id` = s.`user_id`
WHERE 1=1
AND u.`status` IN (1) # только одобренные и не заблокированные
AND u.`is_internal_user` = 0 # internal users
AND l.`user_id` IS NULL # не сотрудники
AND u.`webmaster_cmf_subsystem_id` IN (19,20,21) # только npm
AND u.`billing_source` <> 'nobilling'
AND s.`moved_to_user_id` IS NULL
AND u.`moved_to_user_id` IS NULL
AND s.`deleted`=0
;

Add Comment

Please, Sign In to add comment