Materialized Views (Emulated in SQLite)

Advanced

A dashboard repeatedly asks for the total sales and order count per region from the 2,000-row sales table. Re-aggregating on every page view is wasteful when the numbers change only once a day.

Many databases offer materialized views for this. SQLite does not, but you can emulate one with a regular table filled from a query. Create a table named sales_by_region with the columns region, total_amount (sum of amount) and order_count (number of sales), then select all of its rows ordered by region.

Constraints

Create the table sales_by_region with CREATE TABLE ... AS SELECT
Columns: region, total_amount, order_count
Finish with SELECT * FROM sales_by_region ORDER BY region;

Hints

Hint 1

CREATE TABLE name AS SELECT ... stores the query result as a real table.

Hint 2

Use GROUP BY region with SUM(amount) and COUNT(*) and alias them.

Keywords

SQL

Run your code to see results

No results yet

Run your code to check it against the examples, or submit to run every test.