data for lateral join

raw.sql

-- Create accounts table with ID, account_name, and a JSONB column for storing addresses
CREATE TABLE accounts (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  addresses JSONB
);

-- Create purchases table with account_id, created_at, and product_name columns
CREATE TABLE purchases (
  purchase_id SERIAL PRIMARY KEY,
  account_id INT REFERENCES accounts(id),
  name VARCHAR(255),
  tags TEXT,
  created_at TIMESTAMP
);

-- Insert sample data into the accounts table, including account names and various addresses
INSERT INTO accounts (name, addresses) VALUES
  ('Acme Corp', '[{"state": "California", "city": "Los Angeles", "zip": "90001"}, {"state": "Texas", "city": "Houston", "zip": "77001"}]'),
  ('Globex Inc.', '[{"state": "New York", "city": "New York", "zip": "10001"}, {"state": "Florida", "city": "Miami", "zip": "33101"}]'),
  ('Innotech', '[{"state": "Illinois", "city": "Chicago", "zip": "60601"}, {"state": "Ohio", "city": "Columbus", "zip": "43085"}]'),
  ('Vandalay Industries', '[{"state": "Georgia", "city": "Atlanta", "zip": "30301"}, {"state": "Washington", "city": "Seattle", "zip": "98101"}]');

-- Insert sample data into the purchases table
INSERT INTO purchases (account_id, name, created_at, tags) VALUES
  (1, 'Widget A', '2023-08-01 12:00:00', 'widget,electronics'),
  (1, 'Widget B', '2023-08-05 14:00:00', 'widget,electronics'),
  (2, 'Gadget X', '2023-08-03 10:00:00', 'gadget,leisure'),
  (2, 'Gadget Y', '2023-08-06 16:00:00', 'gadget,leisure'),
  (3, 'Tool 1', '2023-07-01 12:00:00', 'tool,work'),
  (3, 'Tool 2', '2023-07-05 14:00:00', 'tool,work'),
  (4, 'Instrument P', '2023-06-03 10:00:00', 'instrument,music'),
  (4, 'Instrument Q', '2023-06-06 16:00:00', 'instrument,music'),
  (1, 'Widget C', '2023-08-07 09:00:00', 'widget,electronics'),
  (2, 'Gadget Z', '2023-08-08 17:00:00', 'gadget,leisure'),
  (3, 'Tool 3', '2023-07-03 13:00:00', 'tool,work'),
  (4, 'Instrument R', '2023-06-04 11:00:00', 'instrument,music');