SQL to pick up and calculate SUM

Question

I have 2 tables:

• Table 1 has 2 columns: Username and Join date
• Table 2 has 4 columns: Order No., Username, Sales date, Sales revenue

How to calculate the total sales revenue of each Username within 30 days from Join date? Thank you very much in advance!!!

Answer 1

You can use join and aggregation:

select u.username, sum(revenue)
from users u left join
     orders o
     on o.username = u.username and
        o.salesdate < u.joindate + interval 30 day
group by u.username