Example data: items and orders

CREATE TABLE orders (  order_id int NOT NULL,  order_date timestamp NOT NULL,  item text NOT NULL,  quantity int NOT NULL );  INSERT INTO orders VALUES (1,current_timestamp,'brownie',10), (1,current_timestamp,'chocolate cake',1), (1,current_timestamp,'chocolate chip cookie',20), (1,current_timestamp,'coffee cake',1), (1,current_timestamp,'fruit tart',4), (2,current_timestamp + (15 * interval '1 minute'),'chocolate chip cookie',20), (2,current_timestamp + (15 * interval '1 minute'),'brownie',20), (3,current_timestamp + (30 * interval '1 minute'),'sugar cookie',10), (3,current_timestamp + (30 * interval '1 minute'),'donut',36), (3,current_timestamp + (30 * interval '1 minute'),'chiffon cake',2), (3,current_timestamp + (30 * interval '1 minute'),'egg tart',6), (4,current_timestamp + (1 * interval '1 day') + (35 * interval '1 minute'),'cheesecake',1), (5,current_timestamp + (1 * interval '1 day')+ (35 * interval '1 minute'),'chocolate chip cookie',20), (5,current_timestamp + (1 * interval '1 day')+ (35 * interval '1 minute'),'brownie',20), (5,current_timestamp + (1 * interval '1 day')+ (35 * interval '1 minute'),'cheesecake',1), (5,current_timestamp + (1 * interval '1 day')+ (35 * interval '1 minute'),'cupcake',6), (5,current_timestamp + (1 * interval '1 day')+ (35 * interval '1 minute'),'chocolate cake',1), (6,current_timestamp + (1 * interval '2 day'),'chocolate cake',1), (7,current_timestamp + (1 * interval '2 day')+ (10 * interval '1 minute'),'coffee cake',1), (7,current_timestamp + (1 * interval '2 day')+ (10 * interval '1 minute'),'egg tart',12), (8,current_timestamp + (1 * interval '2 day')+ (15 * interval '1 minute'),'chocolate chip cookie',12), (8,current_timestamp + (1 * interval '2 day')+ (15 * interval '1 minute'),'brownie',12), (8,current_timestamp + (1 * interval '2 day')+ (15 * interval '1 minute'),'sugar cookie',12), (8,current_timestamp + (1 * interval '2 day')+ (15 * interval '1 minute'),'donut',12), (9,current_timestamp + (1 * interval '2 day')+ (30 * interval '1 minute'),'cupcake',6), (10,current_timestamp + (1 * interval '3 day'),'chiffon cake',1), (10,current_timestamp + (1 * interval '3 day'),'egg tart',6), (10,current_timestamp + (1 * interval '3 day'),'fruit tart',6), (11,current_timestamp + (1 * interval '3 day') + (15 * interval '1 minute'),'cheesecake',1), (12,current_timestamp + (1 * interval '3 day') + (35 * interval '1 minute'),'brownies',12), (12,current_timestamp + (1 * interval '3 day') + (35 * interval '1 minute'),'cupcake',12), (12,current_timestamp + (1 * interval '3 day') + (35 * interval '1 minute'),'chocolate cake',1) ;  CREATE TABLE items(  item text NOT NULL,  price numeric(8,4) NOT NULL,  currency text NOT NULL DEFAULT 'USD' );  INSERT INTO items VALUES ('brownie',2.25,'USD'), ('cheesecake',40,'USD'), ('chiffon cake',30,'USD'), ('chocolate cake',30,'USD'), ('chocolate chip cookie',2.5,'USD'), ('coffee cake',25,'USD'), ('cupcake',3,'USD'), ('donut',1.25,'USD'), ('egg tart',2.5,'USD'), ('fruit tart',4.5,'USD'), ('sugar cookie',2.5,'USD');  CREATE VIEW orders_view AS SELECT o.*,i.price,o.quantity * i.price as subtotal FROM orders as o JOIN items as i ON o.item = i.item;  CREATE VIEW orders_daily_totals AS SELECT date_trunc('day',order_date) AS order_date,  sum(subtotal) AS daily_total FROM orders_view GROUP BY date_trunc('day',order_date);   CREATE TABLE sales_items (  week_of date NOT NULL,  items text[] );  INSERT INTO sales_items VALUES (date_trunc('week', current_timestamp),ARRAY['brownie','chocolate chip cookie','chocolate cake']), (date_trunc('week', current_timestamp + (1* interval '7 day')),ARRAY['chocolate chip cookie','donut','cupcake']);