An SQL Query I don't know how to write (aMember wordpress plugin)

Question

I'm currently working on a site that uses aMember to manage payments for a WordPress site, to enforce the paywall. I was not the admin that set it up, and I am still learning my way around their database. Additionally, my SQL-fu is weak; I'm not an engineer by training but I'm the only one in the organization who knows any SQL at all.

My CFO wants a report that breaks down the number of new registrations by day, going back two years. To the best of my knowledge, aMember does not have a simple breakdown of this. What it DOES have is a complete record of every payment we have ever received.

I can easily break down this payment record by date to see how many payments we got on a given date. The trouble is, many of these payments are from existing users. Additionally, we have users that have subscribed, cancelled, then re-subscribed. Our threshold for this is 30 days, since we consider that a "full lapse". So, we want to ignore any payments made on [date] by users having a payment that has expired within 30 days of [date].

example: John Smith subscribed on 12/1/2012. John Smith's last payment expired on 1/1/2013. John's account does not renew. John resubscribed on 1/5/2013. John's payment should be counted as "new" on 12/1/2012 (since he had no prior payments on record), but not on 1/5/2013 (because he had a payment expire on 1/1, within 30 days of the 1/5 payment).

the schema is below. the current query, that does NOT filter out existing members renewing and is only a count of payments received, is as follows. if any additional information is needed, or someone has knowledge of aMember beyond my own, please let me know what else I can provide to facilitate an answer. I am in over my head and doing my best!

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

Can you please show some sample data for your table and expected results based on that? — bonCodigo, Feb 05 '13 at 18:59
can you be more specific? do you just want to see a few sample rows, or the result of the above query? — Kelly Reid, Feb 05 '13 at 19:24

score 2 · Answer 1 · answered Feb 06 '13 at 06:16

I think this will do it. It may need a bit of adjustment. I'm not sure exactly what the amount > 0 does, but it's included in the subquery as well.

Running it against some good test data to make sure it produces the correct result is probably a good idea.

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

An SQL Query I don't know how to write (aMember wordpress plugin)

1 Answers1