{"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\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\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_orders","compiledQueryString":"select count(distinct order_id) as orders,\n sum(net_revenue) / nullif(count(distinct order_id), 0) as aov\nfrom retail.orders_inv\nwhere 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 order_id) as orders,\n sum(net_revenue) / nullif(count(distinct order_id), 0) as aov\nfrom retail.orders_inv\nwhere 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_yoy","compiledQueryString":"-- comparable window: 2011 (ends 9 Dec) vs 2010 before 10 Dec; ignores the date filter, as in the original\nselect sum(case when year = 2011 then net_revenue else 0 end)\n / nullif(sum(case when year = 2010 and yoy_window then net_revenue else 0 end), 0) - 1 as yoy\nfrom retail.sales_month\nwhere country like '${inputs.country.value}'\n and category like '${inputs.category.value}'","inputQueryString":"-- comparable window: 2011 (ends 9 Dec) vs 2010 before 10 Dec; ignores the date filter, as in the original\nselect sum(case when year = 2011 then net_revenue else 0 end)\n / nullif(sum(case when year = 2010 and yoy_window then net_revenue else 0 end), 0) - 1 as yoy\nfrom retail.sales_month\nwhere country like '${inputs.country.value}'\n and category like '${inputs.category.value}'","compiled":false,"inline":true},{"id":"by_month","compiledQueryString":"select order_month,\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 order by 1","inputQueryString":"select order_month,\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 order by 1","compiled":false,"inline":true},{"id":"seasonality","compiledQueryString":"select month(order_month) as month_no,\n strftime(order_month, '%b') as month,\n cast(cast(year as integer) as varchar) as year,\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 all\norder by month_no, year","inputQueryString":"select month(order_month) as month_no,\n strftime(order_month, '%b') as month,\n cast(cast(year as integer) as varchar) as year,\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 all\norder by month_no, year","compiled":false,"inline":true},{"id":"top_markets","compiledQueryString":"select country, 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}')\nwhere country <> 'United Kingdom'\ngroup by 1 order by 2 desc limit 10","inputQueryString":"select country, 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}')\nwhere country <> 'United Kingdom'\ngroup by 1 order by 2 desc limit 10","compiled":false,"inline":true},{"id":"cat_mix","compiledQueryString":"select category, 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 2 desc","inputQueryString":"select category, 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 2 desc","compiled":false,"inline":true}]}

Trading Overview