{"queries":[{"id":"metricas","compiledQueryString":"select distinct metric from marketshare.fact_full order by 1","inputQueryString":"select distinct metric from marketshare.fact_full order by 1","compiled":false,"inline":true},{"id":"seg_cat","compiledQueryString":"with base as (\n select f.category, f.value,\n case\n when '${inputs.win}' = 'L4M' and f.pidx between a.aidx - 3 and a.aidx then 'cur'\n when '${inputs.win}' = 'L4M' and f.pidx between a.aidx - 7 and a.aidx - 4 then 'prev'\n when '${inputs.win}' = 'L3M' and f.pidx between a.aidx - 2 and a.aidx then 'cur'\n when '${inputs.win}' = 'L3M' and f.pidx between a.aidx - 5 and a.aidx - 3 then 'prev'\n end as bucket\n from marketshare.fact_full f\n cross join (\n select cast('${inputs.anio}' as integer) * 12\n + cast('${inputs.mes}' as integer) as aidx\n ) a\n where f.metric = '${inputs.metrica.value}' and f.category is not null\n and ('${inputs.pais}' = 'ALL' or f.country_code = '${inputs.pais}')\n),\ntot as (\n select sum(value) filter (where bucket = 'cur') as mkt_cur,\n sum(value) filter (where bucket = 'prev') as mkt_prev\n from base\n),\nt as (\n select category,\n sum(value) filter (where bucket = 'cur') as cur,\n sum(value) filter (where bucket = 'prev') as prev\n from base where bucket is not null group by 1\n),\nagg as (\n select\n category, cur, prev,\n cur - coalesce(prev, 0) as crecimiento,\n case when prev is null or prev = 0 then null else cur / prev - 1 end as pct_crec,\n case when (select mkt_cur from tot) is null or (select mkt_cur from tot) = 0 then null\n when (select mkt_prev from tot) is null or (select mkt_prev from tot) = 0 then 0\n else (cur / (select mkt_cur from tot) - coalesce(prev,0) / (select mkt_prev from tot)) * 10000\n end as bps\n from t\n where cur > 0\n)\nselect\n category, cur, prev, crecimiento, pct_crec, bps,\n replace(printf('%,d', cast(round(cur) as bigint)), ',', '.') as cur_fmt,\n replace(printf('%,d', cast(round(coalesce(prev,0)) as bigint)), ',', '.') as prev_fmt,\n case when pct_crec is null then '–'\n when pct_crec > 0 then '▲ ' || replace(printf('%.1f', pct_crec * 100), '.', ',') || ' %'\n when pct_crec < 0 then '▼ ' || replace(printf('%.1f', abs(pct_crec) * 100), '.', ',') || ' %'\n else '– 0,0 %'\n end as pct_crec_html,\n case when bps is null then '–'\n when bps > 0 then '▲ ' || replace(printf('%.1f', bps), '.', ',') || ''\n when bps < 0 then '▼ ' || replace(printf('%.1f', abs(bps)), '.', ',') || ''\n else '– 0'\n end as bps_html\nfrom agg\norder by cur desc","inputQueryString":"with base as (\n select f.category, f.value,\n case\n when '${inputs.win}' = 'L4M' and f.pidx between a.aidx - 3 and a.aidx then 'cur'\n when '${inputs.win}' = 'L4M' and f.pidx between a.aidx - 7 and a.aidx - 4 then 'prev'\n when '${inputs.win}' = 'L3M' and f.pidx between a.aidx - 2 and a.aidx then 'cur'\n when '${inputs.win}' = 'L3M' and f.pidx between a.aidx - 5 and a.aidx - 3 then 'prev'\n end as bucket\n from marketshare.fact_full f\n cross join (\n select cast('${inputs.anio}' as integer) * 12\n + cast('${inputs.mes}' as integer) as aidx\n ) a\n where f.metric = '${inputs.metrica.value}' and f.category is not null\n and ('${inputs.pais}' = 'ALL' or f.country_code = '${inputs.pais}')\n),\ntot as (\n select sum(value) filter (where bucket = 'cur') as mkt_cur,\n sum(value) filter (where bucket = 'prev') as mkt_prev\n from base\n),\nt as (\n select category,\n sum(value) filter (where bucket = 'cur') as cur,\n sum(value) filter (where bucket = 'prev') as prev\n from base where bucket is not null group by 1\n),\nagg as (\n select\n category, cur, prev,\n cur - coalesce(prev, 0) as crecimiento,\n case when prev is null or prev = 0 then null else cur / prev - 1 end as pct_crec,\n case when (select mkt_cur from tot) is null or (select mkt_cur from tot) = 0 then null\n when (select mkt_prev from tot) is null or (select mkt_prev from tot) = 0 then 0\n else (cur / (select mkt_cur from tot) - coalesce(prev,0) / (select mkt_prev from tot)) * 10000\n end as bps\n from t\n where cur > 0\n)\nselect\n category, cur, prev, crecimiento, pct_crec, bps,\n replace(printf('%,d', cast(round(cur) as bigint)), ',', '.') as cur_fmt,\n replace(printf('%,d', cast(round(coalesce(prev,0)) as bigint)), ',', '.') as prev_fmt,\n case when pct_crec is null then '–'\n when pct_crec > 0 then '▲ ' || replace(printf('%.1f', pct_crec * 100), '.', ',') || ' %'\n when pct_crec < 0 then '▼ ' || replace(printf('%.1f', abs(pct_crec) * 100), '.', ',') || ' %'\n else '– 0,0 %'\n end as pct_crec_html,\n case when bps is null then '–'\n when bps > 0 then '▲ ' || replace(printf('%.1f', bps), '.', ',') || ''\n when bps < 0 then '▼ ' || replace(printf('%.1f', abs(bps)), '.', ',') || ''\n else '– 0'\n end as bps_html\nfrom agg\norder by cur desc","compiled":false,"inline":true},{"id":"seg_long","compiledQueryString":"with base as (\n select f.category, f.value,\n case\n when '${inputs.win}' = 'L4M' and f.pidx between a.aidx - 3 and a.aidx then 'cur'\n when '${inputs.win}' = 'L4M' and f.pidx between a.aidx - 7 and a.aidx - 4 then 'prev'\n when '${inputs.win}' = 'L3M' and f.pidx between a.aidx - 2 and a.aidx then 'cur'\n when '${inputs.win}' = 'L3M' and f.pidx between a.aidx - 5 and a.aidx - 3 then 'prev'\n end as bucket\n from marketshare.fact_full f\n cross join (\n select cast('${inputs.anio}' as integer) * 12\n + cast('${inputs.mes}' as integer) as aidx\n ) a\n where f.metric = '${inputs.metrica.value}' and f.category is not null\n and ('${inputs.pais}' = 'ALL' or f.country_code = '${inputs.pais}')\n),\nt as (\n select category,\n sum(value) filter (where bucket = 'cur') as cur,\n sum(value) filter (where bucket = 'prev') as prev\n from base where bucket is not null group by 1\n having sum(value) filter (where bucket = 'cur') > 0\n)\nselect category, '${inputs.win}' as periodo, cur as ventas from t\nunion all\nselect category, '${inputs.win}' || '-1' as periodo, prev as ventas from t","inputQueryString":"with base as (\n select f.category, f.value,\n case\n when '${inputs.win}' = 'L4M' and f.pidx between a.aidx - 3 and a.aidx then 'cur'\n when '${inputs.win}' = 'L4M' and f.pidx between a.aidx - 7 and a.aidx - 4 then 'prev'\n when '${inputs.win}' = 'L3M' and f.pidx between a.aidx - 2 and a.aidx then 'cur'\n when '${inputs.win}' = 'L3M' and f.pidx between a.aidx - 5 and a.aidx - 3 then 'prev'\n end as bucket\n from marketshare.fact_full f\n cross join (\n select cast('${inputs.anio}' as integer) * 12\n + cast('${inputs.mes}' as integer) as aidx\n ) a\n where f.metric = '${inputs.metrica.value}' and f.category is not null\n and ('${inputs.pais}' = 'ALL' or f.country_code = '${inputs.pais}')\n),\nt as (\n select category,\n sum(value) filter (where bucket = 'cur') as cur,\n sum(value) filter (where bucket = 'prev') as prev\n from base where bucket is not null group by 1\n having sum(value) filter (where bucket = 'cur') > 0\n)\nselect category, '${inputs.win}' as periodo, cur as ventas from t\nunion all\nselect category, '${inputs.win}' || '-1' as periodo, prev as ventas from t","compiled":false,"inline":true}]}

Segmento de Mercado

Categorías

No Results