WITH ride_assignments AS
  (SELECT *
   FROM
     (SELECT rd.*,
             row_number() OVER (PARTITION BY id
                                ORDER BY updated_at DESC) AS rdn
      FROM pathao_ride.ride_assignments AS rd
      WHERE created_at >= '2017-01-01'
        AND updated_at >= '2017-01-01' )
   WHERE rdn = 1 ),
     rides AS
  (SELECT *
   FROM
     (SELECT rd.*,
             row_number() OVER (PARTITION BY id
                                ORDER BY updated_at DESC) AS rdn
      FROM pathao_ride.rides AS rd
      WHERE created_at >= '2017-01-01'
        AND updated_at >= '2017-01-01' )
   WHERE rdn = 1 ),
     users AS
  (SELECT *
   FROM
     (SELECT *,
             ROW_NUMBER() OVER(PARTITION BY id
                               ORDER BY updated_at DESC) AS row_number
      FROM `data-cloud-production.pathao_ride.users`
      WHERE updated_at IS NOT NULL) AS A
   WHERE row_number = 1 )
SELECT a.driver_id,
       name AS Name,
       number AS Number,
       active_days,
       lifetime,
       ASSIGNMENT,COMPLETED/active_days as avg_rides,
       ASSIGNMENT-user_Cancelation AS Actual_Assignment,
                  COMPLETED,
                  CANCELED,
                  COMPLETED*100/(ASSIGNMENT-user_Cancelation+.0000000000000000000001) AS CR,
                  ASSIGNMENT- D_C AS SKIPPED,
                  D_C,
                  user_Cancelation
FROM (
        (SELECT driver_id,
                count(*) AS ASSIGNMENT
         FROM ride_assignments
         WHERE created_at BETWEEN "{{start_time}}" AND "{{end_time}}"
           AND EXTRACT(hour
                       FROM created_at)>={{
           FROM}}
           AND EXTRACT(hour
                       FROM created_at)<{{TO}}
         GROUP BY driver_id) a
      JOIN
        (SELECT driver_id,
                max(date(created_at)) AS Last_Active_Date,
                count(DISTINCT date(created_at)) AS active_days,
                date_diff(CURRENT_DATE, min(date(created_at)),DAY) AS lifetime,
                count(CASE
                          WHEN status="COMPLETED" THEN rider_id
                          ELSE NULL
                      END) AS COMPLETED,
                count(CASE
                          WHEN status="CANCELED" THEN rider_id
                          ELSE NULL
                      END) AS CANCELED,
                count(CASE
                          WHEN status="CANCELED"
                               AND canceled_by=driver_id THEN rider_id
                          ELSE NULL
                      END)D_C,
                count(CASE
                          WHEN status="CANCELED"
                               AND canceled_by=rider_id THEN rider_id
                          ELSE NULL
                      END) AS user_Cancelation
         FROM rides
         WHERE ride_type = {{ride_type}}
           AND city_id = 4
           AND created_at BETWEEN "{{start_time}}" AND "{{end_time}}"
           AND EXTRACT(hour
                       FROM created_at)>={{
           FROM}}
           AND EXTRACT(hour
                       FROM created_at)<{{TO}}
         GROUP BY 1) b ON a.driver_id = b.driver_id
      JOIN
        (SELECT id,
                name,number
         FROM users) c ON b.driver_id=c.id)