BigQuery Script for Getting Average Days of Specific Date Period

Question

I need to build a query for days on market (DOM) for Jan 2021 = AVG(sold date - contract date) for all properties that sold date is [20210101 =< Sold Date =< 20210131]

This is what I have written:

SELECT AVG(DATE_DIFF(field_3102,field_3103)) as DOM 
   FROM `winter-agility-327715.Properties_dataset.Final_Table` 
WHERE field_3102 BETWEEN '2021-01-01' AND '2021-01-31'

field_3102 is basically sold date

Answer 1

The overall logic on your written query already matches the above requirement. However, it will not run because of a syntax error using DATE_DIFF() function. To correct this, you must specify a date_part on your DATE_DIFF() function. You may refer to this BigQuery - DATE_DIFF Documentation for more details.

See below updated query.

SELECT AVG(DATE_DIFF(field_3102,field_3103,DAY)) as DOM 
   FROM `winter-agility-327715.Properties_dataset.Final_Table` 
WHERE field_3102 BETWEEN '2021-01-01' AND '2021-01-31'

Since your query requirement is DAYS on market, we used the date_part "DAY" for us to return the intervals in DAYS.