{"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":"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(net_revenue) as net_revenue,\n sum(net_margin) as gross_margin,\n sum(net_margin) / nullif(sum(net_revenue), 0) as margin_pct,\n sum(net_revenue) / nullif(sum(net_units), 0) as wtd_price,\n sum(case when price_band in ('£8-20', '£20+') then net_revenue else 0 end) / nullif(sum(net_revenue), 0) as premium_share\nfrom (select * from retail.sales_month\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}')","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(net_revenue) as net_revenue,\n sum(net_margin) as gross_margin,\n sum(net_margin) / nullif(sum(net_revenue), 0) as margin_pct,\n sum(net_revenue) / nullif(sum(net_units), 0) as wtd_price,\n sum(case when price_band in ('£8-20', '£20+') then net_revenue else 0 end) / nullif(sum(net_revenue), 0) as premium_share\nfrom (select * from retail.sales_month\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}')","compiled":false,"inline":true},{"id":"margin_by_month","compiledQueryString":"select order_month, sum(net_margin) / nullif(sum(net_revenue), 0) as margin_pct\nfrom (select * from retail.sales_month\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 order by 1","inputQueryString":"select order_month, sum(net_margin) / nullif(sum(net_revenue), 0) as margin_pct\nfrom (select * from retail.sales_month\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 order by 1","compiled":false,"inline":true},{"id":"by_band","compiledQueryString":"select price_band_order, price_band,\n sum(net_margin) / nullif(sum(net_revenue), 0) as \"Margin %\",\n sum(net_revenue) as \"Net Revenue\",\n sum(net_margin) as gross_margin\nfrom (select * from retail.sales_month\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, 2 order by 1","inputQueryString":"select price_band_order, price_band,\n sum(net_margin) / nullif(sum(net_revenue), 0) as \"Margin %\",\n sum(net_revenue) as \"Net Revenue\",\n sum(net_margin) as gross_margin\nfrom (select * from retail.sales_month\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, 2 order by 1","compiled":false,"inline":true},{"id":"sku_scatter","compiledQueryString":"select stock_code, max(product) as product,\n sum(net_units) as units,\n sum(net_margin) / nullif(sum(net_revenue), 0) as margin_pct,\n sum(net_revenue) as 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}')\ngroup by 1\nhaving sum(net_units) > 0\norder by revenue desc\nlimit 400","inputQueryString":"select stock_code, max(product) as product,\n sum(net_units) as units,\n sum(net_margin) / nullif(sum(net_revenue), 0) as margin_pct,\n sum(net_revenue) as 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}')\ngroup by 1\nhaving sum(net_units) > 0\norder by revenue desc\nlimit 400","compiled":false,"inline":true},{"id":"margin_watch","compiledQueryString":"select product as \"Product\",\n sum(net_units) as units,\n sum(net_revenue) as net_rev,\n sum(net_margin) / nullif(sum(net_revenue), 0) as margin_pct,\n case when sum(net_margin) / nullif(sum(net_revenue), 0) < 0.3846\n then '' || printf('%.1f%%', 100 * sum(net_margin) / nullif(sum(net_revenue), 0)) || ''\n else printf('%.1f%%', 100 * sum(net_margin) / nullif(sum(net_revenue), 0)) end as margin_html\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 net_rev desc\nlimit 20","inputQueryString":"select product as \"Product\",\n sum(net_units) as units,\n sum(net_revenue) as net_rev,\n sum(net_margin) / nullif(sum(net_revenue), 0) as margin_pct,\n case when sum(net_margin) / nullif(sum(net_revenue), 0) < 0.3846\n then '' || printf('%.1f%%', 100 * sum(net_margin) / nullif(sum(net_revenue), 0)) || ''\n else printf('%.1f%%', 100 * sum(net_margin) / nullif(sum(net_revenue), 0)) end as margin_html\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 net_rev desc\nlimit 20","compiled":false,"inline":true}]}

Pricing & Margin

No Results