API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Dec 12th, 2018
89
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 34.39 KB | None | 0 0
raw download clone embed print report
  1. VIEW: delivery_attempt {
  2.   # DEVELOPER
  3.   # Created BY: Pedro Passarelli
  4.   # Maintained BY: Lucas Pereira
  5.   # Created DATE: 08/11/2018
  6.   # Business Goal: Calculate the service level achievement FOR E-commerce package's delivery attempts.
  7.  
  8.  #VIEW DEFINITION
  9.  label: "Delivery Attempt"
  10.  
  11.  derived_table: {
  12. #   persist_for: "4 hours"
  13. #   distribution_style: all
  14.    sql:
  15.        WITH
  16.            package_entry AS  ( -- add this directly from package view
  17.                               SELECT task.package_id AS package_id,
  18.                                      MIN(CASE  WHEN task.type = 'Confirmação' AND task.status = 'Completa com sucesso' THEN completed_at
  19.                                                ELSE NULL
  20.                                          END) AS date
  21.                                 FROM ${task.SQL_TABLE_NAME} AS task
  22.                             GROUP BY 1),
  23.  
  24.            delivery_attempt AS ( --table that filters only what we consider as a valid attempt (which is based on the type of the task and status of its ack
  25.                                SELECT  dispatch.n1pk_dispatch_id AS id,
  26.                                        dispatch.package_id AS package_id,
  27.                                        dispatch.completed_at AS completed_date,
  28.                                        dispatch.ack_status AS status, --check name
  29.                                        ROW_NUMBER () OVER
  30.                                                          (PARTITION BY package_id
  31.                                                          ORDER BY completed_at ASC) AS number,
  32.                                        CASE  WHEN  ack_status IN ('Destinatário ausente', 'Endereço sem complemento')
  33.                                                    -- AND  number BETWEEN 1 AND 2 --Athena does not support referencing a previously defined field
  34.                                                    AND ROW_NUMBER () OVER
  35.                                                                          (PARTITION BY package_id
  36.                                                                          ORDER BY completed_at ASC) BETWEEN 1 AND 2
  37.                                                    THEN 'Re-envio automático' --packages with unsuccessful 1st or 2nd delivery attempts with these statuses should be automatically re-dispatched
  38.                                              WHEN  ack_status = 'Realizado com sucesso'
  39.                                                    THEN 'Pacote finalizado'
  40.                                              ELSE 'Tratativa'  --packages that have attempts with other statuses or unsucessful 3rd (or more) attempts should wait
  41.                                        END AS next_attempt_action
  42.                                   FROM ${dispatch.SQL_TABLE_NAME} AS dispatch --check name
  43.                                  WHERE ack_status IN ( 'Realizado com sucesso',
  44.                                                        'Endereço errado',
  45.                                                        'Endereço sem complemento',
  46.                                                        'Destinatário ausente',
  47.                                                        'Destinatário não encontrado',
  48.                                                        'Recusado')), --pegar o task_type = d AND waypoint.role = ra
  49.  
  50.            deadline_kickoff AS ( --table that determines which date we should use for calculating the deadline for each one of the package's delivery attempt
  51.                                 SELECT  package_entry.package_id AS package_id,
  52.                                         COALESCE(delivery_attempt.NUMBER, 1) AS delivery_attempt_number,
  53.                                         CASE  WHEN COALESCE(delivery_attempt.NUMBER, 1) = 1--delivery_attempt.number = 1 --Athena does not support referencing a previously defined field
  54.                                                 THEN package_entry.DATE  --1st attempt should always use entry_date
  55.                                               WHEN LAG(delivery_attempt.next_attempt_action, 1) OVER --when its an automatic re-dispatch, we should use the completed_date from the prior attempt
  56.                                                                                               (PARTITION BY package_entry.package_id
  57.                                                                                               ORDER BY delivery_attempt.completed_date) = 'Re-envio automático'
  58.                                                 THEN LAG(delivery_attempt.completed_date, 1) OVER
  59.                                                                                                       (PARTITION BY package_entry.package_id
  60.                                                                                                       ORDER BY delivery_attempt.completed_date)
  61.                                               ELSE NULL --when the package is delivered or it is pending interation from the company, we do not have a deadline for a new attempt
  62.                                         END AS DATE
  63.                                    FROM package_entry
  64.                         LEFT OUTER JOIN delivery_attempt
  65.                                      ON package_entry.package_id = delivery_attempt.package_id
  66.                                GROUP BY package_entry.package_id,
  67.                                         delivery_attempt.NUMBER,
  68.                                         package_entry.DATE,
  69.                                         delivery_attempt.next_attempt_action,
  70.                                         delivery_attempt.completed_date),
  71.  
  72.        city_slo AS (
  73.             SELECT  package.n1pk_package_id AS package_id,
  74.                     destination.city AS city,
  75.                     destination.state AS state, -- According to what we discussed, we can actually do the filtering by state.
  76.                     CASE WHEN package.system_slo = 'D1' THEN
  77.                       (CASE  WHEN package_entry.DATE BETWEEN FROM_ISO8601_TIMESTAMP('2018-11-19') AND FROM_ISO8601_TIMESTAMP('2018-12-31') --Considering the period of 19/11~31/12
  78.                               THEN (CASE WHEN state IN ('SP', 'RJ', 'MG', 'PR')
  79.                                             THEN 'D2'
  80.                                         WHEN state IN ('RS', 'DF', 'GO')
  81.                                             THEN 'D3'
  82.                                         WHEN state IN ('BA', 'CE', 'PE', 'AM')
  83.                                             THEN 'D4'
  84.                                       END)
  85.                             ELSE --Changes the SLO from cities which have different rules, but D1.
  86.                                 (CASE  WHEN state IN ('RS', 'DF', 'GO')
  87.                                         THEN 'D2'
  88.                                         WHEN state IN ('BA', 'CE', 'PE', 'AM')
  89.                                         THEN 'D3'
  90.                                         ELSE 'D1'
  91.                                     END)
  92.                         END)
  93.                     ELSE 'D0' --Redundant information, but it's easier to visualize on this way.
  94.                     END AS agreed_slo
  95.           FROM ${base_package.SQL_TABLE_NAME} AS package
  96.           JOIN loggi.dispatch_destination AS destination
  97.             ON package.fk_destination_id = destination.id
  98.           JOIN package_entry AS package_entry
  99.             ON package_entry.package_id = package.n1pk_package_id
  100.          ),
  101.  
  102.             ex_holiday AS (
  103.                           SELECT  deadline_kickoff.package_id AS package_id,
  104.                                   deadline_kickoff.delivery_attempt_number AS delivery_attempt_number,
  105.                                   deadline_kickoff.DATE AS deadline_kickoff,
  106.                                   city_slo.agreed_slo AS agreed_slo,
  107.                                   city_slo.city AS city,
  108.     -- Using Athena:
  109.                                   (CASE WHEN  DOW(deadline_kickoff.DATE) BETWEEN 1 AND 5           --Calculates the deadline for a first attempt on D0
  110.                                                AND HOUR(deadline_kickoff.DATE) < 20
  111.                                               THEN DATE_ADD('hour', 22, DATE_ADD('day', 0, DATE_TRUNC('day',deadline_kickoff.DATE)))           --if on monday - friday (before 8pm), due date is same day
  112.                                         WHEN  DOW(deadline_kickoff.DATE) BETWEEN 1 AND 4
  113.                                                AND HOUR(deadline_kickoff.DATE) >= 20
  114.                                               THEN DATE_ADD('hour', 22, DATE_ADD('day', 1, DATE_TRUNC('day',deadline_kickoff.DATE)))            --if on monday - thursday (after 8pm), due date is next day
  115.                                         WHEN  (DOW(deadline_kickoff.DATE) = 5
  116.                                                AND HOUR(deadline_kickoff.DATE) >= 20)
  117.                                               OR DOW(deadline_kickoff.DATE) BETWEEN 6 AND 7
  118.                                               THEN DATE_ADD('hour', 22, DATE_ADD('week', 1, DATE_TRUNC('week',deadline_kickoff.DATE)))          --if on saturday - sunday or friday (after 8pm), due date is next Monday
  119.                                         ELSE NULL
  120.                                   END) AS deadline_zero,
  121.                                   (CASE WHEN  DOW(deadline_kickoff.DATE) BETWEEN 1 AND 5           --Calculates the deadline for a first attempt on D1
  122.                                                AND HOUR(deadline_kickoff.DATE) < 13
  123.                                               THEN DATE_ADD('hour', 22, DATE_ADD('day', 0, DATE_TRUNC('day',deadline_kickoff.DATE)))            --if on monday - friday (before 1pm), due date is same day
  124.                                         WHEN  DOW(deadline_kickoff.DATE) BETWEEN 1 AND 4
  125.                                                AND HOUR(deadline_kickoff.DATE) >= 13
  126.                                               THEN DATE_ADD('hour', 22, DATE_ADD('day', 1, DATE_TRUNC('day',deadline_kickoff.DATE)))            --if on monday - thursday (after 1pm), due date is next day
  127.                                         WHEN  (DOW(deadline_kickoff.DATE) = 5
  128.                                                AND HOUR(deadline_kickoff.DATE) >= 13)
  129.                                               OR DOW(deadline_kickoff.DATE) BETWEEN 6 AND 7
  130.                                               THEN DATE_ADD('hour', 22, DATE_ADD('week', 1, DATE_TRUNC('week',deadline_kickoff.DATE)))          --if on saturday - sunday or friday (after 1pm), due date is next Monday
  131.                                         ELSE NULL
  132.                                   END) AS deadline_one,
  133.                                   (CASE WHEN  DOW(deadline_kickoff.DATE) BETWEEN 1 AND 4
  134.                                               THEN DATE_ADD('hour', 22, DATE_ADD('day', 1, DATE_TRUNC('day',deadline_kickoff.DATE)))            --if on monday - thursday, due date is next day
  135.                                         WHEN  DOW(deadline_kickoff.DATE) BETWEEN 5 AND 7
  136.                                               THEN DATE_ADD('hour', 22, DATE_ADD('week', 1, DATE_TRUNC('week',deadline_kickoff.DATE)))          --if on friday - sunday, due date is next Monday
  137.                                         ELSE NULL
  138.                                   END) AS deadline_redispatch,
  139.                                   (CASE
  140.                                       WHEN city_slo.agreed_slo = 'D0'
  141.                                               THEN (CASE  WHEN  DOW(deadline_kickoff.DATE) BETWEEN 1 AND 5           --Calculates the deadline for a first attempt on D0
  142.                                                                  AND HOUR(deadline_kickoff.DATE) < 20
  143.                                                                 THEN DATE_ADD('hour', 22, DATE_ADD('day', 0, DATE_TRUNC('day',deadline_kickoff.DATE)))           --if on monday - friday (before 8pm), due date is same day
  144.                                                           WHEN  DOW(deadline_kickoff.DATE) BETWEEN 1 AND 4
  145.                                                                  AND HOUR(deadline_kickoff.DATE) >= 20
  146.                                                                 THEN DATE_ADD('hour', 22, DATE_ADD('day', 1, DATE_TRUNC('day',deadline_kickoff.DATE)))            --if on monday - thursday (after 8pm), due date is next day
  147.                                                           WHEN  (DOW(deadline_kickoff.DATE) = 5
  148.                                                                  AND HOUR(deadline_kickoff.DATE) >= 20)
  149.                                                                 OR DOW(deadline_kickoff.DATE) BETWEEN 6 AND 7
  150.                                                                 THEN DATE_ADD('hour', 22, DATE_ADD('week', 1, DATE_TRUNC('week',deadline_kickoff.DATE)))          --if on saturday - sunday or friday (after 8pm), due date is next Monday
  151.                                                           ELSE NULL
  152.                                                     END)
  153.  
  154.                                       WHEN city_slo.agreed_slo = 'D1'
  155.                                               THEN (CASE  WHEN  DOW(deadline_kickoff.DATE) BETWEEN 1 AND 5           --Calculates the deadline for a first attempt on D1
  156.                                                                  AND HOUR(deadline_kickoff.DATE) < 13
  157.                                                                 THEN DATE_ADD('hour', 22, DATE_ADD('day', 0, DATE_TRUNC('day',deadline_kickoff.DATE)))            --if on monday - friday (before 1pm), due date is same day
  158.                                                           WHEN  DOW(deadline_kickoff.DATE) BETWEEN 1 AND 4
  159.                                                                  AND HOUR(deadline_kickoff.DATE) >= 13
  160.                                                                 THEN DATE_ADD('hour', 22, DATE_ADD('day', 1, DATE_TRUNC('day',deadline_kickoff.DATE)))            --if on monday - thursday (after 1pm), due date is next day
  161.                                                           WHEN  (DOW(deadline_kickoff.DATE) = 5
  162.                                                                  AND HOUR(deadline_kickoff.DATE) >= 13)
  163.                                                                 OR DOW(deadline_kickoff.DATE) BETWEEN 6 AND 7
  164.                                                                 THEN DATE_ADD('hour', 22, DATE_ADD('week', 1, DATE_TRUNC('week',deadline_kickoff.DATE)))          --if on saturday - sunday or friday (after 1pm), due date is next Monday
  165.                                                           ELSE NULL
  166.                                                     END)
  167.  
  168.                                       WHEN city_slo.agreed_slo = 'D2'
  169.                                               THEN (CASE  WHEN  DOW(deadline_kickoff.DATE) BETWEEN 1 AND 4           --Calculates the deadline for a first attempt on D2
  170.                                                                  AND HOUR(deadline_kickoff.DATE) < 13
  171.                                                                 THEN DATE_ADD('hour', 22, DATE_ADD('day', 1, DATE_TRUNC('day',deadline_kickoff.DATE)))            --if on monday - thursday (before 1pm), due date is next day
  172.                                                           WHEN  DOW(deadline_kickoff.DATE) BETWEEN 1 AND 3
  173.                                                                  AND HOUR(deadline_kickoff.DATE) >= 13
  174.                                                                 THEN DATE_ADD('hour', 22, DATE_ADD('day', 2, DATE_TRUNC('day',deadline_kickoff.DATE)))            --if on sunday - wednesday (after 1pm), due date is 2 days after kickoff
  175.                                                           WHEN  (DOW(deadline_kickoff.DATE) = 4
  176.                                                                  AND HOUR(deadline_kickoff.DATE) >= 13) OR
  177.                                                                 (DOW(deadline_kickoff.DATE) = 5
  178.                                                                  AND HOUR(deadline_kickoff.DATE) < 13)
  179.                                                                 THEN DATE_ADD('hour', 22, DATE_ADD('week', 1, DATE_TRUNC('week',deadline_kickoff.DATE)))          -- if on thursday (after 1pm) or friday (before 1pm), then the deadline will be set off on next Monday.
  180.                                                           WHEN
  181.                                                                 (DOW(deadline_kickoff.DATE) = 5
  182.                                                                  AND HOUR(deadline_kickoff.DATE) >= 13)
  183.                                                               OR DOW(deadline_kickoff.DATE) BETWEEN 6 AND 7
  184.                                                                 THEN DATE_ADD('hour', 22, DATE_ADD('day', 1, (DATE_ADD('week', 1, DATE_TRUNC('week',deadline_kickoff.DATE)))))          --if on saturday - sunday or friday (after 1pm), due date is next Tuesday
  185.                                                           ELSE NULL
  186.                                                       END)
  187.  
  188.                                       WHEN city_slo.agreed_slo = 'D3'
  189.                                               THEN (CASE  WHEN  DOW(deadline_kickoff.DATE) BETWEEN 1 AND 3           --Calculates the deadline for a first attempt on D1
  190.                                                                  AND HOUR(deadline_kickoff.DATE) < 13
  191.                                                                 THEN DATE_ADD('hour', 22, DATE_ADD('day', 2, DATE_TRUNC('day',deadline_kickoff.DATE)))            --if on monday - wedneday (before 1pm), due date is 2 days after kickoff
  192.                                                           WHEN  DOW(deadline_kickoff.DATE) BETWEEN 1 AND 2
  193.                                                                  AND HOUR(deadline_kickoff.DATE) >= 13
  194.                                                                 OR DOW(deadline_kickoff.DATE) = 7
  195.                                                                 THEN DATE_ADD('hour', 22, DATE_ADD('day', 3, DATE_TRUNC('day',deadline_kickoff.DATE)))            --if on sunday or monday - tuesday (after 1pm), due date 3 days after kickoff
  196.                                                           WHEN (DOW(deadline_kickoff.DATE) = 3
  197.                                                                  AND HOUR(deadline_kickoff.DATE) >= 13)
  198.                                                             OR (DOW(deadline_kickoff.DATE) = 4
  199.                                                                  AND HOUR(deadline_kickoff.DATE) < 13)
  200.                                                             THEN DATE_ADD('hour', 22, DATE_ADD('week', 1, DATE_TRUNC('week',deadline_kickoff.DATE)))              --if on wednesday (after 1pm) or thursday (before 1pm), due date is on next Monday.
  201.                                                           WHEN (DOW(deadline_kickoff.DATE) = 4
  202.                                                                  AND HOUR(deadline_kickoff.DATE) >= 13)
  203.                                                             OR (DOW(deadline_kickoff.DATE) = 5
  204.                                                                  AND HOUR(deadline_kickoff.DATE) < 13)
  205.                                                             THEN DATE_ADD('hour', 22, DATE_ADD('day', 1, (DATE_ADD('week', 1, DATE_TRUNC('week',deadline_kickoff.DATE))))) --if on thursday (after 1pm) or friday (before 1pm), due date is next Tuesday
  206.                                                           WHEN  (DOW(deadline_kickoff.DATE) = 5
  207.                                                                  AND HOUR(deadline_kickoff.DATE) >= 13)
  208.                                                                 OR DOW(deadline_kickoff.DATE) = 6
  209.                                                                 THEN DATE_ADD('hour', 22, DATE_ADD('day', 2, (DATE_ADD('week', 1, DATE_TRUNC('week',deadline_kickoff.DATE)))))         --if on saturday or friday (after 1pm), due date is next Wednesday
  210.                                                           ELSE NULL
  211.                                                     END)
  212.  
  213.                                      WHEN city_slo.agreed_slo = 'D4'
  214.                                               THEN (CASE  WHEN  DOW(deadline_kickoff.DATE) BETWEEN 1 AND 2           --Calculates the deadline for a first attempt on D1
  215.                                                                  AND HOUR(deadline_kickoff.DATE) < 13
  216.                                                                 THEN DATE_ADD('hour', 22, DATE_ADD('day', 3, DATE_TRUNC('day',deadline_kickoff.DATE)))            --if on monday - tuesday (before 1pm), due date is 3 days after kickoff
  217.                                                           WHEN  DOW(deadline_kickoff.DATE) = 1
  218.                                                                  AND HOUR(deadline_kickoff.DATE) >= 13
  219.                                                                 OR DOW(deadline_kickoff.DATE) = 7
  220.                                                                 THEN DATE_ADD('hour', 22, DATE_ADD('day', 4, DATE_TRUNC('day',deadline_kickoff.DATE)))            --if on sunday - monday (after 1pm), due date is 4 days after kickoff
  221.                                                           WHEN  DOW(deadline_kickoff.DATE) = 2
  222.                                                                  AND HOUR(deadline_kickoff.DATE) >= 13
  223.                                                                 OR DOW(deadline_kickoff.DATE) = 3
  224.                                                                  AND HOUR(deadline_kickoff.DATE) < 13
  225.                                                                 THEN DATE_ADD('hour', 22, DATE_ADD('week', 1, DATE_TRUNC('week',deadline_kickoff.DATE)))            --if on tuesday (after 1pm) or wednesday (before 1pm), due date is next Monday.
  226.                                                           WHEN DOW(deadline_kickoff.DATE) = 3
  227.                                                                  AND HOUR(deadline_kickoff.DATE) >= 13
  228.                                                                 OR DOW(deadline_kickoff.DATE) = 4
  229.                                                                  AND HOUR(deadline_kickoff.DATE) < 13
  230.                                                                 THEN DATE_ADD('hour', 22, DATE_ADD('day', 1, (DATE_ADD('week', 1, DATE_TRUNC('week',deadline_kickoff.DATE))))) --if on wednesday (after 1pm) or thursday (before 1pm), due date is next Tuesday.
  231.                                                           WHEN DOW(deadline_kickoff.DATE) = 4
  232.                                                                  AND HOUR(deadline_kickoff.DATE) >= 13
  233.                                                                 OR DOW(deadline_kickoff.DATE) = 5
  234.                                                                  AND HOUR(deadline_kickoff.DATE) < 13
  235.                                                                 THEN DATE_ADD('hour', 22, DATE_ADD('day', 2, (DATE_ADD('week', 1, DATE_TRUNC('week',deadline_kickoff.DATE))))) --if on thrusday (after 1pm) or friday (before 1pm), due date is next Wednesday
  236.                                                           WHEN  (DOW(deadline_kickoff.DATE) = 5
  237.                                                                  AND HOUR(deadline_kickoff.DATE) >= 13)
  238.                                                                 OR DOW(deadline_kickoff.DATE) = 6
  239.                                                                 THEN DATE_ADD('hour', 22, DATE_ADD('day', 3, (DATE_ADD('week', 1, DATE_TRUNC('week',deadline_kickoff.DATE)))))          --if on saturday or friday (after 1pm), due date is next Thursday
  240.                                                     END)
  241.                                       WHEN  delivery_attempt_number > 1
  242.                                             THEN (CASE  WHEN  DOW(deadline_kickoff.DATE) BETWEEN 1 AND 4
  243.                                                               THEN DATE_ADD('hour', 22, DATE_ADD('day', 1, DATE_TRUNC('day',deadline_kickoff.DATE)))            --if on monday - thursday, due date is next day
  244.                                                         WHEN  DOW(deadline_kickoff.DATE) BETWEEN 5 AND 7
  245.                                                               THEN DATE_ADD('hour', 22, DATE_ADD('week', 1, DATE_TRUNC('week',deadline_kickoff.DATE)))          --if on friday - sunday, due date is next Monday
  246.                                                         ELSE NULL
  247.                                                     END)
  248.                                           END) AS deadline
  249.                              FROM deadline_kickoff
  250.                              JOIN city_slo AS city_slo
  251.                                ON deadline_kickoff.package_id = city_slo.package_id
  252.                              JOIN ${base_package.SQL_TABLE_NAME} AS package
  253.                                ON deadline_kickoff.package_id = package.n1pk_package_id),
  254.  
  255. --As Holidays were inserted on Athena database, then we can try to use it!
  256.   holiday_final AS (
  257.               SELECT ex_holiday.package_id AS package_id,
  258.                      ex_holiday.delivery_attempt_number AS delivery_attempt_number, --adding to make sure every attempt has its own deadline
  259.                      CASE WHEN DATE_TRUNC('day', FROM_ISO8601_TIMESTAMP(holidays.holiday_date) AT TIME ZONE 'America/Sao_Paulo')
  260.                       BETWEEN DATE_ADD('DAY', -1, DATE_TRUNC('day',ex_holiday.deadline_kickoff)) AND DATE_ADD('DAY', -1, DATE_TRUNC('day',ex_holiday.deadline)) --Using the fact that the differences betwwen UTC and Sao Paulo timezone makes the equivalence to happen in the day before the deadline
  261.                           THEN (CASE WHEN DOW(ex_holiday.deadline) = 5 --When it's Friday and a holiday, so the deadline must be delayed to Monday
  262.                                 THEN DATE_ADD('week', 1, DATE_TRUNC('week', ex_holiday.deadline))--Just copying the code - dunno if it's correct
  263.                                 ELSE  DATE_ADD('day', 1, DATE_TRUNC('day',ex_holiday.deadline))
  264.                                 END) --Just copying -> ELSE: Just add 1 day more for the deadline
  265.                           ELSE ex_holiday.deadline
  266.                           END AS deadline_holiday
  267.                     FROM ex_holiday
  268.                     LEFT JOIN holidays ON DATE_TRUNC('day', FROM_ISO8601_TIMESTAMP(holidays.holiday_date) AT TIME ZONE 'America/Sao_Paulo') = DATE_ADD('DAY', -1, DATE_TRUNC('day',ex_holiday.deadline))
  269.                     )
  270.  
  271.         SELECT  ex_holiday.package_id AS n2pk_package_id,
  272.                 ex_holiday.delivery_attempt_number AS n2pk_delivery_attempt_number,
  273.                 package_entry.DATE AS package_entry_date,
  274.                 ex_holiday.agreed_slo AS agreed_slo,
  275.                 deadline_kickoff.DATE AS deadline_kickoff,
  276.                 ex_holiday.deadline_zero AS deadline_zero,
  277.                 ex_holiday.deadline_one AS deadline_one,
  278.                 ex_holiday.deadline_redispatch AS deadline_redispatch,
  279.                 --ex_holiday.deadline AS deadline,
  280.                 holiday_final.deadline_holiday AS deadline,
  281.                 delivery_attempt.id AS task_id,
  282.                 delivery_attempt.completed_date AS completed_at,
  283.                 CASE  WHEN  delivery_attempt.completed_date > holiday_final.deadline_holiday--ex_holiday.deadline
  284.                             OR  (delivery_attempt.completed_date IS NULL
  285.                                   AND holiday_final.deadline_holiday /*ex_holiday.deadline*/ < NOW() AT TIME ZONE 'America/Sao_Paulo')
  286.                         THEN 'Fora do prazo'
  287.                       WHEN  delivery_attempt.completed_date <= holiday_final.deadline_holiday--ex_holiday.deadline
  288.                         THEN 'Dentro do prazo'
  289.                       ELSE NULL
  290.                 END AS task_sla_achievement,
  291.                 ex_holiday.city AS city,
  292.                 delivery_attempt.STATUS AS STATUS,
  293.                 package.STATUS AS package_status
  294.            FROM ex_holiday
  295.            JOIN package_entry
  296.              ON ex_holiday.package_id = package_entry.package_id
  297.            JOIN deadline_kickoff
  298.              ON ex_holiday.package_id = deadline_kickoff.package_id AND ex_holiday.delivery_attempt_number = deadline_kickoff.delivery_attempt_number
  299.            JOIN ${base_package.SQL_TABLE_NAME} AS package
  300.              ON ex_holiday.package_id = package.n1pk_package_id
  301. LEFT OUTER JOIN delivery_attempt
  302.              ON ex_holiday.package_id = delivery_attempt.package_id AND ex_holiday.delivery_attempt_number = delivery_attempt.NUMBER
  303.            JOIN holiday_final
  304.              ON holiday_final.package_id = ex_holiday.package_id AND ex_holiday.delivery_attempt_number = holiday_final.delivery_attempt_number --Adding delivery_attempt_number to make sure every attempt has its own deadline
  305.                   ;;
  306.   }
  307.  
  308.  
  309. #DIMENSIONS------------------------------------------------------------------
  310.  
  311.   dimension: n2pk_package_id {
  312.     # Author: Pedro Passarelli
  313.     # DATE: 2018-11-08
  314.     # Output example:
  315.     # Notes:
  316.     # LAST Modified BY: Pedro
  317.     label: "Package ID"
  318.     description: "Shows the identification number of the package."
  319.     TYPE: NUMBER
  320.     SQL: ${TABLE}.n2pk_package_id ;;
  321.   }
  322.  
  323.   dimension: n2pk_delivery_attempt_number {
  324.     # Author: Pedro Passarelli
  325.     # DATE: 2018-11-08
  326.     # Output example:
  327.     # Notes:
  328.     # LAST Modified BY: Pedro
  329.     label: "Number"
  330.     description: "Shows the number of this delivery attempt in the package lifecycle"
  331.     TYPE: NUMBER
  332.     SQL: ${TABLE}.n2pk_delivery_attempt_number ;;
  333.   }
  334.  
  335.   dimension: agreed_slo {
  336.     # Author: Pedro Passarelli
  337.     # DATE: 2018-11-08
  338.     # Output example: D2
  339.     # Notes: Differs FROM base_package.system_slo
  340.     # LAST Modified BY: Lucas
  341.     label: "Agreed SLO"
  342.     view_label: "Package"
  343.     description: "Shows the SLO (Service Level Objective) agreed with companies, considering rules for each city."
  344.     TYPE: string
  345.     SQL: ${TABLE}.agreed_slo ;;
  346.   }
  347.  
  348.   dimension: city {
  349.     # Author: Lucas Pereira
  350.     # DATE: 2018-11-26
  351.     # Output example:
  352.     # Notes: This dimension IS used TO filtering purposes ON the Explore.
  353.     # LAST Modified BY: Lucas
  354.     label: "City"
  355.     description: "Shows the city which the delivery is taken place."
  356.     TYPE: string
  357.     SQL: ${TABLE}.city ;;
  358.     suggestions: [
  359.       "São Paulo",
  360.       "Rio de Janeiro",
  361.       "Belo Horizonte",
  362.       "Curitiba",
  363.       "Campinas",
  364.       "São José dos Campos",
  365.       "Ribeirão Preto",
  366.       "Santos",
  367.       "Porto Alegre",
  368.       "Brasília",
  369.       "Goiânia",
  370.       "Salvador",
  371.       "Fortaleza",
  372.       "Recife",
  373.       "Manaus"
  374.       ]
  375.   }
  376.  
  377.   dimension_group: completed {
  378.     # Author: Pedro Passarelli
  379.     # DATE: 2018-11-08
  380.     # Output example:
  381.     # Notes:
  382.     # LAST Modified BY: Pedro
  383.     label: "Completed"
  384.     description: "Date in which the delivery attempt is considered completed. This date is compared with the deadline to determine if the attempt is within SLA or not"
  385.     TYPE: TIME
  386.     timeframes: [TIME, DATE, week, MONTH, YEAR, hour_of_day, time_of_day, day_of_week, raw]
  387.     datatype: TIMESTAMP
  388.     SQL: ${TABLE}.completed_at ;;
  389.   }
  390.  
  391.   dimension_group: deadline {
  392.     # Author: Pedro Passarelli
  393.     # DATE: 2018-11-08
  394.     # Output example:
  395.     # Notes:
  396.     # LAST Modified BY: Pedro
  397.     label: "Deadline"
  398.     description: "Limit date for a delivery attempt to happen in order for being considered within SLA"
  399.     TYPE: TIME
  400.     timeframes: [TIME, DATE, week, MONTH, YEAR, hour_of_day, time_of_day, day_of_week, raw]
  401.     datatype: TIMESTAMP
  402.     SQL: ${TABLE}.deadline ;;
  403.   }
  404.  
  405.   dimension_group: deadline_kickoff {
  406.     # Author: Pedro Passarelli
  407.     # DATE: 2018-11-08
  408.     # Output example:
  409.     # Notes:
  410.     # LAST Modified BY: Pedro
  411.     label: "Deadline Kickoff"
  412.     description: "Date in which the deadline for a delivery attempt starts running"
  413.     TYPE: TIME
  414.     timeframes: [TIME, DATE, week, MONTH, YEAR, hour_of_day, time_of_day, day_of_week, raw]
  415.     datatype: TIMESTAMP
  416.     SQL: ${TABLE}.deadline_kickoff ;;
  417.   }
  418.  
  419.   dimension_group: package_entry {
  420.     # Author: Pedro Passarelli
  421.     # DATE: 2018-11-08
  422.     # Output example:
  423.     # Notes:
  424.     # LAST Modified BY: Pedro
  425.     label: "Package Entry"
  426.     description: "Date in which the package is considered Loggi's responsability. Considered for determining first attempt deadline date"
  427.     TYPE: TIME
  428.     timeframes: [TIME, DATE, week, MONTH, YEAR, hour_of_day, time_of_day, day_of_week, raw]
  429.     datatype: TIMESTAMP
  430.     SQL: ${TABLE}.package_entry_date ;;
  431.   }
  432.  
  433.   dimension: STATUS {
  434.     # Author: Pedro Passarelli
  435.     # DATE: 2018-11-08
  436.     # Output example:
  437.     # Notes:
  438.     # LAST Modified BY: Pedro
  439.     label: "Delivery Attempt Status"
  440.     description: "Status of the attempt, i.e 'Realizado com sucesso', 'Endereço errado', etc"
  441.     TYPE: string
  442.     SQL: ${TABLE}.STATUS ;;
  443.   }
  444.  
  445.   dimension: task_id {
  446.     # Author: Pedro Passarelli
  447.     # DATE: 2018-11-08
  448.     # Output example:
  449.     # Notes: Although we shoud NOT have duplicates OF this FIELD IS this VIEW, this IS NOT the PRIMARY KEY AS there should be LINES IN which this FIELD IS NULL
  450.     # LAST Modified BY: Pedro
  451.     label: "Task ID"
  452.     description: "Shows the identification number of the task that corresponds to the delivery attempt."
  453.     TYPE: NUMBER
  454.     SQL: ${TABLE}.n2pk_package_id ;;
  455.   }
  456.  
  457.   dimension: task_sla_achievement {
  458.     # Author: Pedro Passarelli
  459.     # DATE: 2018-11-08
  460.     # Output example:
  461.     # Notes:
  462.     # LAST Modified BY: Pedro
  463.     label: "Delivery Attempt SLA"
  464.     description: "Describes whether a delivery attempt happened within its deadline or not. Possible values are 'Dentro do prazo', 'Fora do prazo' or NULL (when there is no deadline defined)"
  465.     TYPE: string
  466.     SQL: ${TABLE}.task_sla_achievement ;;
  467.   }
  468.  
  469. #FIELD SETS------------------------------------------------------------------
  470.  
  471.   SET: business_user_dimensions {
  472.     FIELDS: [
  473.       n2pk_delivery_attempt_number,
  474.       agreed_slo,
  475.       city,
  476.       deadline_kickoff_date,
  477.       deadline_kickoff_day_of_week,
  478.       deadline_kickoff_hour_of_day,
  479.       deadline_kickoff_month,
  480.       deadline_kickoff_raw,
  481.       deadline_kickoff_time,
  482.       deadline_kickoff_time_of_day,
  483.       deadline_kickoff_week,
  484.       deadline_kickoff_year,
  485.       deadline_date,
  486.       deadline_day_of_week,
  487.       deadline_hour_of_day,
  488.       deadline_month,
  489.       deadline_raw,
  490.       deadline_time,
  491.       deadline_time_of_day,
  492.       deadline_week,
  493.       deadline_year
  494.       ]
  495.   }
  496.  
  497.  
  498. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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!