title: SQL: Ride Share data to find 4-week moving average of time to pickup
body: <p>I was asked this question in an interview but I could not answer it correctly.
  Here I have 3 tables</p>
  <ol>
  <li>Driver</li>
  </ol>
  <ul>
  <li>driver_id Unique identifier for a driver</li>
  <li>driver_onboard_date Date on which driver was on-boarded</li>
  </ul>
  <ol start="2">
  <li>Rides</li>
  </ol>
  <ul>
  <li>driver_id Unique identifier for a driver</li>
  <li>ride_id Unique identifier for a ride that was completed by the driver</li>
  <li>ride_distance Ride distance in meters</li>
  <li>ride_duration Ride durations in seconds</li>
  <li>ride_prime_time PrimeTime applied on the ride</li>
  </ul>
  <ol start="3">
  <li>Ride_type</li>
  </ol>
  <ul>
  <li>ride_id Unique identifier for a ride</li>
  <li>event event describes the type of event( requested_at, accepted_at, arrived_at, picked_up_at, dropped_off_at)</li>
  <li>timestamp Time of event</li>
  </ul>
  <p>Q1. Trend of 4-week moving average of time to pickup (time elapsed between
  accepted_at and picked_up at)</p>
  <p>I tried the following but didn't seem like it worked.</p>
  <pre><code>SELECT
  
  TO_CHAR (T1.TIMESTAMP,’WW’) AS WEEK_NUM,
  
  AVG( ( CAST(T1.TIMESTAMP AS DATE) – CAST( T2.TIMESTAMP AS DATE ))*60*24) AS
  TIME_TO_PICKUP_MIN,
  
  AVG(AVG( ( CAST(T1.TIMESTAMP AS DATE) – CAST( T2.TIMESTAMP AS DATE ))*60*24) )
  OVER (ORDER BY TO_CHAR(T1.TIMESTAMP,’WW’) ROWS BETWEEN 3 PRECEDING AND
  CURRENT ROW) AS AVG_FOUR_WEEK_TREND
  
  FROM RIDE_TIMESTAMPS T1 INNER JOIN RIDE_TIMESTAMPS T1 ON T1.RIDE_ID=T2.RIDE_ID AND
  T1.EVENT=’accepted_at’ AND T2.EVENT=’picked_up_at’
  
  GROUP BY TO_CHAR(T1.TIMESTAMP,’WW’)
  </code></pre>