title: SQL Solve conflicting results in finding AVG trip duration for each day in a year
body: <p>I have a table that looks like this.
  It contains bike trip data from 2 different <code>user_type</code>: member and casual.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>trip_id</th>
  <th>started_at</th>
  <th>ended_at</th>
  <th>user_type</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>2019-01-01 07.30 UTC</td>
  <td>2019-01-01 07.40 UTC</td>
  <td>member</td>
  </tr>
  <tr>
  <td>2</td>
  <td>2019-01-01 09:20 UTC</td>
  <td>2019-01-01 09:46 UTC</td>
  <td>member</td>
  </tr>
  <tr>
  <td>3</td>
  <td>2019-01-02 14:20 UTC</td>
  <td>2019-01-02 14:45 UTC</td>
  <td>casual</td>
  </tr>
  <tr>
  <td>......</td>
  <td>......</td>
  <td>......</td>
  <td>......</td>
  </tr>
  <tr>
  <td>9999</td>
  <td>2019-12-31 06:20 UTC</td>
  <td>2019-12-31 06:50 UTC</td>
  <td>casual</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Inside the result table, I try to get both  average number of trips AND average trip duration taken by each user type each day.</p>
  <pre><code>WITH cleaned_data2 AS (
    SELECT 
      user_type, 
      COUNT(*) trips, 
      AVG(TIMESTAMP_DIFF(ended_at, started_at, MINUTE)) avg_trip_duration,
      FORMAT_DATE('%a',DATE(started_at)) day_of_week,
      EXTRACT (dayofweek FROM DATE(started_at)) pos
    FROM `capstone-361002.Cyclistic_bike_share.Merged-bike-trip-data`
    GROUP BY user_type, day_of_week, DATE(started_at), pos
    )
  
  SELECT day_of_week,  
    CAST(avg_num_trips_member AS int64) avg_num_trips_member,
    CAST(avg_num_trips_casual AS int64) avg_num_trips_casual,
    avg_duration_member,
    avg_duration_casual
    
  FROM cleaned_data2
  PIVOT (AVG(trips) avg_num_trips, AVG(avg_trip_duration) avg_duration FOR user_type IN ('member', 'casual')) AS pv
  ORDER BY pos
  </code></pre>
  <p>Format of result table :</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>day_of_week</th>
  <th>avg_num_trips_member</th>
  <th>avg_num_trips_casual</th>
  <th>avg_duration_member</th>
  <th>avg_duration_casual</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Sun</td>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
  </tr>
  <tr>
  <td>Mon</td>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
  </tr>
  <tr>
  <td>TUe</td>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
  </tr>
  <tr>
  <td>Wed</td>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
  </tr>
  <tr>
  <td>Thu</td>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
  </tr>
  <tr>
  <td>Fri</td>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
  </tr>
  <tr>
  <td>Sat</td>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>However,  I find that the value of AVG duration columns differ greatly when I instead wrote this query:</p>
  <pre><code>SELECT FORMAT_DATE('%a',DATE(started_at)) day_of_week,
    AVG(TIMESTAMP_DIFF(ended_at, started_at, MINUTE)) avg_trip_duration
  FROM my_table
  GROUP BY user_type, day_of_week
  </code></pre>
  <p>Any idea why this happened? Which one is correct? And how should I revise the first query to get the correct average duration of trips taken each day in a year?</p>