Retrieves a list of members who are expired on a given membership. Make sure to set the correct @membership_id on line 1

expired_members_per_membership.sql

SET @membership_id = 10379;
SELECT um_first_name.meta_value AS first_name,
       um_last_name.meta_value AS last_name,
       u.user_email AS email,
       l.lifetime_count,
       t.expires_at AS expires_at
  FROM wp_users AS u
  LEFT JOIN wp_usermeta AS um_first_name
    ON um_first_name.user_id = u.ID
   AND um_first_name.meta_key = 'first_name'
  LEFT JOIN wp_usermeta AS um_last_name
    ON um_last_name.user_id = u.ID
   AND um_last_name.meta_key = 'last_name'
  JOIN (
         SELECT u1.ID as user_id,
                (
                  SELECT COUNT(*)
                    FROM wp_mepr_transactions AS t1
                   WHERE t1.user_id = u1.ID
                     AND t1.product_id = @membership_id
                     AND t1.expires_at = '0000-00-00 00:00:00'
                ) AS lifetime_count
           FROM wp_users AS u1
        ) AS l
    ON l.user_id = u.ID
  JOIN (
         SELECT t2.user_id, max(t2.expires_at) AS expires_at
           FROM wp_mepr_transactions AS t2
          WHERE t2.product_id = @membership_id
          GROUP BY t2.user_id
       ) AS t
    ON t.user_id = u.ID
 WHERE t.expires_at < NOW()
   AND l.lifetime_count = 0