How do find the number of revenue per user with a start date for a specific amount of days

Question

Say you have a table of users with the date they signed up. And another table with their revenue How could we calculate the amount of revenue say for 6 months or 9 months of each member since they signed up using mySQL command?

Table 1 has 2 columns: 1. user_id 2. date_of_join Table 2 has 4 columns: 1. purchase_id 2. user_id 3. purchase_amount 4. purchase_date.

I would like to do something like that

SELECT Users.user_id, AVG(Purchases.purchase_amount)
FROM Users
INNER JOIN Purchases
WHERE MONTH(Users.date_of_join) = 06
AND AVG(Purchases.purchase_amount) BETWEEN Users.date_of_join AND DATE_ADD(date_of_join, INTERVAL 30);

Answer 1

This would select all purchases for 6 months since date of sign up for user with id 5

SELECT p.*
FROM Purchases p
INNER JOIN Users u ON u.id=p.user_id
WHERE p.purchase_date BETWEEN u.date_of_join AND DATE_ADD(u.date_of_join, INTERVAL 6 MONTH) AND u.id=5;

Selecting the average is then trivial

SELECT AVG(p.purchase_amount)
FROM Purchases p
INNER JOIN Users u ON u.id=p.user_id
WHERE p.purchase_date BETWEEN u.date_of_join AND DATE_ADD(u.date_of_join, INTERVAL 6 MONTH) AND u.id=5;

---

If you want all the intervals as separate columns, then it becomes a bit more complex, but is still fairly easy.

SELECT avg_3.amount as avg_3, avg_6.amount as avg_6, avg_9.amount as avg_9
FROM Users u

INNER JOIN (
    SELECT AVG(p.purchase_amount) amount, p.user_id
    FROM Purchases p
    INNER JOIN Users u ON u.id=p.user_id
    WHERE p.purchase_date BETWEEN u.date_of_join AND DATE_ADD(u.date_of_join, INTERVAL 3 MONTH)
    GROUP BY p.user_id
) as avg_3 ON u.id=avg_3.user_id

INNER JOIN (
    SELECT AVG(p.purchase_amount) amount, p.user_id
    FROM Purchases p
    INNER JOIN Users u ON u.id=p.user_id
    WHERE p.purchase_date BETWEEN u.date_of_join AND DATE_ADD(u.date_of_join, INTERVAL 6 MONTH)
    GROUP BY p.user_id
) as avg_6 ON u.id=avg_6.user_id

INNER JOIN (
    SELECT AVG(p.purchase_amount) amount, p.user_id
    FROM Purchases p
    INNER JOIN Users u ON u.id=p.user_id
    WHERE p.purchase_date BETWEEN u.date_of_join AND DATE_ADD(u.date_of_join, INTERVAL 9 MONTH)
    GROUP BY p.user_id
) as avg_9 ON u.id=avg_9.user_id

WHERE u.id=5;