raena_analytics_scripts/redshift/sql_code/360_rdash_etl.sql

--------------------------Rdash -------------------------

DROP TABLE IF EXISTS raena_analytics.om_mongo_brand_category;


CREATE TABLE raena_analytics.om_mongo_brand_category AS
select rsellviewid,
json_extract_path_text(brand, 'name') as Brand_name,
json_extract_path_text(JSON_EXTRACT_ARRAY_ELEMENT_TEXT(replace(categorypath, chr(39), ''), 0), 'hasChild') as has_child,
json_extract_path_text(JSON_EXTRACT_ARRAY_ELEMENT_TEXT(replace(categorypath, chr(39), ''), 0), 'id') as actual_categoryid,
json_extract_path_text(JSON_EXTRACT_ARRAY_ELEMENT_TEXT(replace(categorypath, chr(39), ''), 0), 'name') as actual_category_name
from raena_analytics.mongo_baseproducts 
union 
select rsellviewid,
json_extract_path_text(brand, 'name') as Brand_name,
json_extract_path_text(JSON_EXTRACT_ARRAY_ELEMENT_TEXT(replace(categorypath, chr(39), ''), 1), 'hasChild') as has_child,
json_extract_path_text(JSON_EXTRACT_ARRAY_ELEMENT_TEXT(replace(categorypath, chr(39), ''), 1), 'id') as actual_categoryid,
json_extract_path_text(JSON_EXTRACT_ARRAY_ELEMENT_TEXT(replace(categorypath, chr(39), ''), 1), 'name') as actual_category_name
from raena_analytics.mongo_baseproducts mo 
union
select rsellviewid,
json_extract_path_text(brand, 'name') as Brand_name,
json_extract_path_text(JSON_EXTRACT_ARRAY_ELEMENT_TEXT(replace(categorypath, chr(39), ''), 2), 'hasChild') as has_child,
json_extract_path_text(JSON_EXTRACT_ARRAY_ELEMENT_TEXT(replace(categorypath, chr(39), ''), 2), 'id') as actual_categoryid,
json_extract_path_text(JSON_EXTRACT_ARRAY_ELEMENT_TEXT(replace(categorypath, chr(39), ''), 2), 'name') as actual_category_name
from raena_analytics.mongo_baseproducts mo 
union
select rsellviewid,
json_extract_path_text(brand, 'name') as Brand_name,
json_extract_path_text(JSON_EXTRACT_ARRAY_ELEMENT_TEXT(replace(categorypath, chr(39), ''), 3), 'hasChild') as has_child,
json_extract_path_text(JSON_EXTRACT_ARRAY_ELEMENT_TEXT(replace(categorypath, chr(39), ''), 3), 'id') as actual_categoryid,
json_extract_path_text(JSON_EXTRACT_ARRAY_ELEMENT_TEXT(replace(categorypath, chr(39), ''), 3), 'name') as actual_category_name
from raena_analytics.mongo_baseproducts mo;

DROP TABLE IF exists raena_analytics.om_mongo_Order_item_details;


CREATE TABLE raena_analytics.om_mongo_Order_item_details AS
SELECT orderid,
       orderitemid,
       quantity,
       originalprice,
       rsellviewid,
       productsource,
       sku
FROM raena_analytics.mongo_orderitems mo
ORDER BY orderid ;

drop table if exists raena_analytics.om_mongo_Order_details;

create table raena_analytics.om_mongo_Order_details
as
select createdat,
orderid,
json_extract_path_text(storeinfo, 'name') as store,
json_extract_path_text(storeinfo, 'storeOwner') as sellername,
json_extract_path_text(storeinfo, 'storeOwnerPhone') as Sellerphone,
storename,
marketplaceorderid,
totalamount ,orderStatus
from raena_analytics.mongo_orders;


DROP TABLE IF EXISTS raena_analytics.om_mongo_orders_summary_metabase;


CREATE TABLE raena_analytics.om_mongo_orders_summary_metabase AS
SELECT DISTINCT mod.*,
                moid.orderitemid,
                moid.quantity,
                moid.originalprice,
                moid.rsellviewid,
                moid.productsource,
                moid.sku,
                mbc.brand_name,
                mbc.has_child,
                mbc.actual_categoryid,
                mbc.actual_category_name,
                row_number() over (partition BY mod.orderid,orderitemid,sku
                                   ORDER BY originalprice DESC) AS R
FROM raena_analytics.om_mongo_Order_details MOD
LEFT JOIN raena_analytics.om_mongo_Order_item_details moid ON MOD.orderid=moid.orderid
LEFT JOIN raena_analytics.om_mongo_brand_category mbc ON moid.rsellviewid=mbc.rsellviewid;

DROP TABLE IF EXISTS raena_analytics.OM_Mongo_orders_dump_metabase;


CREATE TABLE raena_analytics.OM_Mongo_orders_dump_metabase AS
SELECT orderid,
       status,
       shippingamount,
       paymenttype,
       createdat,
       isarchived,
       invoicedata,
       estimatedeliverydate,
       delivereddate,
       pickupdonedate,
       actualshippingdate,
       daystoship,
       currency,
       cancelreason,
       cancelby,
       buyercancelreason,
       orderupdatedat,
       ordercreatedat,
       totalamount,
       orderstatus,
       paymenttime,
       paymentstatus,
       storename,
       marketplacestoreid,
       marketplaceorderid,
       sellerbusinessprostoreid,
       sellerbusinessproid,
       sellerraenaemail,
       sellerraenaphonenumber,
       sellerraenausername,
       sellerraenauserid,
       fulfillmentstatus,
       marketplaceid,
       storeid,
       orderref,
       json_extract_path_text(storeinfo, 'storeOwner') as sellername,
       json_extract_path_text(storeinfo, 'storeOwnerPhone') as Sellerphone
FROM raena_analytics.mongo_orders;