Helpful SQL queries for working with the MemberPress WordPress plugin

count-new-members-between-two-dates.sql

SELECT count(DISTINCT txns.user_id)
FROM (
       SELECT
         user_id,
         min(created_at) AS first_txn_created_at
       FROM wp_mepr_transactions
       WHERE status IN ('complete', 'confirmed')
       GROUP BY user_id
     ) AS first_txns
  INNER JOIN wp_mepr_transactions AS txns
    ON txns.user_id = first_txns.user_id
       AND txns.created_at = first_txns.first_txn_created_at
WHERE 
    created_at >= '2018-04-01 00:00:00' 
    AND created_at < '2018-05-01 00:00:00';

count-number-of-paying-members-with-an-active-membership-at-a-point-in-time.sql

SELECT count(DISTINCT txns.user_id)
FROM (
       SELECT
         user_id,
         max(expires_at) AS latest_txn_expires_at
       FROM wp_mepr_transactions
       WHERE status IN ('complete', 'confirmed')
       GROUP BY user_id
     ) AS latest_txns
  INNER JOIN wp_mepr_transactions AS txns
    ON txns.user_id = latest_txns.user_id
       AND txns.expires_at = latest_txns.latest_txn_expires_at
WHERE
  amount + tax_amount >= 0
  AND expires_at > '2018-04-01 00:00:00'
  AND created_at < '2018-04-01 00:00:00'
  # omit internal users by role
  AND txns.user_id NOT IN (
    SELECT DISTINCT user_id AS internal_user
    FROM wp_usermeta
    WHERE
      meta_key = 'wp_capabilities'
      AND meta_value REGEXP '("administrator"|"editor"|"author"|"contributor")'
  );

count-users-with-active-subscriptions-during-a-period.sql

SELECT count(DISTINCT user_dates.user_id)
FROM (
       SELECT
         user_id,
         min(created_at) AS start_date,
         max(expires_at) AS paid_up_to
       FROM wp_mepr_transactions
       WHERE status IN ('complete', 'confirmed')
       GROUP BY user_id
     ) AS user_dates
WHERE
  user_dates.start_date < '2017-07-01 00:00:00' # start date is before the end of the period
  AND (
    user_dates.paid_up_to >= '2017-06-01 00:00:00' # latest transaction expires after the beginning of the period
    OR user_dates.paid_up_to IS NULL 
    OR user_dates.paid_up_to = '0000-00-00 00:00:00'
  )
  # don't count internal users
  AND user_dates.user_id NOT IN (
    SELECT DISTINCT user_id AS internal_user
    FROM wp_usermeta
    WHERE
      meta_key = 'wp_capabilities'
      AND meta_value REGEXP '(administrator|editor|author|contributor)'
  );

select-first-complete-and-confirmed-transactions-for-each-user.sql

SELECT *
FROM (
       SELECT
         user_id,
         min(created_at) AS first_txn_created_at
       FROM wp_mepr_transactions
       WHERE status IN ('complete', 'confirmed')
       GROUP BY user_id
     ) AS first_txns
  INNER JOIN wp_mepr_transactions AS txns
    ON txns.user_id = first_txns.user_id
       AND txns.created_at = first_txns.first_txn_created_at;

select-user-ids-that-do-not-have-transactions.sql

# count non-internal users that have the member role but don't have any transactions in MemberPress
SELECT DISTINCT user_id
FROM wp_usermeta AS umeta
WHERE
  umeta.user_id NOT IN (
    SELECT DISTINCT user_id
    FROM wp_mepr_transactions
  )
  AND meta_key = 'wp_capabilities'
  AND meta_value LIKE '%member-role%'
  AND meta_value NOT REGEXP '(administrator|editor|author|contributor)';