SHARE
TWEET

Untitled

a guest Feb 26th, 2020 84 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. WITH ride_assignments AS
  2.   (SELECT *
  3.    FROM
  4.      (SELECT rd.*,
  5.              row_number() OVER (PARTITION BY id
  6.                                 ORDER BY updated_at DESC) AS rdn
  7.       FROM pathao_ride.ride_assignments AS rd
  8.       WHERE created_at >= '2017-01-01'
  9.         AND updated_at >= '2017-01-01' )
  10.    WHERE rdn = 1 ),
  11.      rides AS
  12.   (SELECT *
  13.    FROM
  14.      (SELECT rd.*,
  15.              row_number() OVER (PARTITION BY id
  16.                                 ORDER BY updated_at DESC) AS rdn
  17.       FROM pathao_ride.rides AS rd
  18.       WHERE created_at >= '2017-01-01'
  19.         AND updated_at >= '2017-01-01' )
  20.    WHERE rdn = 1 ),
  21.      users AS
  22.   (SELECT *
  23.    FROM
  24.      (SELECT *,
  25.              ROW_NUMBER() OVER(PARTITION BY id
  26.                                ORDER BY updated_at DESC) AS row_number
  27.       FROM `data-cloud-production.pathao_ride.users`
  28.       WHERE updated_at IS NOT NULL) AS A
  29.    WHERE row_number = 1 )
  30. SELECT a.driver_id,
  31.        name AS Name,
  32.        number AS Number,
  33.        active_days,
  34.        lifetime,
  35.        ASSIGNMENT,COMPLETED/active_days as avg_rides,
  36.        ASSIGNMENT-user_Cancelation AS Actual_Assignment,
  37.                   COMPLETED,
  38.                   CANCELED,
  39.                   COMPLETED*100/(ASSIGNMENT-user_Cancelation+.0000000000000000000001) AS CR,
  40.                   ASSIGNMENT- D_C AS SKIPPED,
  41.                   D_C,
  42.                   user_Cancelation
  43. FROM (
  44.         (SELECT driver_id,
  45.                 count(*) AS ASSIGNMENT
  46.          FROM ride_assignments
  47.          WHERE created_at BETWEEN "{{start_time}}" AND "{{end_time}}"
  48.            AND EXTRACT(hour
  49.                        FROM created_at)>={{
  50.            FROM}}
  51.            AND EXTRACT(hour
  52.                        FROM created_at)<{{TO}}
  53.          GROUP BY driver_id) a
  54.       JOIN
  55.         (SELECT driver_id,
  56.                 max(date(created_at)) AS Last_Active_Date,
  57.                 count(DISTINCT date(created_at)) AS active_days,
  58.                 date_diff(CURRENT_DATE, min(date(created_at)),DAY) AS lifetime,
  59.                 count(CASE
  60.                           WHEN status="COMPLETED" THEN rider_id
  61.                           ELSE NULL
  62.                       END) AS COMPLETED,
  63.                 count(CASE
  64.                           WHEN status="CANCELED" THEN rider_id
  65.                           ELSE NULL
  66.                       END) AS CANCELED,
  67.                 count(CASE
  68.                           WHEN status="CANCELED"
  69.                                AND canceled_by=driver_id THEN rider_id
  70.                           ELSE NULL
  71.                       END)D_C,
  72.                 count(CASE
  73.                           WHEN status="CANCELED"
  74.                                AND canceled_by=rider_id THEN rider_id
  75.                           ELSE NULL
  76.                       END) AS user_Cancelation
  77.          FROM rides
  78.          WHERE ride_type = {{ride_type}}
  79.            AND city_id = 4
  80.            AND created_at BETWEEN "{{start_time}}" AND "{{end_time}}"
  81.            AND EXTRACT(hour
  82.                        FROM created_at)>={{
  83.            FROM}}
  84.            AND EXTRACT(hour
  85.                        FROM created_at)<{{TO}}
  86.          GROUP BY 1) b ON a.driver_id = b.driver_id
  87.       JOIN
  88.         (SELECT id,
  89.                 name,number
  90.          FROM users) c ON b.driver_id=c.id)
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top