{"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_units) as units,\n sum(net_revenue) as net_revenue,\n sum(returned_revenue) / nullif(sum(net_revenue), 0) as return_rate\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_units) as units,\n sum(net_revenue) as net_revenue,\n sum(returned_revenue) / nullif(sum(net_revenue), 0) as return_rate\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":"kpi_skus","compiledQueryString":"select count(distinct stock_code) filter (where net_lines > 0) as active_skus 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}')","inputQueryString":"select count(distinct stock_code) filter (where net_lines > 0) as active_skus 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}')","compiled":false,"inline":true},{"id":"returns_by_month","compiledQueryString":"select order_month, sum(returned_revenue) / nullif(sum(net_revenue), 0) as return_rate\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}')\nwhere order_month < date '2011-12-01'\ngroup by 1 order by 1","inputQueryString":"select order_month, sum(returned_revenue) / nullif(sum(net_revenue), 0) as return_rate\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}')\nwhere order_month < date '2011-12-01'\ngroup by 1 order by 1","compiled":false,"inline":true},{"id":"cat_perf","compiledQueryString":"select category,\n sum(net_margin) / nullif(sum(net_revenue), 0) as \"Margin %\",\n sum(net_revenue) as \"Net Revenue\"\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 \"Net Revenue\" desc","inputQueryString":"select category,\n sum(net_margin) / nullif(sum(net_revenue), 0) as \"Margin %\",\n sum(net_revenue) as \"Net Revenue\"\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 \"Net Revenue\" desc","compiled":false,"inline":true},{"id":"returns_by_product","compiledQueryString":"select product,\n sum(returned_revenue) as returned,\n sum(returned_revenue) / nullif(sum(net_revenue), 0) as return_pct\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 returned desc\nlimit 15","inputQueryString":"select product,\n sum(returned_revenue) as returned,\n sum(returned_revenue) / nullif(sum(net_revenue), 0) as return_pct\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 returned desc\nlimit 15","compiled":false,"inline":true},{"id":"top_skus","compiledQueryString":"select 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\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 15","inputQueryString":"select 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\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 15","compiled":false,"inline":true},{"id":"band_mix","compiledQueryString":"select category, price_band, price_band_order, sum(net_revenue) as net_revenue\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 all\norder by category, price_band_order","inputQueryString":"select category, price_band, price_band_order, sum(net_revenue) as net_revenue\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 all\norder by category, price_band_order","compiled":false,"inline":true}]}

Merchandising

No Results