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)'; |