I am trying to relicate the "Revenue by top 5 states" chart in the Executive overview dashboard using SQL but I can't seem to be able to do this. Can anyone help please?
SELECT "Billing address city", SUM("base_grand_total") as "Base grand total", extract(year from "created_at (use)") as "Year", extract(month from "created_at (use)") as "Month" FROM "sales_order" WHERE "created_at (use)" >= '2016-01-01 00:00:00' GROUP BY "Billing address city","Year","Month" ORDER BY "Billing address city" ASC LIMIT 100
Hi Miles -
Could you share where you're attempting to replicate the SQL query in?
The columns `Billing address city` and `created_at (use)` are only available on your Magento BI Data Warehouse and not in the database on your end, so these columns will not be recognized by your SQL client.
Senior Analyst, Magento Business Intelligence