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
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.