NPS logic

gistfile1.txt

insert_nps_summary = """
          INSERT
              INTO star_summary.f_nps
            SELECT webshop_language,
                   order_id,
                   COALESCE(q_nps_score, {v_int_to_replace_null})                      AS nps_score,
                   COALESCE(q_onlineshop_and_sortment_score, {v_int_to_replace_null})  AS onlineshop_and_sortment_score,
                   COALESCE(q_order_and_payment_score, {v_int_to_replace_null})        AS order_and_payment_score,
                   COALESCE(q_delivery_score, {v_int_to_replace_null})                 AS delivery_score,
                   COALESCE(q_article_quality_score, {v_int_to_replace_null})          AS article_quality_score,
                   COALESCE(q_customer_service_score, {v_int_to_replace_null})         AS customer_service_score,
                   COALESCE(archive_date, '{v_date_to_replace_null}')                  AS archive_date,
                   order_delivery_week,
                   COALESCE(cancellation_status, '{v_str_to_replace_null}')            AS cancellation_status,
                   zipcode,
                   SUM(CASE
                           WHEN ((nps_results.q_nps_score = 9) OR (nps_results.q_nps_score = 10))
                               THEN 1
                           ELSE 0 END)             AS promoters,
                   SUM(CASE
                           WHEN ((nps_results.q_nps_score >= 0) AND (nps_results.q_nps_score <= 6))
                               THEN 1
                           ELSE 0 END)             AS detractors,
                   SUM(CASE
                           WHEN (nps_results.q_nps_score IS NOT NULL)
                               THEN 1
                           ELSE 0 END)             AS total_order_id,
                   SUM(CASE
                           WHEN (nps_results.q_nps_score = 0)
                               THEN 1
                           ELSE 0 END)             AS total_0,
                   SUM(CASE
                           WHEN ((nps_results.q_nps_score >= 8) AND (nps_results.q_nps_score <= 10))
                               THEN 1
                           ELSE 0 END)             AS happy_homes
              FROM public.nps_results
             GROUP BY webshop_language,
                      order_id,
                      q_nps_score,
                      q_onlineshop_and_sortment_score,
                      q_order_and_payment_score,
                      q_delivery_score,
                      q_article_quality_score,
                      q_customer_service_score,
                      archive_date,
                      order_delivery_week,
                      cancellation_status,
                      zipcode;""".format(v_int_to_replace_null=v_int_to_replace_null,
                                         v_str_to_replace_null=v_str_to_replace_null,
                                         v_date_to_replace_null=v_date_to_replace_null)

sql_nps_summary = SQL.with_transaction([create_nps_summary,
                                        truncate_nps_summary,
                                        insert_nps_summary])

sql_exchange_logic = """
        DROP TABLE IF EXISTS star_intermediate.exchange_logic;
        CREATE TABLE star_intermediate.exchange_logic AS
        SELECT return.order_number,
               return.order_line
          FROM (SELECT order_number, order_code, order_line
                  FROM star_summary.f_orders
                 WHERE order_type = '{order_return}') return
               INNER JOIN
               (SELECT order_number, order_code, order_line, order_code_parent
                  FROM star_summary.f_orders
                 WHERE order_type = '{order_exchange}') exchange
               ON return.order_code = exchange.order_code_parent
                   AND return.order_line = exchange.order_line
         GROUP BY return.order_number,
                  return.order_line;""".format(order_exchange=order_exchange,
                                               order_return=order_return)

sql_last_claims = """
        DROP TABLE IF EXISTS star_intermediate.claims_last_solution;
        CREATE TABLE star_intermediate.claims_last_solution AS
        SELECT all_claims.order_number,
               all_claims.order_line,
               all_claims.order_date,
               CASE
                   WHEN er.order_number IS NOT NULL AND er.order_line IS NOT NULL
                       AND all_claims.order_type = '{order_return}'
                       THEN '{order_exchange}'
                   ELSE all_claims.order_type END
          FROM (SELECT order_number,
                       order_line,
                       order_date,
                       order_type
                  FROM (
                           SELECT order_number,
                                  order_line,
                                  order_date,
                                  order_type,
                                  row_number() OVER (PARTITION BY order_line,
                                      order_number ORDER BY document_creation_time,
                                      order_code DESC ) rnk
                             FROM star_summary.f_orders
                            WHERE order_type IN ({order_type_string})
                       ) a
                 WHERE rnk = 1) all_claims
               LEFT JOIN star_intermediate.exchange_logic er
               ON all_claims.order_number = er.order_number
                   AND all_claims.order_line = er.order_line;
        """.format(order_type_string=order_type_string,
                   order_exchange=order_exchange,
                   order_return=order_return
                   )

sql_claims_count = """
        DROP TABLE IF EXISTS star_intermediate.claims_count;
        CREATE TABLE star_intermediate.claims_count AS
        SELECT order_number,
               order_line,
               COUNT(*) claim_solutions_count
          FROM star_summary.f_orders
         WHERE order_type IN
               ({order_type_string})
         GROUP BY order_number,
                  order_line;
        """.format(order_type_string=order_type_string)

intermediate_ticket_stats_max = f"""
        DROP TABLE IF EXISTS {intermediate_schema}.f_ticket_stats_max;
        CREATE TABLE {intermediate_schema}.f_ticket_stats_max
            DISTKEY ( order_code )
        AS
        SELECT ticket_queue_skey,
               order_code
          FROM (
                   SELECT ticket_queue_skey,
                          order_code,
                          row_number() OVER (PARTITION BY order_code
                              ORDER BY last_updated_at_skey,
                                  first_event_time_skey DESC) rnk
                     FROM {analytical_schema}.f_ticket_stats
               ) ticket_queue_max
         WHERE rnk = 1;"""


sql_delay_notify_count = f"""
        DROP TABLE IF EXISTS star_intermediate.delay_notify_count;
        CREATE TABLE star_intermediate.delay_notify_count AS
        SELECT items_line_no,
               order_id,
               count(*) delay_notify_count
          FROM public.notification_data
         WHERE mail_type = '{order_delay_string}'
           AND items_latest_delivery_date >= '2019-05-08 22:59:59.000000'
         GROUP BY items_line_no, order_id;
        """


sql_claims_last_reason = f"""
            DROP TABLE IF EXISTS star_intermediate.return_reason;
            CREATE TABLE star_intermediate.return_reason AS
            SELECT t1.order_number,
       t1.item_line_number,
       t2.return_reason_skey
  FROM star_summary.f_sales_order t1
       LEFT JOIN star_analytical.d_return_reason t2
       ON t1.return_reason_code = t2.return_reason_code
 GROUP BY t1.order_number,
          t1.item_line_number, t2.return_reason_skey ;"""


create_nps_analytical = """
        CREATE TABLE IF NOT EXISTS star_analytical.f_nps
        (
            webshop_language               VARCHAR(8) ENCODE ZSTD,
            order_number                   VARCHAR(16) ENCODE ZSTD,
            order_line                     INTEGER ENCODE ZSTD,
            delivery_type_skey             INTEGER ENCODE ZSTD,
            item_skey                      BIGINT ENCODE ZSTD,
            shop_skey                      INTEGER ENCODE ZSTD,
            order_date_skey                BIGINT ENCODE ZSTD,
            last_service_organization_skey BIGINT ENCODE ZSTD,
            payment_method                 VARCHAR(32) ENCODE ZSTD,
            claims_last_solution           VARCHAR(16) ENCODE ZSTD,
            showroom_code                  VARCHAR(32) ENCODE ZSTD,
            order_type                     VARCHAR(16) ENCODE ZSTD,
            distribution_channel           VARCHAR(64) ENCODE ZSTD,
            shipping_agent                 VARCHAR(64) ENCODE ZSTD,
            delivery_requested_date        DATE ENCODE ZSTD,
            date_delivery_promised         DATE ENCODE ZSTD,
            invoice_date                   DATE ENCODE ZSTD,
            postal_code                    VARCHAR(8) ENCODE ZSTD,
            nps_score                      INTEGER ENCODE ZSTD,
            onlineshop_and_sortment_score  INTEGER ENCODE ZSTD,
            order_and_payment_score        INTEGER ENCODE ZSTD,
            delivery_score                 INTEGER ENCODE ZSTD,
            article_quality_score          INTEGER ENCODE ZSTD,
            customer_service_score         INTEGER ENCODE ZSTD,
            archive_date                   DATE ENCODE ZSTD,
            order_delivery_week            VARCHAR(128) ENCODE ZSTD,
            cancellation_status            VARCHAR(128) ENCODE ZSTD,
            promoters                      BIGINT ENCODE ZSTD,
            detractors                     BIGINT ENCODE ZSTD,
            total_order_id                 BIGINT ENCODE ZSTD,
            total_0                        BIGINT ENCODE ZSTD,
            happy_homes                    BIGINT ENCODE ZSTD,
            claim_solutions_count          BIGINT ENCODE ZSTD,
            delay_notify_count             INTEGER ENCODE ZSTD,
            return_reason_skey             INTEGER ENCODE ZSTD
        )
            DISTKEY ( order_number )
            SORTKEY ( order_number );
            ALTER TABLE star_analytical.f_nps
            OWNER TO admin;"""

truncate_nps_analytical = "TRUNCATE TABLE star_analytical.f_nps;"

insert_nps_analytical = """
        INSERT
          INTO star_analytical.f_nps(webshop_language,
                             order_number,
                             order_line,
                             delivery_type_skey,
                             item_skey,
                             shop_skey,
                             order_date_skey,
                             last_service_organization_skey,
                             payment_method,
                             claims_last_solution,
                             showroom_code,
                             order_type,
                             distribution_channel,
                             shipping_agent,
                             delivery_requested_date,
                             date_delivery_promised,
                             invoice_date,
                             postal_code,
                             nps_score,
                             onlineshop_and_sortment_score,
                             order_and_payment_score,
                             delivery_score,
                             article_quality_score,
                             customer_service_score,
                             archive_date,
                             order_delivery_week,
                             cancellation_status,
                             promoters,
                             detractors,
                             total_order_id,
                             total_0,
                             happy_homes,
                             claim_solutions_count,
                             delay_notify_count,
                             return_reason_skey)
        SELECT webshop_language,
               f_nps.order_number,
               f_orders.order_line,
               COALESCE(ddt.delivery_type_skey, {v_int_to_replace_null}),
               COALESCE(d_item.item_skey, {v_int_to_replace_null}),
               COALESCE(d_shop.shop_skey, {v_int_to_replace_null}),
               COALESCE(d_calendar.date_skey,{v_date_to_replace_null}),
               COALESCE(f_ticket_stats_max.ticket_queue_skey,{v_int_to_replace_null}),
               COALESCE(payment_method, '{v_str_to_replace_null}'),
               claims_last_solution.order_type,
               COALESCE(f_orders.showroom_code, '{v_str_to_replace_null}'),
               f_orders.order_type,
               f_orders.distribution_channel,
               shipping_agent,
               delivery_requested_date,
               date_delivery_promised,
               invoice_date,
               postal_code,
               nps_score,
               onlineshop_and_sortment_score,
               order_and_payment_score,
               delivery_score,
               article_quality_score,
               customer_service_score,
               archive_date,
               order_delivery_week,
               cancellation_status,
               promoters,
               detractors,
               total_order_id,
               total_0,
               happy_homes,
               claim_solutions_count,
               delay_notify_count,
               COALESCE(return_reason.return_reason_skey, {v_int_to_replace_null})    
          FROM star_summary.f_nps
               LEFT JOIN star_summary.f_orders
               ON f_orders.order_code = f_nps.order_number
               LEFT JOIN star_intermediate.claims_last_solution
               ON f_orders.order_number = claims_last_solution.order_number
                   AND f_orders.order_line = claims_last_solution.order_line
               LEFT JOIN star_intermediate.delay_notify_count
			   ON delay_notify_count.items_line_no = f_orders.order_line 
                   AND delay_notify_count.order_id = f_orders.order_number                     
               LEFT JOIN star_intermediate.return_reason
               ON claims_last_solution.order_number = return_reason.order_number
                  AND claims_last_solution.order_line = return_reason.item_line_number
               LEFT JOIN star_intermediate.claims_count
               ON f_orders.order_number = claims_count.order_number
                   AND f_orders.order_line = claims_count.order_line 
               LEFT JOIN star_intermediate.f_ticket_stats_max
               ON f_ticket_stats_max.order_code = f_nps.order_number   
               LEFT JOIN star_analytical.d_delivery_type AS ddt
               ON f_orders.delivery_type = ddt.delivery_type_desc
               LEFT JOIN star_analytical.d_item
               ON f_orders.item_code = d_item.item_code
               LEFT JOIN star_analytical.d_shop
               ON f_orders.shop_code = d_shop.shop_code
               LEFT JOIN star_analytical.d_calendar
               ON f_orders.order_date = d_calendar.date;""".format(v_int_to_replace_null=v_int_to_replace_null,
                                                                   v_str_to_replace_null=v_str_to_replace_null,
                                                                   v_date_to_replace_null=v_date_to_replace_null)