Quickstart Guide

CREATE SCHEMA auction_demo;

SET search_path = auction_demo;

CREATE SOURCE auction_house
  FROM LOAD GENERATOR AUCTION
  (TICK INTERVAL '1s', AS OF 100000)
  FOR ALL TABLES;

SHOW SOURCES;

name                   | type           | cluster    
---------------------- | -------------- | ----------
accounts               | subsource      | quickstart
auction_house          | load-generator | quickstart
auction_house_progress | progress       | null
auctions               | subsource      | quickstart
bids                   | subsource      | quickstart
organizations          | subsource      | quickstart
users                  | subsource      | quickstart

CREATE VIEW winning_bids AS
SELECT DISTINCT ON (a.id) 
  b.*,
  a.item,
  a.seller
FROM auctions AS a
JOIN bids AS b ON a.id = b.auction_id
WHERE 
  b.bid_time < a.end_time          -- Bid placed before auction ended
  AND mz_now() >= a.end_time        -- Auction has completed
ORDER BY 
  a.id,
  b.amount DESC,                    -- Highest bid wins
  b.bid_time,
  b.buyer;

SELECT * FROM winning_bids
ORDER BY bid_time DESC
LIMIT 10;

SELECT * FROM winning_bids
WHERE item = 'Best Pizza in Town'
ORDER BY bid_time DESC
LIMIT 10;

CREATE INDEX wins_by_item ON winning_bids (item);

SELECT * FROM winning_bids
WHERE item = 'Best Pizza in Town'
ORDER BY bid_time DESC
LIMIT 10;

CREATE VIEW flip_activities AS
SELECT 
  w2.seller AS flipper_id,
  w2.item,
  w2.amount AS sold_amount,
  w1.amount AS purchased_amount,
  w2.amount - w1.amount AS profit,
  datediff('days', w2.bid_time, w1.bid_time) AS days_between
FROM winning_bids AS w1
JOIN winning_bids AS w2
  ON w1.buyer = w2.seller          -- Same person bought and sold
  AND w1.item = w2.item            -- Same item
WHERE 
  w2.amount > w1.amount            -- Sold at higher price
  AND datediff('days', w2.bid_time, w1.bid_time) < 8;  -- Within 8 days

SELECT * FROM flip_activities
ORDER BY profit DESC
LIMIT 10;

CREATE TABLE known_flippers (
  flipper_id BIGINT
);

CREATE VIEW flippers AS
SELECT flipper_id
FROM (
  -- Users with multiple flip activities
  SELECT flipper_id
  FROM flip_activities
  GROUP BY flipper_id
  HAVING COUNT(*) >= 2
  
  UNION ALL
  
  -- Manually added known flippers
  SELECT flipper_id
  FROM known_flippers
);

SUBSCRIBE TO (
  SELECT * FROM flippers
) WITH (snapshot = false);

INSERT INTO known_flippers VALUES (450);

-- Track seller credits
CREATE VIEW seller_credits AS
SELECT 
  seller,
  SUM(amount) AS credits
FROM winning_bids
GROUP BY seller;

-- Track buyer debits
CREATE VIEW buyer_debits AS
SELECT 
  buyer,
  SUM(amount) AS debits
FROM winning_bids
GROUP BY buyer;

-- Verify totals match (should always be 0 difference)
CREATE VIEW funds_movement AS
SELECT 
  SUM(credits) AS total_credits,
  SUM(debits) AS total_debits,
  SUM(credits) - SUM(debits) AS difference
FROM (
  SELECT SUM(credits) AS credits, 0 AS debits
  FROM seller_credits
  
  UNION
  
  SELECT 0 AS credits, SUM(debits) AS debits
  FROM buyer_debits
);

SUBSCRIBE TO (
  SELECT * FROM funds_movement
);

-- Drop the source and all dependent objects
DROP SOURCE auction_house CASCADE;

-- Drop the table
DROP TABLE known_flippers;

-- Drop the schema
DROP SCHEMA auction_demo CASCADE;