{"queries":[{"id":"months","compiledQueryString":"select distinct order_month from retail.sales_month order by 1","inputQueryString":"select distinct order_month from retail.sales_month order by 1","compiled":false,"inline":true},{"id":"countries","compiledQueryString":"select country, sum(net_revenue) as rev from retail.sales_month group by 1 order by rev desc","inputQueryString":"select country, sum(net_revenue) as rev from retail.sales_month group by 1 order by rev desc","compiled":false,"inline":true},{"id":"categories","compiledQueryString":"select category, sum(net_revenue) as rev from retail.sales_month group by 1 order by rev desc","inputQueryString":"select category, sum(net_revenue) as rev from retail.sales_month group by 1 order by rev desc","compiled":false,"inline":true},{"id":"sku_rank","compiledQueryString":"with sku as (\n select stock_code, max(product) as product, sum(net_revenue) as rev\n from (select * from retail.sales_sku\n where order_month between date_trunc('month', '${inputs.date_range.start}'::date + ${new Date().getTimezoneOffset() < 0 ? 1 : 0})\n and '${inputs.date_range.end}'::date + ${new Date().getTimezoneOffset() < 0 ? 1 : 0}\n and country like '${inputs.country.value}'\n and category like '${inputs.category.value}')\n group by 1\n)\nselect stock_code, product, rev,\n row_number() over (order by rev desc) as rn,\n count(*) over () as n,\n sum(rev) over () as tot,\n sum(rev) over (order by rev desc rows between unbounded preceding and current row) as cum\nfrom sku\nwhere rev > 0","inputQueryString":"with sku as (\n select stock_code, max(product) as product, sum(net_revenue) as rev\n from (select * from retail.sales_sku\n where order_month between date_trunc('month', '${inputs.date_range.start}'::date + ${new Date().getTimezoneOffset() < 0 ? 1 : 0})\n and '${inputs.date_range.end}'::date + ${new Date().getTimezoneOffset() < 0 ? 1 : 0}\n and country like '${inputs.country.value}'\n and category like '${inputs.category.value}')\n group by 1\n)\nselect stock_code, product, rev,\n row_number() over (order by rev desc) as rn,\n count(*) over () as n,\n sum(rev) over () as tot,\n sum(rev) over (order by rev desc rows between unbounded preceding and current row) as cum\nfrom sku\nwhere rev > 0","compiled":false,"inline":true},{"id":"kpi","compiledQueryString":"-- the DateRange input reports local midnight as a UTC date, i.e. one day early east of UTC; the + n undoes that\nselect sum(case when rn <= 10 then rev else 0 end) / max(tot) as top10_share,\n sum(case when rn <= 100 then rev else 0 end) / max(tot) as top100_share,\n count(*) filter (where cum <= 0.5 * tot) as skus_to_half,\n max(rev) as top_sku_rev\nfrom (with sku as (\n select stock_code, max(product) as product, sum(net_revenue) as rev\n from (select * from retail.sales_sku\n where order_month between date_trunc('month', '${inputs.date_range.start}'::date + ${new Date().getTimezoneOffset() < 0 ? 1 : 0})\n and '${inputs.date_range.end}'::date + ${new Date().getTimezoneOffset() < 0 ? 1 : 0}\n and country like '${inputs.country.value}'\n and category like '${inputs.category.value}')\n group by 1\n)\nselect stock_code, product, rev,\n row_number() over (order by rev desc) as rn,\n count(*) over () as n,\n sum(rev) over () as tot,\n sum(rev) over (order by rev desc rows between unbounded preceding and current row) as cum\nfrom sku\nwhere rev > 0)","inputQueryString":"-- the DateRange input reports local midnight as a UTC date, i.e. one day early east of UTC; the + n undoes that\nselect sum(case when rn <= 10 then rev else 0 end) / max(tot) as top10_share,\n sum(case when rn <= 100 then rev else 0 end) / max(tot) as top100_share,\n count(*) filter (where cum <= 0.5 * tot) as skus_to_half,\n max(rev) as top_sku_rev\nfrom ${sku_rank}","compiled":true,"inline":true},{"id":"curve","compiledQueryString":"select (ceil(100.0 * rn / n / 5) * 5)::int as top_pct,\n max(cum) / max(tot) as cum_share\nfrom (with sku as (\n select stock_code, max(product) as product, sum(net_revenue) as rev\n from (select * from retail.sales_sku\n where order_month between date_trunc('month', '${inputs.date_range.start}'::date + ${new Date().getTimezoneOffset() < 0 ? 1 : 0})\n and '${inputs.date_range.end}'::date + ${new Date().getTimezoneOffset() < 0 ? 1 : 0}\n and country like '${inputs.country.value}'\n and category like '${inputs.category.value}')\n group by 1\n)\nselect stock_code, product, rev,\n row_number() over (order by rev desc) as rn,\n count(*) over () as n,\n sum(rev) over () as tot,\n sum(rev) over (order by rev desc rows between unbounded preceding and current row) as cum\nfrom sku\nwhere rev > 0)\ngroup by 1 order by 1","inputQueryString":"select (ceil(100.0 * rn / n / 5) * 5)::int as top_pct,\n max(cum) / max(tot) as cum_share\nfrom ${sku_rank}\ngroup by 1 order by 1","compiled":true,"inline":true},{"id":"movers","compiledQueryString":"-- fixed comparable window: 2011 (ends 9 Dec) vs 2010 before 10 Dec; ignores the date filter, as in the original\nwith s as (\n select stock_code, max(product) as d,\n sum(case when year = 2011 then net_revenue else 0 end) as r11,\n sum(case when year = 2010 and yoy_window then net_revenue else 0 end) as r10\n from retail.sales_sku\n where country like '${inputs.country.value}'\n and category like '${inputs.category.value}'\n group by 1\n), elig as (select * from s where r10 >= 15000 or r11 >= 15000)\nselect case when r10 = 0 then 'New in 2011' when r11 = 0 then 'Dropped'\n when r11 > r10 then 'Grown' else 'Declined' end as status,\n left(d, 20) as product, r10, r11, r11 - r10 as change\nfrom elig\norder by abs(r11 - r10) desc\nlimit 12","inputQueryString":"-- fixed comparable window: 2011 (ends 9 Dec) vs 2010 before 10 Dec; ignores the date filter, as in the original\nwith s as (\n select stock_code, max(product) as d,\n sum(case when year = 2011 then net_revenue else 0 end) as r11,\n sum(case when year = 2010 and yoy_window then net_revenue else 0 end) as r10\n from retail.sales_sku\n where country like '${inputs.country.value}'\n and category like '${inputs.category.value}'\n group by 1\n), elig as (select * from s where r10 >= 15000 or r11 >= 15000)\nselect case when r10 = 0 then 'New in 2011' when r11 = 0 then 'Dropped'\n when r11 > r10 then 'Grown' else 'Declined' end as status,\n left(d, 20) as product, r10, r11, r11 - r10 as change\nfrom elig\norder by abs(r11 - r10) desc\nlimit 12","compiled":false,"inline":true},{"id":"hero","compiledQueryString":"with top5 as (\n select stock_code, left(max(product), 18) as nm\n from (select * from retail.sales_sku\n where order_month between date_trunc('month', '${inputs.date_range.start}'::date + ${new Date().getTimezoneOffset() < 0 ? 1 : 0})\n and '${inputs.date_range.end}'::date + ${new Date().getTimezoneOffset() < 0 ? 1 : 0}\n and country like '${inputs.country.value}'\n and category like '${inputs.category.value}')\n group by 1\n order by sum(net_revenue) desc\n limit 5\n)\nselect b.order_month as month, t.nm as product, sum(b.net_revenue) as net_revenue\nfrom (select * from retail.sales_sku\n where order_month between date_trunc('month', '${inputs.date_range.start}'::date + ${new Date().getTimezoneOffset() < 0 ? 1 : 0})\n and '${inputs.date_range.end}'::date + ${new Date().getTimezoneOffset() < 0 ? 1 : 0}\n and country like '${inputs.country.value}'\n and category like '${inputs.category.value}') b join top5 t on t.stock_code = b.stock_code\ngroup by 1, 2 order by 1","inputQueryString":"with top5 as (\n select stock_code, left(max(product), 18) as nm\n from (select * from retail.sales_sku\n where order_month between date_trunc('month', '${inputs.date_range.start}'::date + ${new Date().getTimezoneOffset() < 0 ? 1 : 0})\n and '${inputs.date_range.end}'::date + ${new Date().getTimezoneOffset() < 0 ? 1 : 0}\n and country like '${inputs.country.value}'\n and category like '${inputs.category.value}')\n group by 1\n order by sum(net_revenue) desc\n limit 5\n)\nselect b.order_month as month, t.nm as product, sum(b.net_revenue) as net_revenue\nfrom (select * from retail.sales_sku\n where order_month between date_trunc('month', '${inputs.date_range.start}'::date + ${new Date().getTimezoneOffset() < 0 ? 1 : 0})\n and '${inputs.date_range.end}'::date + ${new Date().getTimezoneOffset() < 0 ? 1 : 0}\n and country like '${inputs.country.value}'\n and category like '${inputs.category.value}') b join top5 t on t.stock_code = b.stock_code\ngroup by 1, 2 order by 1","compiled":false,"inline":true},{"id":"top_units","compiledQueryString":"select product,\n sum(net_units) as units,\n sum(net_revenue) as net_rev,\n sum(net_revenue) / nullif(sum(net_units), 0) as avg_price\nfrom (select * from retail.sales_sku\n where order_month between date_trunc('month', '${inputs.date_range.start}'::date + ${new Date().getTimezoneOffset() < 0 ? 1 : 0})\n and '${inputs.date_range.end}'::date + ${new Date().getTimezoneOffset() < 0 ? 1 : 0}\n and country like '${inputs.country.value}'\n and category like '${inputs.category.value}')\ngroup by 1\norder by units desc\nlimit 15","inputQueryString":"select product,\n sum(net_units) as units,\n sum(net_revenue) as net_rev,\n sum(net_revenue) / nullif(sum(net_units), 0) as avg_price\nfrom (select * from retail.sales_sku\n where order_month between date_trunc('month', '${inputs.date_range.start}'::date + ${new Date().getTimezoneOffset() < 0 ? 1 : 0})\n and '${inputs.date_range.end}'::date + ${new Date().getTimezoneOffset() < 0 ? 1 : 0}\n and country like '${inputs.country.value}'\n and category like '${inputs.category.value}')\ngroup by 1\norder by units desc\nlimit 15","compiled":false,"inline":true}]}

Bestsellers

No Results