Problems with translating MYSQL query using date_add function and time intervals for BQ

Question

I have a query that runs on our MYSQL database. It takes forever to run, so I would like to use BigQuery instead. The relevant table (a.xxx) is already in the cloud, and I've tried adjusting the code for BQ, but I'm not having any luck. The query basically pulls the number of individuals making purchases by day, and the number of those same individuals, who made another purchase 1-7 days after the initial purchase. I would appreciate any help!!!!

Here is the query:

    select f.fts_date, 
count(distinct f.FTS_id) as FTS_count, 
count(distinct s.passportid) as SVS_count, 
(count(distinct s.passportid)/count(distinct f.FTS_id)) as return_rate 

from

(select passportid as FTS_id,addressid, date(signdatetime) as FTS_date from a.xxx
where  date(signdatetime)>'2015-6-10'  and fts="Y" and disposition="accepted") as f

left join a.xxx as s
on f.passportid=s.passportid and f.addressid=s.addressid and s.disposition="accepted" and 
s.signdatetime between date_add(f.signdatetime, 1, "DAY") and date_add(f.signdatetime, 7, "DAY")  

group by 1

Answer 1

BigQuery doesn't support INTERVAL keyword in DATE_ADD function, instead you should write it as

date_add(FTS_date, 1, "DAY")

See https://cloud.google.com/bigquery/query-reference#datetimefunctions for more details