SQL for revenue by top 5 states

[MilesW]

Hi

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?

Many thanks

Miles

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

[akash_agrawal]

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.

Thanks,

Akash Agrawal

Senior Analyst, Magento Business Intelligence