{"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":"perf","compiledQueryString":"with base as (\n select f.value, ${inputs.campo.value} as dimension,\n case\n when '${inputs.win}' = 'MES' and f.pidx = a.aidx then 'cur'\n when '${inputs.win}' = 'MES' and f.pidx = a.aidx - 1 then 'prev'\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}' = 'TAM' and f.pidx between a.aidx - 11 and a.aidx then 'cur'\n when '${inputs.win}' = 'TAM' and f.pidx between a.aidx - 23 and a.aidx - 12 then 'prev'\n when '${inputs.win}' = 'YTD' and f.year = a.ayear and f.pidx <= a.aidx then 'cur'\n when '${inputs.win}' = 'YTD' and f.year = a.ayear - 1 and f.pidx <= a.aidx - 12 then 'prev'\n end as bucket\n from marketshare.fact_full f\n cross join (\n select cast('${inputs.anio}' as integer) as ayear,\n cast('${inputs.anio}' as integer) * 12\n + cast('${inputs.mes}' as integer) as aidx\n ) a\n where f.metric = '${inputs.metrica.value}'\n and ('${inputs.pais}' = 'ALL' or f.country_code = '${inputs.pais}')\n),\ntot as (select sum(value) filter (where bucket = 'cur') as mkt_cur from base),\nagg as (\n select\n dimension,\n sum(value) filter (where bucket = 'cur') as ventas,\n sum(value) filter (where bucket = 'prev') as ventas_prev,\n sum(value) filter (where bucket = 'cur') - sum(value) filter (where bucket = 'prev') as crecimiento,\n case when sum(value) filter (where bucket = 'prev') = 0 then null\n else sum(value) filter (where bucket = 'cur') / sum(value) filter (where bucket = 'prev') - 1 end as pct_crec,\n sum(value) filter (where bucket = 'cur') / (select mkt_cur from tot) as peso\n from base\n where bucket is not null\n group by 1\n having sum(value) filter (where bucket = 'cur') > 0\n)\nselect\n dimension, ventas, ventas_prev, crecimiento, pct_crec, peso,\n replace(printf('%,d', cast(round(ventas) as bigint)), ',', '.') as ventas_fmt,\n replace(printf('%,d', cast(round(coalesce(ventas_prev,0)) as bigint)), ',', '.') as ventas_prev_fmt,\n replace(printf('%.1f', peso * 100), '.', ',') || ' %' as peso_fmt,\n case when crecimiento is null then '–'\n when crecimiento > 0 then '▲ ' || replace(printf('%,d', cast(round(crecimiento) as bigint)), ',', '.') || ''\n when crecimiento < 0 then '▼ ' || replace(printf('%,d', cast(round(abs(crecimiento)) as bigint)), ',', '.') || ''\n else '– 0'\n end as crec_html,\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\nfrom agg\norder by ventas desc","inputQueryString":"with base as (\n select f.value, ${inputs.campo.value} as dimension,\n case\n when '${inputs.win}' = 'MES' and f.pidx = a.aidx then 'cur'\n when '${inputs.win}' = 'MES' and f.pidx = a.aidx - 1 then 'prev'\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}' = 'TAM' and f.pidx between a.aidx - 11 and a.aidx then 'cur'\n when '${inputs.win}' = 'TAM' and f.pidx between a.aidx - 23 and a.aidx - 12 then 'prev'\n when '${inputs.win}' = 'YTD' and f.year = a.ayear and f.pidx <= a.aidx then 'cur'\n when '${inputs.win}' = 'YTD' and f.year = a.ayear - 1 and f.pidx <= a.aidx - 12 then 'prev'\n end as bucket\n from marketshare.fact_full f\n cross join (\n select cast('${inputs.anio}' as integer) as ayear,\n cast('${inputs.anio}' as integer) * 12\n + cast('${inputs.mes}' as integer) as aidx\n ) a\n where f.metric = '${inputs.metrica.value}'\n and ('${inputs.pais}' = 'ALL' or f.country_code = '${inputs.pais}')\n),\ntot as (select sum(value) filter (where bucket = 'cur') as mkt_cur from base),\nagg as (\n select\n dimension,\n sum(value) filter (where bucket = 'cur') as ventas,\n sum(value) filter (where bucket = 'prev') as ventas_prev,\n sum(value) filter (where bucket = 'cur') - sum(value) filter (where bucket = 'prev') as crecimiento,\n case when sum(value) filter (where bucket = 'prev') = 0 then null\n else sum(value) filter (where bucket = 'cur') / sum(value) filter (where bucket = 'prev') - 1 end as pct_crec,\n sum(value) filter (where bucket = 'cur') / (select mkt_cur from tot) as peso\n from base\n where bucket is not null\n group by 1\n having sum(value) filter (where bucket = 'cur') > 0\n)\nselect\n dimension, ventas, ventas_prev, crecimiento, pct_crec, peso,\n replace(printf('%,d', cast(round(ventas) as bigint)), ',', '.') as ventas_fmt,\n replace(printf('%,d', cast(round(coalesce(ventas_prev,0)) as bigint)), ',', '.') as ventas_prev_fmt,\n replace(printf('%.1f', peso * 100), '.', ',') || ' %' as peso_fmt,\n case when crecimiento is null then '–'\n when crecimiento > 0 then '▲ ' || replace(printf('%,d', cast(round(crecimiento) as bigint)), ',', '.') || ''\n when crecimiento < 0 then '▼ ' || replace(printf('%,d', cast(round(abs(crecimiento)) as bigint)), ',', '.') || ''\n else '– 0'\n end as crec_html,\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\nfrom agg\norder by ventas desc","compiled":false,"inline":true}]}

Performance

Valor €

No Results