Last active
October 9, 2022 21:23
-
-
Save mishterk/d5e260811cfcff96ed0f24757e88de74 to your computer and use it in GitHub Desktop.
Helpful SQL queries for working with the MemberPress WordPress plugin
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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'; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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")' | |
); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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)' | |
); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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
Hi.
Is it possible to write a sql query that extracts all the current members (which have a valid transaction expiring after today) grouped by their mepr-address-country?
I find it difficult to set it up... thanks!