Redshift Materialized View Demo

gistfile1.txt

drop table store;

drop table sales;

drop materialized view city_sales;


CREATE TABLE "sales" (
  "id" int PRIMARY KEY,
  "item" varchar,
  "store_id" int,
  "customer_id" int,
  "amount" numeric
);

CREATE TABLE "store" (
  "id" int PRIMARY KEY,
  "name" varchar,
  "owner" varchar,
  "city" varchar
);

ALTER TABLE "sales" ADD FOREIGN KEY ("store_id") REFERENCES "store" ("id");

INSERT INTO public.store
(id, name, owner, city)
VALUES(1, 'Electronic Shop', 'Seb', 'Paris'),
(2, 'Video Land', 'Jeff', 'Seattle'),
(3, 'Photo Click', 'Natalia', 'Paris');


INSERT INTO public.sales
(id, item, store_id, customer_id, amount)
VALUES(1, 'HDMI - Thunderbold adapter', 1, 1, 30),
(2, 'SSD Disk 1Tb', 1, 2, 500),(3, 'Flash Card Reader', 1, 3, 10),
(4, 'HDMI - SDI Mixer Box', 2, 1, 300),(5, '4k Camera', 2, 1, 500),
(6, 'Light Ring', 3, 2, 100),(7, 'UV Filter', 3, 1, 50);


select * from store ;

select * from sales;

SELECT st.city, SUM(sa.amount)
  FROM sales sa, store st
  WHERE sa.store_id = st.id
  GROUP BY st.city;
  
CREATE MATERIALIZED VIEW city_sales AS (
  SELECT st.city, SUM(sa.amount) as total_sales
  FROM sales sa, store st
  WHERE sa.store_id = st.id
  GROUP BY st.city
);

SELECT city,total_sales FROM city_sales;

SELECT city, total_sales FROM city_sales WHERE city = 'Paris';

INSERT INTO sales
(id, item, store_id, customer_id, amount)
VALUES(8, 'Gaming PC Super ProXXL', 1, 1, 3000);

REFRESH MATERIALIZED VIEW city_sales;

SELECT city, total_sales FROM city_sales WHERE city = 'Paris';