with "_user_territories" as (select "id" from "psa"."territories" where "territoryset_id" = '3453' or "territoryset_id" = '1' group by "id"), "_territories" as ((with "children_terrs" as (select "sector_id", "territory_id", uniqueref as child_uniqueref from "psa"."sectors_territories" inner join psa.territories as t on "t"."id" = "territory_id" where "sector_id" in (select "sector_id" from "psa"."sectors_territories" where "territory_id" in ('227082')) and "territory_id" in (select "id" from "psa"."territories" where territoryset_id=1) and "territory_id" in (select "id" from ntk_getsubterritoriesid(ARRAY[227082], 1))), "parent_terrs" as (select "sector_id", "territory_id", "name", uniqueref as parent_uniqueref from "psa"."sectors_territories" as "st" left join psa.territories as t on "t"."id" = "st"."territory_id" where "t"."id" in ('227082')), "st" as (select "children_terrs"."territory_id" as "id", "parent_terrs"."territory_id" as "parent_id", "parent_terrs"."name" as "parent_name", count(*) as num from "children_terrs" inner join "parent_terrs" on "parent_terrs"."sector_id" = "children_terrs"."sector_id" group by "children_terrs"."territory_id", "parent_terrs"."territory_id", "parent_terrs"."name", "child_uniqueref", "parent_uniqueref" order by 1 asc, child_uniqueref like concat(parent_uniqueref,'%') desc nulls last, count(*) desc)  select "t"."id", "t"."name", "t"."id" as "parent_id", "t"."name" as "parent_name", "ts"."id" as "territoryset_id", "t"."uniqueref" as "territory_uniqueref", "ts"."type_id", 0 as child, "ts"."brand_id" from "psa"."territories" as "t" inner join psa.territorysets as ts on "ts"."id" = "territoryset_id" where "t"."id" in ('227082') and ts.id=3453 and "t"."id" in (select "id" from "_user_territories")) union (select st.id, "t"."name", "st"."parent_id", "st"."parent_name", "ts"."id" as "territoryset_id", "t"."uniqueref" as "territory_uniqueref", "ts"."type_id", 1 as child, "ts"."brand_id" from "st" inner join psa.territories as t on "t"."id" = "st"."id" inner join psa.territorysets as ts on "ts"."id" = "t"."territoryset_id")), "_sectors_territories" as (select "parent_id", "_territories"."id", "ps"."sector_id", CASE WHEN parent_id = _territories.id THEN coalesce(ps.split, 1) ELSE coalesce(cs.split, 1) END as split from "_territories" left join "psa"."sectors_territories" as "ps" on "_territories"."parent_id" = "ps"."territory_id" left join "psa"."sectors_territories" as "cs" on "_territories"."id" = "cs"."territory_id" where ps.sector_id = cs.sector_id group by "parent_id", "_territories"."id", "ps"."sector_id", 4), "_brand_groups_array" as (select CONCAT('g',brand_groups.id) as id, "brand_groups"."name", array_agg(brand_id) as brand_ids, "brand_groups"."slug", "weight" from "psa"."brand_groups" left join "psa"."brand_groups_brands" on "group_id" = "brand_groups"."id" where "brand_groups_brands"."country_id" in ('244') and "uv" = 'TRUE' and "brand_groups"."id" < '9999' and 0=1 is null and "brand_groups"."id" < '2' group by "brand_groups"."id", "brand_groups"."name"), "_top_country_brands" as (select "brand", sum(total) as total from "psa"."country_brands" where "country_id" in ('244') and "r" <= '50' group by "brand" order by 2 desc), "_brand_groups" as ((select * from "_brand_groups_array") union (select mv_id::varchar, name, array_agg (mv_id) as brand_ids, lower(name) as slug, CASE WHEN mv_id in (1002,1003,2064) THEN 500 ELSE 1000 END as weight from "psa"."segmentations" where "filter_id" = '7' and "mv_id" in (select "brand" from "psa"."mv_sales_distinct" group by "brand") and ("mv_id" in ('1002')) and "mv_id" in (select "brand_id" from "psa"."brand_groups_brands" where "group_id" = '1' group by "brand_id") group by "mv_id", "name", "slug", "weight") order by "weight" asc, "name" asc), "_territory_names" as (select DISTINCT territories.id as territory_id,  coalesce(dealers.code, dealer_groups.group_code, '-') as id, coalesce(dealers.name, dealer_groups.name, territories.name) as name, "area_managers"."name" as "area_name" from "psa"."territories" left join "psa"."dealers" on "dealers"."territory_uniqueref" = "territories"."uniqueref" and "dealers"."country_id" = '244' left join "psa"."dealer_groups" on "dealer_groups"."group_code" = "uniqueref" and "dealer_groups"."type" = 'Dealer Group' left join "psa"."area_managers" on "area_managers"."code" = "territories"."uniqueref" where "territoryset_id" = '3453' or "territoryset_id" = '1' or "territoryset_id" = '1'), "_uniqueref_dealers" as (select "territories"."id", "dealers"."code", "dealers"."name" from "_territories" as "territories" left join "psa"."dealers" on "code" = "territories"."territory_uniqueref" where "code" is not null and "country_id" in ('244')), "_sector_dealers" as (select "territories"."id", "dealers"."code", "dealers"."name" from "_territories" as "territories" left join "_sectors_territories" as "st" on "st"."id" = "territories"."id" and "st"."parent_id" = "territories"."parent_id" left join "psa"."dealers" on "dealers"."sector_id" = "st"."sector_id" where "code" is not null and "vn_contract" = 'Y' and "country_id" in ('244') and "dealers"."brand_id" = '1002'), "_territory_dealers" as (select COALESCE(_uniqueref_dealers.code,_sector_dealers.code) as code, COALESCE(_uniqueref_dealers.name,_sector_dealers.name) as name, "territory_uniqueref", "territories"."id" as "territory_id", "territories"."id" as "id", "child", "parent_id" from "_territories" as "territories" left join "_uniqueref_dealers" on "_uniqueref_dealers"."id" = "territories"."id" left join "_sector_dealers" on "_sector_dealers"."id" = "territories"."id" where "child" = '1' group by 1, 2, 3, 4, 5, 6, 7), "_base_st" as (select "territory_id", "sector_id" from "psa"."territorysets" left join "psa"."territories" on "territoryset_id" = "territorysets"."id" left join "psa"."sectors_territories" on "territory_id" = "territories"."id" where "type_id" = '5' and "country_id" in ('244')), "_country_brand_dealers" as (select "id", "code", "name" from "psa"."dealers" where "country_id" in ('244') and "brand_id" = '1002' group by "id", "code", "name"), "_comparitive_territory_sales" as (select "territories"."parent_id", "st"."id", coalesce(count(DISTINCT CASE WHEN  "date" between '2019-01-01' and '2019-03-30' THEN sale_id END),0) as "total", coalesce(count(DISTINCT CASE WHEN  "date" between '2018-01-01' and '2018-03-30' THEN sale_id END),0) as "total_prev", coalesce(count(DISTINCT CASE WHEN in_territory = TRUE AND "date" between '2019-01-01' and '2019-03-30' THEN sale_id END),0) as "in_territory", coalesce(count(DISTINCT CASE WHEN in_territory = TRUE AND "date" between '2018-01-01' and '2018-03-30' THEN sale_id END),0) as "in_territory_prev" from "_territories" as "territories" left join "_sectors_territories" as "st" on "st"."id" = "territories"."id" left join "psa"."mv_pipo" on "st"."sector_id" = "mv_pipo"."sold_sector_id" left join "psa"."mv_sales" on "mv_sales"."id" = "sale_id" where "mv_sales"."brand" = '1002' and "mv_sales"."country_id" = '244' and "mv_sales"."sector_id" in (select distinct "sector_id" from "psa"."territories" left join "psa"."sectors_territories" on "territory_id" = "territories"."id" where "territories"."id" in ('227082')) and ("date" between '2019-01-01' and '2019-03-30' or "date" between '2018-01-01' and '2018-03-30') and "ts_type_id" = 1 and "network_id" = '1' group by "territories"."parent_id", "st"."id"), "_comparitive_pumpin" as (select "territories"."parent_id", "st"."id", coalesce(count(DISTINCT CASE WHEN  "date" between '2019-01-01' and '2019-03-30' THEN sale_id END),0) as "pump_in", coalesce(count(DISTINCT CASE WHEN  "date" between '2018-01-01' and '2018-03-30' THEN sale_id END),0) as "pump_in_prev" from "_territories" as "territories" left join "_sectors_territories" as "st" on "st"."id" = "territories"."id" left join "psa"."mv_pipo" on "st"."sector_id" = "mv_pipo"."sold_sector_id" left join "psa"."mv_sales" on "mv_sales"."id" = "sale_id" where "mv_sales"."brand" = '1002' and "mv_sales"."country_id" = '244' and "mv_sales"."sector_id" in (select distinct "sector_id" from "psa"."territories" left join "psa"."sectors_territories" on "territory_id" = "territories"."id" where "territories"."id" in ('227082')) and "mv_sales"."uniqueref" in (select "code" from "_country_brand_dealers") and ("date" between '2019-01-01' and '2019-03-30' or "date" between '2018-01-01' and '2018-03-30') and "ts_type_id" in (1, null) and "in_territory" = FALSE  and "network_id" = '1' group by "territories"."parent_id", "st"."id"), "_regs" as (select sum(CASE WHEN  "date" between '2019-01-01' and '2019-03-30' THEN split END) as "total", sum(CASE WHEN  "date" between '2018-01-01' and '2018-03-30' THEN split END) as "total_prev", "territories"."id", "st"."parent_id" from "_territories" as "territories" left join "_sectors_territories" as "st" on "st"."id" = "territories"."id" and "st"."parent_id" = "territories"."parent_id" left join "psa"."mv_registrations" on "st"."sector_id" = "mv_registrations"."sector_id" where ("date" between '2019-01-01' and '2019-03-30' or "date" between '2018-01-01' and '2018-03-30') and "mv_registrations"."country_id" = '244' and "dedistortion" < '2' group by "territories"."id", "st"."parent_id")  select '' as uniqueref, '' as territory_id, '' as dealer_id, t.name as sector, coalesce(_regs.total,0) as tiv, coalesce(in_territory,0) + coalesce(pump_in,0) as total_sales, coalesce(in_territory,0) as in_territory, CASE
                        WHEN
                            (coalesce(in_territory,0) + coalesce(pump_in,0)) > 0
                        THEN
                            round(coalesce((in_territory)::numeric / (coalesce(in_territory,0) + coalesce(pump_in,0)),0) * 100,6)
                        ELSE
                            0
                        END
                            as percent, coalesce(pump_in,0) as pump_in from "_territories" as "t" left join "_regs" on "_regs"."id" = "t"."id" left join "_comparitive_territory_sales" on "_comparitive_territory_sales"."id" = "t"."id" left join "_comparitive_pumpin" on "_comparitive_pumpin"."id" = "t"."id" where child = 1 order by "t"."name" asc limit 100