title: How to fix scheduled query in BigQuery not returning results correctly
body: <p>I've written a few queries for BigQuery in standard SQL that are scheduled to run every day at 5AM UTC+2, but they return null or empty results.</p>
  
  <p>Specifically, the queries are looking at the previous day's data, so they take the <code>@run_date</code> parameter and subtract 1 day to get the correct date to be used as the <code>_table_suffix</code> (the data is column partitioned).</p>
  
  <p>I've tested running the queries manually, both from console (switching out <code>@run_date</code> for <code>CURRENT_DATE()</code> ) and by manually scheduling a backfill, using the current date as the run date.</p>
  
  
  
  <pre class="lang-sql prettyprint-override"><code>WITH installs AS (
      SELECT 
          COUNTIF(event_name="first_open" AND app_info.install_source="manual_install") AS manualInstalls,
          COUNTIF(event_name="first_open" AND app_info.install_source &lt;&gt; "manual_install") AS appStoreOtherInstalls,
          COUNTIF(event_name="first_open") AS installsTotal,
          COUNTIF(event_name="app_remove" AND user_id IS NOT NULL) AS uninstallRegistered,
          COUNTIF(event_name="app_remove" AND user_id IS NULL) AS uninstallUnregistered,
          COUNTIF(event_name="app_remove") AS uninstallsTotal
      FROM `project.dataset.events_*`
      WHERE _table_suffix=CAST(FORMAT_DATE("%Y%m%d",DATE_SUB(@run_date, INTERVAL 1 DAY)) AS STRING)
  ),
  
  maus AS (
      SELECT 
  
      COUNT( DISTINCT
          (SELECT 
              IF(
                  REGEXP_CONTAINS(user_id, 
                  CASE
                      WHEN user_id LIKE "+237%" THEN r"\+237((67)|(8[0-3])|(5[0-4]))"
                      WHEN user_id LIKE "+27%" THEN r"\+27((83)|(810)|(6((0[3-5])|(3([0-5]|[8-9]))|(40)|(5[5-7])))|7(1(0|[7-9])|(3|8)))"
                      WHEN user_id LIKE "+225%" THEN r"\+225(0|[4-8])[4-6]"
                      WHEN user_id LIKE "+242%" THEN r"\+24206[4-9]"
                  END
                  ),
              user_id, "")
          )
      ) AS mauNetwork,
  
      count(DISTINCT user_id) AS mau
      FROM `project.dataset.events_*`
      WHERE _table_suffix BETWEEN CAST(FORMAT_DATE("%Y%m%d",DATE_SUB(@run_date, INTERVAL 30 DAY)) AS STRING) 
      AND CAST(FORMAT_DATE("%Y%m%d",DATE_SUB(@run_date, INTERVAL 1 DAY)) AS STRING)
  )
  
  SELECT 
      CAST(FORMAT_DATE("%Y%m%d", DATE_SUB(@run_date, INTERVAL 1 DAY)) AS STRING) AS event_date, 
      IF(installs.manualInstalls IS NOT NULL, installs.manualInstalls, 0) AS manualInstalls, 
      IF(installs.appStoreOtherInstalls IS NOT NULL, installs.appStoreOtherInstalls, 0) AS appStoreOtherInstalls,
      IF(installs.installsTotal IS NOT NULL, installs.installsTotal, 0) AS installsTotal,
      IF(installs.uninstallRegistered IS NOT NULL, installs.uninstallRegistered, 0) AS uninstallRegistered, 
      IF(installs.uninstallUnregistered IS NOT NULL, installs.uninstallUnregistered, 0) AS uninstallNotRegistered, 
      IF(installs.uninstallsTotal IS NOT NULL, installs.uninstallsTotal, 0) AS uninstallsTotal,
      IF(maus.mau IS NOT NULL, maus.mau, 0) AS mau,
      IF(maus.mauNetwork IS NOT NULL, maus.mauNetwork, 0) AS mauNetwork
  FROM installs, signUps, daus, maus
  </code></pre>
  
  <p>I expected all columns to be populated, but they're zero-filled since they're null. The subquery block called <code>maus</code> does actually return results, but it has a much longer window - I suspect there's maybe something wrong with my logic around retrieving the previous day's data the way that I am.</p>
  
  <p>I'd really appreciate any insight / suggestions!</p>