SHARE
TWEET

Untitled

a guest Nov 14th, 2019 99 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. with order_order as (
  2.     select *
  3.     from
  4.         (
  5.             select f.*, row_number() over(partition by id order by modified_at desc) as rn
  6.             from pathao_food.order_order as f
  7.             where modified_at >= '2019-01-01'
  8.         )
  9.     where rn = 1
  10. )
  11.  
  12. Select
  13.     * ,
  14.     safe_divide(delivered_order* 100 , Assigned_order) as CR
  15. From    
  16.    ( select
  17.     Extract(Year from a.ED) as year,
  18.     Extract(Month from a.ED) as month ,
  19.     Count(a.driver_id) as No_of_Riders,
  20.     Sum(a.Assigned_order) as Assigned_order,
  21.     Sum(b.accepted_order) as accepted_order,
  22.     Sum(b.delivered_order) as delivered_order
  23.    
  24. from (
  25. select   driver_id, ED, count(tracking_id) Assigned_order
  26.     from
  27.         (
  28.         select date(timestamp_add(order_order.created_at, interval 6 hour)) ED, tracking_id, driver_id
  29.         from order_order
  30.         CROSS JOIN UNNEST(split(order_order.drivers_tried, ',')) as driver_id
  31.         where
  32.         date(timestamp_add(order_order.created_at, interval 6 hour)) >= '2019-01-01'
  33.         and order_order.city = 1
  34.         )a
  35.     where a.driver_id <> ''
  36.     group by 1,2)a
  37. left join
  38.  
  39. (select
  40.     date(timestamp_add(order_order.created_at, interval 6 hour)) ED,
  41.     driver, count(tracking_id) accepted_order,
  42.     count(case when status = 'DELIVERED' then tracking_id end) delivered_order
  43.     from order_order
  44.     group by 1,2)b
  45.    
  46.     on a.ED=b.ed and cast(a.driver_id as numeric) = cast(b.driver as numeric)
  47.    
  48. Group by 1 , 2
  49. Order By 1,2)
  50. Order By 1,2
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top