Facebook

60 Dakikalık Interval safest cryptocurrency exchange

From TG, 1 Month ago, written in SQL.
URL https://pastebin.pl/view/144b7db9
Embed
Download Paste or View Raw
Hits: 149
  1. WITH time_interval AS (
  2.   SELECT
  3.     EXTRACT(HOUR FROM interval_timestamp) AS HOUR,
  4.     DATE(interval_timestamp) AS interval_date,
  5.     CASE
  6.       WHEN(EXTRACT(TIME FROM interval_timestamp)>='00:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'01:00:00') THEN '00:00-01:00'
  7.       WHEN(EXTRACT(TIME FROM interval_timestamp)>='01:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'02:00:00') THEN '01:00-02:00'
  8.       WHEN(EXTRACT(TIME FROM interval_timestamp)>='02:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'03:00:00') THEN '02:00-03:00'
  9.       WHEN(EXTRACT(TIME FROM interval_timestamp)>='03:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'04:00:00') THEN '03:00-04:00'
  10.       WHEN(EXTRACT(TIME FROM interval_timestamp)>='04:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'05:00:00') THEN '04:00-05:00'
  11.       WHEN(EXTRACT(TIME FROM interval_timestamp)>='05:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'06:00:00') THEN '05:00-06:00'
  12.       WHEN(EXTRACT(TIME FROM interval_timestamp)>='06:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'07:00:00') THEN '06:00-07:00'
  13.       WHEN(EXTRACT(TIME FROM interval_timestamp)>='07:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'08:00:00') THEN '07:00-08:00'
  14.       WHEN(EXTRACT(TIME FROM interval_timestamp)>='08:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'09:00:00') THEN '08:00-09:00'
  15.       WHEN(EXTRACT(TIME FROM interval_timestamp)>='09:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'10:00:00') THEN '09:00-10:00'
  16.       WHEN(EXTRACT(TIME FROM interval_timestamp)>='10:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'11:00:00') THEN '10:00-11:00'
  17.       WHEN(EXTRACT(TIME FROM interval_timestamp)>='11:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'12:00:00') THEN '11:00-12:00'
  18.       WHEN(EXTRACT(TIME FROM interval_timestamp)>='12:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'13:00:00') THEN '12:00-13:00'
  19.       WHEN(EXTRACT(TIME FROM interval_timestamp)>='13:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'14:00:00') THEN '13:00-14:00'
  20.       WHEN(EXTRACT(TIME FROM interval_timestamp)>='14:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'15:00:00') THEN '14:00-15:00'
  21.       WHEN(EXTRACT(TIME FROM interval_timestamp)>='15:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'16:00:00') THEN '15:00-16:00'
  22.       WHEN(EXTRACT(TIME FROM interval_timestamp)>='16:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'17:00:00') THEN '16:00-17:00'
  23.       WHEN(EXTRACT(TIME FROM interval_timestamp)>='17:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'18:00:00') THEN '17:00-18:00'
  24.       WHEN(EXTRACT(TIME FROM interval_timestamp)>='18:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'19:00:00') THEN '18:00-19:00'
  25.       WHEN(EXTRACT(TIME FROM interval_timestamp)>='19:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'20:00:00') THEN '19:00-20:00'
  26.       WHEN(EXTRACT(TIME FROM interval_timestamp)>='20:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'21:00:00') THEN '20:00-21:00'
  27.       WHEN(EXTRACT(TIME FROM interval_timestamp)>='21:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'22:00:00') THEN '21:00-22:00'
  28.       WHEN(EXTRACT(TIME FROM interval_timestamp)>='22:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'23:00:00') THEN '22:00-23:00'
  29.       WHEN(EXTRACT(TIME FROM interval_timestamp)>='23:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<='23:59:59') THEN '23:00-00:00'
  30.     END AS call_interval
  31.   FROM
  32.     `telefoni-develop-9.telefoni_develop_9_ey_r_appspot_com.INTERVAL_SAATLIK`
  33.   WHERE
  34.     interval_timestamp BETWEEN '2023-12-11 01:00:00' AND '2023-12-20 23:00:00'
  35. ),
  36. asl AS (
  37.   SELECT
  38.     activecallkey AS activecallkey,
  39.     SUM(duration) AS total_duration
  40.   FROM
  41.     `telefoni-develop-9.develop_9_alo_tech_com.AGENT_STATUS_LOG`
  42.   WHERE
  43.     START BETWEEN '2023-12-11 01:00:00' AND '2023-12-20 23:00:00'
  44.     AND
  45.     STATUS IN ('talking','ringing','hold','aftercallwork','wrapup')
  46.     AND
  47.     namespace ='develop-9.alo-tech.com'
  48.   GROUP BY
  49.     activecallkey
  50. ),
  51. cdr AS (
  52.   SELECT
  53.     activecallkey AS activecallkey,
  54.     answered AS answered,
  55.     namespace AS namespace,
  56.     calldate AS calldate,
  57.     DATE(calldate) AS call_date,
  58.     inbound AS inbound,
  59.     duration AS duration,
  60.     queue.wrapupduration AS queue_wrapupduration,
  61.     queue.key AS queue_key,
  62.     CASE
  63.       WHEN(EXTRACT(TIME FROM calldate)>='00:00:00') AND (EXTRACT(TIME FROM calldate)<'01:00:00') THEN '00:00-01:00'
  64.       WHEN(EXTRACT(TIME FROM calldate)>='01:00:00') AND (EXTRACT(TIME FROM calldate)<'02:00:00') THEN '01:00-02:00'
  65.       WHEN(EXTRACT(TIME FROM calldate)>='02:00:00') AND (EXTRACT(TIME FROM calldate)<'03:00:00') THEN '02:00-03:00'
  66.       WHEN(EXTRACT(TIME FROM calldate)>='03:00:00') AND (EXTRACT(TIME FROM calldate)<'04:00:00') THEN '03:00-04:00'
  67.       WHEN(EXTRACT(TIME FROM calldate)>='04:00:00') AND (EXTRACT(TIME FROM calldate)<'05:00:00') THEN '04:00-05:00'
  68.       WHEN(EXTRACT(TIME FROM calldate)>='05:00:00') AND (EXTRACT(TIME FROM calldate)<'06:00:00') THEN '05:00-06:00'
  69.       WHEN(EXTRACT(TIME FROM calldate)>='06:00:00') AND (EXTRACT(TIME FROM calldate)<'07:00:00') THEN '06:00-07:00'
  70.       WHEN(EXTRACT(TIME FROM calldate)>='07:00:00') AND (EXTRACT(TIME FROM calldate)<'08:00:00') THEN '07:00-08:00'
  71.       WHEN(EXTRACT(TIME FROM calldate)>='08:00:00') AND (EXTRACT(TIME FROM calldate)<'09:00:00') THEN '08:00-09:00'
  72.       WHEN(EXTRACT(TIME FROM calldate)>='09:00:00') AND (EXTRACT(TIME FROM calldate)<'10:00:00') THEN '09:00-10:00'
  73.       WHEN(EXTRACT(TIME FROM calldate)>='10:00:00') AND (EXTRACT(TIME FROM calldate)<'11:00:00') THEN '10:00-11:00'
  74.       WHEN(EXTRACT(TIME FROM calldate)>='11:00:00') AND (EXTRACT(TIME FROM calldate)<'12:00:00') THEN '11:00-12:00'
  75.       WHEN(EXTRACT(TIME FROM calldate)>='12:00:00') AND (EXTRACT(TIME FROM calldate)<'13:00:00') THEN '12:00-13:00'
  76.       WHEN(EXTRACT(TIME FROM calldate)>='13:00:00') AND (EXTRACT(TIME FROM calldate)<'14:00:00') THEN '13:00-14:00'
  77.       WHEN(EXTRACT(TIME FROM calldate)>='14:00:00') AND (EXTRACT(TIME FROM calldate)<'15:00:00') THEN '14:00-15:00'
  78.       WHEN(EXTRACT(TIME FROM calldate)>='15:00:00') AND (EXTRACT(TIME FROM calldate)<'16:00:00') THEN '15:00-16:00'
  79.       WHEN(EXTRACT(TIME FROM calldate)>='16:00:00') AND (EXTRACT(TIME FROM calldate)<'17:00:00') THEN '16:00-17:00'
  80.       WHEN(EXTRACT(TIME FROM calldate)>='17:00:00') AND (EXTRACT(TIME FROM calldate)<'18:00:00') THEN '17:00-18:00'
  81.       WHEN(EXTRACT(TIME FROM calldate)>='18:00:00') AND (EXTRACT(TIME FROM calldate)<'19:00:00') THEN '18:00-19:00'
  82.       WHEN(EXTRACT(TIME FROM calldate)>='19:00:00') AND (EXTRACT(TIME FROM calldate)<'20:00:00') THEN '19:00-20:00'
  83.       WHEN(EXTRACT(TIME FROM calldate)>='20:00:00') AND (EXTRACT(TIME FROM calldate)<'21:00:00') THEN '20:00-21:00'
  84.       WHEN(EXTRACT(TIME FROM calldate)>='21:00:00') AND (EXTRACT(TIME FROM calldate)<'22:00:00') THEN '21:00-22:00'
  85.       WHEN(EXTRACT(TIME FROM calldate)>='22:00:00') AND (EXTRACT(TIME FROM calldate)<'23:00:00') THEN '22:00-23:00'
  86.       WHEN(EXTRACT(TIME FROM calldate)>='23:00:00') AND (EXTRACT(TIME FROM calldate)<='23:59:59') THEN '23:00-00:00'
  87.     END AS call_interval
  88.   FROM
  89.     `telefoni-develop-9.develop_9_alo_tech_com.CDR`
  90.   WHERE
  91.     calldate BETWEEN '2023-12-11 01:00:00' AND '2023-12-20 23:00:00'
  92.       AND
  93.           queue.key IN ("ahRofnRlbGVmb25pLWRldmVsb3AtOXINCxIFUXVldWUiAjEwDKIBFmRldmVsb3AtOS5hbG8tdGVjaC5jb20","ahRofnRlbGVmb25pLWRldmVsb3AtOXISCxIFUXVldWUYgICAmIbjiQkMogEWZGV2ZWxvcC05LmFsby10ZWNoLmNvbQ","ahRofnRlbGVmb25pLWRldmVsb3AtOXISCxIFUXVldWUYgICAhLrJngoMogEWZGV2ZWxvcC05LmFsby10ZWNoLmNvbQ"
  94.     )
  95.       AND
  96.       namespace ='develop-9.alo-tech.com'
  97.     AND
  98.     activecallkey IS NOT NULL
  99. ),
  100. report_level AS (
  101.   SELECT
  102.     time_interval.interval_date AS interval_date,
  103.     time_interval.call_interval AS call_interval,
  104.     (COUNT(DISTINCT CASE WHEN inbound=TRUE AND answered = TRUE THEN cdr.activecallkey ELSE NULL END) + COUNT(DISTINCT CASE WHEN inbound=FALSE THEN cdr.activecallkey ELSE NULL END)) AS total_answered_call_count,
  105.     SUM(asl.total_duration) AS total_status_duration,
  106.     SUM(CASE WHEN inbound=TRUE AND answered=TRUE THEN asl.total_duration ELSE 0 END) + SUM(CASE WHEN inbound=TRUE AND answered=TRUE THEN cdr.queue_wrapupduration ELSE 0 END) AS total_inbound_aht_duration,
  107.     SUM(CASE WHEN inbound=FALSE THEN asl.total_duration ELSE 0 END) + SUM(CASE WHEN inbound=FALSE THEN cdr.queue_wrapupduration ELSE 0 END) AS total_outbound_aht_duration,
  108.     COUNT(DISTINCT CASE WHEN inbound=TRUE AND answered=TRUE THEN cdr.activecallkey END) AS total_inbound_call_count,
  109.     COUNT(DISTINCT CASE WHEN inbound=FALSE THEN cdr.activecallkey END) AS total_outbound_call_count
  110.   FROM
  111.     time_interval
  112.   LEFT JOIN
  113.     cdr
  114.   ON
  115.     time_interval.interval_date = cdr.call_date
  116.     AND
  117.     time_interval.call_interval = cdr.call_interval
  118.   LEFT JOIN
  119.     asl
  120.   ON
  121.     cdr.activecallkey = asl.activecallkey
  122.   GROUP BY
  123.     interval_date,
  124.     call_interval
  125. )
  126. SELECT
  127.   interval_date AS interval_date,
  128.   call_interval AS call_interval,
  129.   total_answered_call_count AS total_answered_call_count,
  130.   total_inbound_call_count AS total_inbound_call_count,
  131.   total_outbound_call_count AS total_outbound_call_count,
  132.   IFNULL(ROUND(total_inbound_aht_duration,2),0) AS total_inbound_aht_duration,
  133.   IFNULL(ROUND(total_outbound_aht_duration,2),0)  AS total_outbound_aht_duration,
  134.   IFNULL(ROUND(SAFE_DIVIDE((total_inbound_aht_duration + total_outbound_aht_duration),(total_inbound_call_count + total_outbound_call_count)),2),0) AS total_aht,
  135.   IFNULL(ROUND(SAFE_DIVIDE(total_inbound_aht_duration,total_inbound_call_count),2),0) AS inbound_aht,
  136.   IFNULL(ROUND(SAFE_DIVIDE(total_outbound_aht_duration,total_outbound_call_count),2),0) AS outbound_aht
  137. FROM
  138.   report_level
  139. ORDER BY
  140.   interval_date DESC,
  141.   call_interval ASC

Reply to "60 Dakikalık Interval"