Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Helpful SQL queries for working with the MemberPress WordPress plugin
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';
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")'
);
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 *
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;
# 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)';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.