Strikerate_sql

Strikerate_sql

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
INNER JOIN star_analytical.d_shop
on item_catalog_history.webshop_code = d_shop.webshop_code
INNER JOIN star_analytical.d_item
ON item_catalog_history.item_code = d_item.item_code;

DROP TABLE IF EXISTS public.strike_rate_bi7007;
CREATE TABLE public.strike_rate_bi7007
AS
SELECT a.order_date_skey,
       a.item_skey,
       a.shop_skey,
       SUM(CASE
               WHEN a.shop_skey = 5
                   THEN
                   c.final_price_amount
               ELSE
                   b.final_price_amount END) goi_after_strike_price,
       SUM(CASE
               WHEN a.shop_skey = 5
                   THEN
                   c.regular_price_amount
               ELSE
                   b.regular_price_amount END) goi_before_strike_price
  FROM star_analytical.f_sales_order a
       LEFT JOIN sandbox_ap.item_catalog_history b
       ON a.item_skey = b.item_skey
           AND a.shop_skey = b.shop_skey
           AND a.order_date_skey = to_char(b.period_date, 'yyyymmdd') :: INTEGER
       LEFT JOIN sandbox_ap.item_catalog_history C
       ON a.item_skey = C.item_skey
           AND C.shop_skey = 1
           AND a.order_date_skey = to_char(C.period_date, 'yyyymmdd') :: INTEGER
    WHERE a.order_date_skey >= 20180101
GROUP BY a.order_date_skey,
       a.item_skey,
       a.shop_skey;
   
grant select on public.strike_rate_bi7007 to juan_verdeguer ;