how to pull data from a table based on date ranges?-CodePudding

Is it possible to have a sql query which pulls data per date and customer? Use case: Table 1 has items purchased by customers, with data of purchase Table 2 has all of the support tickets customers may have reached out for.

I am looking to filter table 2 data to customers who have purchased (in table 1) but also have contacted us either 10 days before or after the purchase.

So if Bob purchases a screw driver on 6/1/2022 (on table 1). i am looking to pull any calls from Bob between 5/21/22 to 6/10/22

A picture to help illustrate:

CodePudding user response：

Untested code using date range:

SELECT
   support_id, customer, date_of_support, details
FROM 
   table_1 AS t1
JOIN 
   table_2 AS t2
ON 
   t1.customer = t2.customer 
WHERE 
    daterange(date_of_purchase - '10 days'::interval, date_of_purchase   '10 days'::interval, '[]') @> date_of_support

The '[]' is included in the daterange to make the upper bound inclusive. In other words it will bump the upper date up one.

UPDATE

From here Date/time functions/operators this would seem to be possible in Athena/Presto:

...
WHERE 
   date_of_support BETWEEN date_of_purchase - interval '10 days' 
    AND date_of_purchase   interval '10' day;