114 lines
3.2 KiB
MySQL
114 lines
3.2 KiB
MySQL
|
|
||
|
|
--------------------------Rdash -------------------------
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS raena_analytics.om_mongo_brand_category;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE raena_analytics.om_mongo_brand_category AS
|
||
|
|
SELECT rsellviewid,
|
||
|
|
brand::json->> 'name' AS Brand_name,
|
||
|
|
arr.item_object::json ->> 'hasChild' AS has_child,
|
||
|
|
arr.item_object::json ->> 'id' AS actual_categoryid,
|
||
|
|
arr.item_object::json ->> 'name' AS actual_category_name
|
||
|
|
FROM raena_analytics.mongo_baseproducts,
|
||
|
|
jsonb_array_elements(replace(categorypath,'''','')::jsonb) WITH
|
||
|
|
ORDINALITY arr(item_object, POSITION);
|
||
|
|
|
||
|
|
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,
|
||
|
|
storeinfo::json->> 'name' AS store,
|
||
|
|
storeinfo::json->> 'storeOwner' AS sellername,
|
||
|
|
storeinfo::json->> '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,
|
||
|
|
storeinfo::json->> 'storeOwner' AS sellername,
|
||
|
|
storeinfo::json->> 'storeOwnerPhone' AS Sellerphone
|
||
|
|
FROM raena_analytics.mongo_orders;
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|