sql

SET @target_time = '2024-06-24 23:59:59';#対象年月末を指定

SELECT
    user.emtg_id AS 'EMTG ID',
    registered_time AS '入会日',
FROM
    user
INNER JOIN fanclub_user ON user.id = fanclub_user.user_id
INNER JOIN fanclub_user_join_log ON fanclub_user.user_id = fanclub_user_join_log.user_id
INNER JOIN (
    SELECT
        user_id,
        max(created_time) AS created_time
    FROM
        fanclub_user_join_log
    WHERE
        created_time <= @target_time
    GROUP BY
        user_id
) AS latest_join_log ON fanclub_user_join_log.user_id = latest_join_log.user_id
AND fanclub_user_join_log.created_time = latest_join_log.created_time
WHERE
    fanclub_user.deleted_time IS NULL
AND fanclub_user.user_id NOT IN (
    SELECT
        user_id
    FROM
        user_attribute_relation
    WHERE
        user_attribute_id IN (1, 4, 5, 6, 7, 10, 11, 12, 13)
) # 関係者除外
AND fanclub_user_join_log.expire_time > @target_time
GROUP BY
    fanclub_user.user_id