Untitled
unknown
plain_text
a year ago
6.1 kB
9
Indexable
with query as (select toYear(li.shipment_shipped_at, 'Europe/Moscow') as y , toMonth(li.shipment_shipped_at, 'Europe/Moscow') as m , toDate(li.shipment_shipped_at, 'Europe/Moscow') as d , li.shipment_id as shipment_id , li.found_quantity as found_quantity, li.price, li.retailer_shelf_price , li.found_quantity*li.price as amount --, li.found_quantity*li.retailer_shelf_price as amount_cp ??со скидкой ниже никак не вяжется --, li.discount --Скидка это скидки от ритейлера, типа перечеркнутой цены ?? судя по всему в фин.метриках не учитывается --, count(distinct li.shipment_id) cnt_ship, count(distinct li.order_id) cnt_ord , li.found_quantity*li.price / sf.goods as dolya , li.found_quantity*li.price / sf.goods * sf.goods_promo_total as goods_promo_total_sku , li.found_quantity*li.price / sf.goods * sf.loyalty_charged_spasibo as loyalty_charged_spasibo_sku , li.found_quantity*li.price / sf.goods * sf.charged_spasibo as charged_spasibo_sku , li.found_quantity*li.price / sf.goods * sf.goods_returns as goods_returns_sku , li.found_quantity*li.price / sf.goods * sf.goods_instamart as goods_instamart_sku , li.found_quantity*li.price / sf.goods * sf.bb_sert as bb_sert_sku , li.found_quantity*li.price / sf.goods * sf.alco_agent_commision as alco_agent_commision_sku --все что идет в целом на заказ , sf.goods as amount_goods-- тоже самое что li.found_quantity*li.price , sf.goods_returns --возвраты , sf.goods_instamart , sf.bb_sert , sf.alco_agent_commision , sf.gmv_goods --- очищенный от собственных закупок, b2b сертификатов, возвратов, комисси с алко заказов , sf.goods_promo_total --идет с минусом, промо на товар , sf.charged_spasibo, sf.loyalty_charged_spasibo --списанные спасибо , sf.gmv_goods_net_promo --- очищенный от собственных закупок, b2b сертификатов, возвратов, комисси с алко заказов и от ПРОМО !!!без списанных спасибо. , sf.gmv_service_fee --доставка и сборка , sf.service_promo_total --промо на доставку и сборку , sf.gmv_service_fee_net_promo --доставка и сборка очищенная от промо , sf.gmv_advertising --!!!!!!!!!!additional_shelf_price_discount --Дополнительные прямые скидки на заказ (помимо базового промо) --все что касается продавца , li.retailer_name as retailer_name , li.retailer_id as retailer_id , li.tenant_id as tenant_id , li.city as city , li.store_id as store_id , sl.legal_entitity as legal_entitity , sl.inn as inn , sd.store_uuid as store_uuid , sd.store_name as store_name --все что касается покупателя , li.user_id as user_id, u.firstname as firstname, u.lastname as lastname, u.email as email, u.pending_email as pending_email, pu.value as phone --все что касается скю , li.retailer_sku as retailer_sku , li.sku as sku , li.product_id as product_id , li.product_name as product_name , li.brand_id as brand_id , li.brand_name as brand_name , li.manufacturer_id as manufacturer_id , li.manufacturer as manufacturer , li.master_category_id as master_category_id , li.master_category as master_category , li.parent_category_id as parent_category_id , mc.product_category as product_category from analytics.line_items as li left join analytics.master_categories_with_product_category_dict as mc on mc.id = li.master_category_id left join analytics.stores_legal_entities as sl on sl.store_id = li.store_id --ИНН по магазинам ретейлера left join analytics.stores_desc as sd on sd.store_id = li.store_id ------справочник магазинов с координатами left join analytics.int_spree_users as u on u.id = li.user_id --ФИО и почта покупателя left join analytics.int_phone_tokens as pu on pu.user_id = li.user_id --номера телефона клиентов left join analytics.bi_shipments_financial as sf on sf.shipment_id = li.shipment_id where 1=1 and sf.b2b_measure != 0 and li.shipment_state = 'shipped' and li.retailer_id in (1,5914,15,8,24,3,97,298,694,122) and toDate(li.shipment_shipped_at, 'Europe/Moscow') between '2023-01-01' and '2023-01-31' --'2024-02-18' --and li.shipment_id in (339235128,433596531,503913335,504010086,504013415,504024545) ) select y, m , count(shipment_id) as cnt_shipm , sum(found_quantity) as quantity , sum(amount) as amount , sum(goods_promo_total_sku) as goods_promo_total_sku , sum(loyalty_charged_spasibo_sku) as loyalty_charged_spasibo_sku , sum(charged_spasibo_sku) as charged_spasibo_sku , sum(goods_returns_sku) as goods_returns_sku , sum(goods_instamart_sku) as goods_instamart_sku , sum(bb_sert_sku) as bb_sert_sku , sum(alco_agent_commision_sku) as alco_agent_commision_sku , retailer_name, retailer_id , tenant_id, city, store_id, legal_entitity, inn, store_uuid, store_name , user_id, firstname, lastname, email, pending_email, phone , retailer_sku, sku, product_id, product_name , brand_id, brand_name, manufacturer_id, manufacturer , master_category_id, master_category, parent_category_id, product_category from query group by y, m , retailer_name, retailer_id , tenant_id, city, store_id, legal_entitity, inn, store_uuid, store_name , user_id, firstname, lastname, email, pending_email, phone , retailer_sku, sku, product_id, product_name , brand_id, brand_name, manufacturer_id, manufacturer , master_category_id, master_category, parent_category_id, product_category
Editor is loading...
Leave a Comment