132 lines
4.8 KiB
SQL
132 lines
4.8 KiB
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;
|
|
|
|
|
|
|
|
|