\connect postgres

drop database if exists fox;
create database fox;

\connect fox

create table users (
  id integer generated by default as identity primary key,
  name text not null
);

create table orders (
  id integer generated by default as identity primary key,
  item text not null,
  amount integer not null default 0
);

create table user_orders (
  id integer generated by default as identity primary key,
  user_id integer not null references users(id),
  order_id integer not null references orders(id)
);

create materialized view order_sums as 
  select  u.id,
          u.name,
          sum(o.amount)
  from    users u
  join    user_orders uo on uo.user_id = u.id
  join    orders o on o.id = uo.order_id
  group by
          u.id,
          u.name
;