Blue Week Special Offer | Brighten your week!
days
hours
minutes
seconds

Filter Date in One column with SQL

I have table like this:

customer_id    date    order_id
12           1/11/2021     2
12           22/11/2021    3
12           31/11/2021    5
42           1/11/2021     2
42           15/11/2021    2
42           31/11/2021    2
43           22/11/2021    1
43           25/11/2021    2

I want to select only the customer_id that are 30 days between their first and last purchase, Then make a join with the product table, which means something like this:

customer_id    date    order_id   Product_name
12           1/11/2021     2         apple
12           22/11/2021    3         car
12           31/11/2021    5         orange
42           1/11/2021     2         apple
42           15/11/2021    2         apple
42           31/11/2021    2         apple

for example:

select customer_id, date, order_id, product_name
left join product on order_id = product_id
where customer_id.max(date) - customer_id.min(date) = 30 

What database are you working with?

If it’s SQLite, can you provide a database file with the data and code that better approximates what you want? This like customer_id.min(date) are very confusing, it’s like you’re writing Python in SQL.