0

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: enter image description here

msulol
  • 67
  • 6

1 Answers1

1

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;
Adrian Klaver
  • 15,886
  • 2
  • 17
  • 28
  • Hi, thanks for sharing, no luck, ::interval doesnt seem to work with Athena (it uses presto) – msulol Jun 23 '22 at 21:20
  • What about substituting `cast('10 days' as interval)` for `'10 days'::interval`? – Adrian Klaver Jun 23 '22 at 21:26
  • i think i got it to work with this, need to test on larger data tomorrow. "where date_diff('day', support_date, purchasedate)<10 or date_diff('day', purchasedate, support_date)<10 and date_diff('day', purchasedate, support_date)>0" – msulol Jun 23 '22 at 21:34
  • See **UPDATE**. If you want to stick with your solution would it not be `<=10`? – Adrian Klaver Jun 23 '22 at 21:45
  • I was running into negative days, so i added the >0. here is the latest which fixes some of the issues, trying yours in 2 mins. "where (date_diff('day', support_date, purchasedate)<10 and date_diff('day', support_date, purchasedate)>0) or (date_diff('day', purchasedate, support_date)<10 and date_diff('day', purchasedate, support_date)>0)" – msulol Jun 23 '22 at 21:50
  • running into "unknown type: interval error. perhaps our presto is not up to date to support interval. – msulol Jun 23 '22 at 21:53
  • See my change from `interval '10 days'` to `interval '10' day`. This matches what is in the Presto docs. – Adrian Klaver Jun 23 '22 at 21:54
  • If the requirement does indeed include '...contacted us either 10 days before or after the purchase" then you are going to have negative days. – Adrian Klaver Jun 23 '22 at 21:59
  • Thanks that worked! it matches the results from my prior one and is easier to read! – msulol Jun 23 '22 at 21:59