Untitled

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