How to calculate in SQL average revenue from unique dates per user

Question

I'm having trouble finding the totals when it comes to 'within 1 & 2 months of the signup date'. I would love some help and some feedback on my current code. No errors, just not sure how to address the question

The question is: Find the average revenue per member that signed up in January within 1 month and 2 months of their signup date

The data table is:

Table 1: Memberships

member_id (PK) int signup_date datetime join_country varchar(2) email varchar cancellation_date date expiry_date date

Table 2: Transactions

transaction_id (PK) int member_id int transaction_date datetime transaction_type_ID int product_id int transaction_amount float

Current code:

SELECT AVG (t.transaction_amount) AS AVG_REV, 
    COUNT(m.member_id) AS Member_ID
    FROM transactions AS t
    INNER JOIN memberships AS m
    ON m.member_id = t.member_id 
    WHERE m.signup_date BETWEEN '2018-01-01' AND '2018-01-31'
    GROUP BY m.member_id
;

Answer 1

Find the average revenue per member that signed up in January within 1 month and 2 months of their signup date

This sounds like you want two numbers: The average 1 month revenue after signup and the average two month revenue after signup. There really isn't an "average revenue" for each member_id. There is a total revenue for each member, and then the average across all members.

I think the best approach is to aggregate the revenue by member and then take the averages. By member, the total revenue in these periods is:

SELECT m.member_id,
       SUM(t.transaction_amount) AS member_revenue, 
       SUM(t.transaction_amount) FILTER (WHERE t.transaction_date < m.signup_date + interval '1 month') AS member_revenue_month_1,
       SUM(t.transaction_amount) FILTER (WHERE t.transaction_date < m.signup_date + interval '2 month') AS member_revenue_month_2
FROM transactions t JOIN
     memberships m
     ON m.member_id = t.member_id 
WHERE m.signup_date >= '2018-01-01' AND
      m.signup_date < '2018-02-01'
GROUP BY m.member_id;

Then, aggregate again for the overall averages:

SELECT AVG(member_revenue_month_1),
       AVG(member_revenue_month_2)
FROM (SELECT m.member_id,
             SUM(t.transaction_amount) AS member_revenue, 
             SUM(t.transaction_amount) FILTER (WHERE t.transaction_date < m.signup_date + interval '1 month') AS member_revenue_month_1,
             SUM(t.transaction_amount) FILTER (WHERE t.transaction_date < m.signup_date + interval '2 month') AS member_revenue_month_2
      FROM transactions t JOIN
           memberships m
           ON m.member_id = t.member_id 
      WHERE m.signup_date >= '2018-01-01' AND
            m.signup_date < '2018-02-01'
      GROUP BY m.member_id;
     ) m

Answer 2

SELECT 
    m.member_id,
    AVG(CASE WHEN t.transaction_date <= m.signup_date + '1 month'::interval THEN t.transaction_amount END) AS AVG_1ST_MONTH, 
    AVG(CASE WHEN t.transaction_date > m.signup_date + '1 month'::interval AND t.transaction_date <= m.signup_date + '2 month'::interval THEN t.transaction_amount END) AS AVG_2ND_MONTH
FROM transactions AS t
INNER JOIN memberships AS m
    ON m.member_id = t.member_id 
WHERE m.signup_date BETWEEN '2018-01-01' AND '2018-01-31'
GROUP BY m.member_id

Also, the alias of this part in your original query doesn't make sense: COUNT(m.member_id) AS Member_ID - this will actually count the number of records (you could say the number of transactions) for each member, so I'm not sure why you named it Member_ID