title: BigQuery/Firebase querying events, order by User Property
body: <p>I connected my Firebase Project to BigQuery and received all of yesterdays events today.</p>
  
  <p>I have a user defined event that has an Integer parameter.
  And a user property "user_job" of type String (E.g. "Pilot", "Mechanic", "Programmer",...)
  I want to know the average of the integer parameter grouped by the user job.</p>
  
  <p>Is there a SQL query that extracts that information?</p>
  
  <p>I know SQL but my problem is that the data is somehow nested in RECORDs and I am not sure how to deal with that.
  If it was one row per event and one column per user property I would not have this problem. If it was like this, I could write something like:</p>
  
  <pre><code>SELECT AVG( param ), user_job FROM table WHERE event_type = "my_event" GROUP BY user_job
  </code></pre>
  
  <p>But user properties are inside a RECORD (array?) and there are multiple events also inside a RECORD.</p>
  
  <p>Or maybe using SQL queries are the wrong way to deal with requests like that. If so, could someone please point me in the direction.
  I see that there are <a href="https://cloud.google.com/bigquery/user-defined-functions" rel="nofollow">user defined functions</a> in BigQuery. So should I create a user defined function that transforms the Firebase BigQuery rows into a flat structure like written above, is that the correct way?</p>
  
  <p><strong>Update:</strong>
  I ran the queries from Sara and Elliot and the bytes processed where the same in both cases (Which is the costs). Saras query ran faster than Elliots, but this might be random chance (and google charges for data, not time).
  Even so I marked Elliots answer as final answer, because according to the docs, Standart SQL is the recommended one.
  I still don't know how this all compares to UDF and might test that.</p>
  
  <p><strong>Update2:</strong>
  I tested it with a UDF implementation which emitted a new row (language, coins) for every round_completed event. It ran longer and processed more data than both SQL only solutions posted by Sara and Elliot.</p>
  
  <pre><code>|_Solution___|_Time___|_Data Processed___|
  |Sara        |2.1s    |399 KB            |
  |Elliot      |2.4s    |399 KB            |
  |UDF         |3.4s    |437 KB            |
  </code></pre>
  
  <p>*Note that the time might be a bad metric, because it is unknown how busy googles data center is at a given time. In the end less data processed costs less. I still included the time, maybe it is useful somehow.</p>