I have a table with users that come to purchase products on some dates. Now I want to create a query where I see for each user, what their purchase data was within the last 14 days from a given date. Is self-join the best option for such operation? I tried:

Select a.user_id, a.date, avg(b.sales) avg_sales
from purchase a
join purchase b
on a.user_id=b.user_id
and a.date>dateadd('day',-14,b.date) --get data for last 14 days from a.date (i.e. if a.date is 2020-01-16, then get the avg of all purchases from 2020-01-01 TO 2020-01-15)

This query is not working as expected and takes also too long.