pareekayush6

DROP TABLE IF EXISTS sandbox_ap.item_catalog_history;
CREATE TABLE  sandbox_ap.item_catalog_history
AS
select d_item.item_skey,
       d_shop.shop_skey,
       period_date :: DATE,
       regular_price_amount,
       final_price_amount
        from public.item_catalog_history

pareekayush6

pareekayush6

from airflow.sensors.s3_key_sensor import S3KeySensor
from datetime import datetime, timedelta
from airflow import DAG
from airflow.operators.dummy_operator import DummyOperator
from airflow.utils.dates import days_ago

default_args = {
    'owner': 'airflow',
    'start_date': days_ago(1),
    'email_on_failure': True,

pareekayush6

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,

pareekayush6

-- Adding MMG in canvas
SELECT d_item.item_main_category AS item_main_category
  FROM star_analytical.d_item d_item
 GROUP BY 1

-- Adding gov,nov, goi in the canvas
-- 5758883
SELECT SUM(1)                    AS cnt:f_gov (sandbox_ap.f_gov)_e5fc330e677b4a4dba98eb5975edbddd:o    ,
       d_item.item_main_category AS item_main_category,
       SUM(f_gov.value)          AS sum:value (f_gov):ok

pareekayush6

------------------- Analysing One Fact table on months and mmg -----------------------

-- Adding MMG on the worksheet
SELECT d_item1.item_main_category AS item_main_category, d_calendar1.year AS year
  FROM sandbox_ap.f_gov f_gov
       INNER JOIN star_analytical.d_item d_item1 ON (f_gov.item_skey = d_item1.item_skey)
       INNER JOIN star_analytical.d_shop d_shop1 ON (f_gov.shop_skey = d_shop1.shop_skey)
       INNER JOIN star_analytical.d_calendar d_calendar1 ON (f_gov.date_skey = d_calendar1.date_skey)
 GROUP BY 1, 2

pareekayush6

CREATE SCHEMA IF NOT EXISTS star_sor;
SET SEARCH_PATH TO star_sor;

-- Dim from different source system
DROP TABLE IF EXISTS d_shop;
CREATE TABLE d_shop
(
    shop_skey        INT IDENTITY (0,1),
    shop_code        VARCHAR(16),
    shop_description VARCHAR(64),

pareekayush6

drop table IF EXISTS sandbox.d_item_status;
create table sandbox.d_item_status(
    item_status_skey bigint,
    is_active varchar(20),
    is_sellable varchar(20)
) DISTSTYLE ALL ;

insert into sandbox.d_item_status
values (1,'is active','is sellable'),
       (2,'is not active','is not sellable'),

pareekayush6

DROP TABLE if EXISTS sandbox.line_status
 SELECT CASE
                               WHEN sales_doc_billing_code IS NOT NULL
                                   AND sales_doc_billing_invoice_type_name = 'Rechnung'
                                   AND billing_doc_last_change_date_local <= '2020-03-09'
                                   THEN 'invoiced'
                               WHEN sales_doc_cancellation_confirmed_reason_description IS NOT NULL
                                   OR (sales_doc_cancellation_requested_reason_type != ''
                                        AND sales_doc_cancellation_date_local <= '2020-03-09')
                                   OR (sales_doc_cancellation_requested_reason_type != ''

pareekayush6

--old logic 
DROP TABLE IF EXISTS test.fisrt_activation_date;
WITH
  item_simple_legacy As (
    SELECT
      *,
      ROW_NUMBER() OVER (PARTITION by item_code, webshop_code ORDER BY item_code, webshop_code, COALESCE(item_activation_date,'9999-12-31') ASC) as row_nr
  FROM public.item_legacy
  )
  SELECT c.meta_sku AS item_code,