title: BigQuery Cross Join Much Faster with Each
body: <p>I'm trying to look at user activity by date. The first step is to build a table of every day since a user account was created, using cross join and a where clause. My first attempt was this:</p>
  
  <pre><code>SELECT
    u.user_id as user_id,
    date(u.created) as signup_date,
    cal.date as date,
  
  from rsdw.user u
    cross join (select date(dt) as date from [rsdw.calendar] where date(dt) &lt; CURRENT_DATE() ) cal
  where
    date(u.created) &lt;= cal.date
  </code></pre>
  
  <p>(The calendar table is just a list of all dates since 2006 (3288 rows). The user table has ~1m rows.) </p>
  
  <p>This query takes a forever... so long that I've abandoned it at 1000 seconds or so. I tried tweaking the query a little. If I add an "each" to the cross join:</p>
  
  <pre><code>SELECT
    u.user_id as user_id,
    date(u.created) as signup_date,
    cal.date as date,
  
  from rsdw.user u
    cross join each (select date(dt) as date from [rsdw.calendar] where date(dt) &lt; CURRENT_DATE() ) cal
  where
    date(u.created) &lt;= cal.date
  </code></pre>
  
  <p>I get an errror:</p>
  
  <pre><code>Error: Cannot CROSS JOIN two tables with EACH qualifiers.
  </code></pre>
  
  <p>Finally, if I keep the "each" but swap the tables, it completes in just 90s!</p>
  
  <pre><code>SELECT
    u.user_id as user_id,
    date(u.created) as signup_date,
    cal.date as date,
  
  from (select date(dt) as date from [rsdw.calendar] where date(dt) &lt; CURRENT_DATE() ) cal
    cross join each rsdw.user u
  where
    date(u.created) &lt;= cal.date
  </code></pre>
  
  <p>Can anyone explain why the third iteration is so much faster and why the second one results in an error?</p>