SQL-запрос, я не знаю, как написать (aMember WordPress плагин)

Question

В настоящее время я работаю над сайтом, который использует aMember для управления платежами за сайт WordPress, чтобы обеспечить платный доступ. Я не был администратором, который настроил это, и я все еще изучаю их базу данных. Кроме того, мой SQL-фу слабый; Я не инженер по образованию, но я единственный в организации, кто вообще знает SQL.

Мой финансовый директор хочет получить отчет, который разбивает количество новых регистраций по дням на два года назад. Насколько мне известно, aMember не имеет простой разбивки этого. То, что он имеет, является полной записью каждого платежа, который мы когда-либо получали.

Я могу легко разбить эту запись о платежах по дате, чтобы увидеть, сколько платежей мы получили за данную дату. Проблема в том, что многие из этих платежей поступают от существующих пользователей. Кроме того, у нас есть пользователи, которые подписались, отменили подписку, а затем снова подписались. Наш порог для этого составляет 30 дней, поскольку мы считаем, что это "полный перерыв". Поэтому мы хотим игнорировать любые платежи, сделанные в [дату] пользователями, у которых платеж истек в течение 30 дней после [даты].

пример: Джон Смит подписался на 1 декабря 2012 года. Срок последнего платежа Джона Смита истек 01.01.2013. Аккаунт Джона не обновляется. Джон подписался на 5 января 2013 года. Платеж Джона должен быть засчитан как "новый" 1 декабря 2012 года (так как у него не было никаких ранее зарегистрированных платежей), но не 5 января 2013 года (поскольку у него истек срок действия платежа 1/1 в течение 30 дней после оплата 1/5).

схема ниже. текущий запрос, который НЕ отфильтровывает обновление существующих членов и представляет собой только количество полученных платежей, выглядит следующим образом. если требуется какая-либо дополнительная информация или кто-то знает о членах помимо моего, пожалуйста, дайте мне знать, что еще я могу предоставить, чтобы облегчить ответ. Я нахожусь над моей головой и делаю все возможное!

SELECT begin_date, COUNT( payment_id ) 
FROM  [foo] 
WHERE amount >0
GROUP BY begin_date
ORDER BY begin_date DESC 

payment_id
member_id
product_id
begin_date
expire_date
paysys_id
receipt_id
amount
completed
remote_addr
data 
time

Answer 1

Я думаю, что это сделает это. Это может потребовать немного корректировки. Я не уверен точно, что делает сумма> 0, но он также включен в подзапрос.

Вероятно, хорошей идеей будет запускать его с хорошими тестовыми данными, чтобы убедиться, что он дает правильный результат.

SELECT begin_date, COUNT( payment_id ) 
FROM  [foo] f1
WHERE amount > 0
AND 
-- don't count it if there's another subscription from this member that expires between the 30 days previous to this one and the end of this one.
(select count(expire_date) from [foo] f2 where f1.member_id = f2.member_id and 
    f2.expire_date BETWEEN DATE_ADD(f1.begin_date, INTERVAL -30 day) and f1.expire_date 
    and f1.payment_id <> f2.payment_id and amount > 0) = 0
GROUP BY begin_date
ORDER BY begin_date DESC