-- This is a standard Postgres Pro table
CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  product_name TEXT,
  amount NUMERIC,
  order_date DATE
);

INSERT INTO orders (product_name, amount, order_date)
VALUES ('Laptop', 1200.00, '2024-07-01'),
      ('Keyboard', 75.50, '2024-07-01'),
      ('Mouse', 25.00, '2024-07-02');

SET duckdb.force_execution = true;
-- Standard SELECT on a Postgres Pro table
SELECT
  category,
  AVG(price) AS avg_price,
  COUNT(*) AS item_count
FROM
  products -- This is a regular Postgres Pro table
GROUP BY
  category
ORDER BY
  avg_price DESC;

SET duckdb.force_execution = true;
-- Standard SELECT on a Postgres Pro table
SELECT
  category,
  AVG(price) AS avg_price,
  COUNT(*) AS item_count
FROM
  products -- This is a regular Postgres Pro table
GROUP BY
  category
ORDER BY
  avg_price DESC;

-- Query a single Parquet file
SELECT
  r['product_id'],
  r['review_text']
FROM
  read_parquet('s3://my-bucket/reviews.parquet') r -- 'r' is a required alias
LIMIT 100;

-- Query multiple CSV files using a glob pattern
SELECT
  r['timestamp'],
  r['event_type'],
  COUNT(*) AS event_count
FROM
  read_csv('s3://my-datalake/logs/2024-*.csv') r
GROUP BY
  r['timestamp'],
  r['event_type'];

-- Join a local Postgres Pro 'customers' table with a remote Parquet file of 'orders'
SELECT
  c.customer_name,
  c.signup_date,
  SUM(r['order_total']) AS total_spent
FROM
  customers c -- This is a Postgres Pro table
JOIN
  read_parquet('s3://my-bucket/orders/*.parquet') r ON c.customer_id = r['customer_id']
WHERE
  c.status = 'active'
GROUP BY
  c.customer_name,
  c.signup_date
ORDER BY
  total_spent DESC;

-- This query uses DuckDB's PIVOT syntax, so it must be wrapped in duckdb.query()
SELECT * FROM duckdb.query('
  PIVOT sales_summary
  ON product_category
  USING SUM(total_sales)
  GROUP BY region;
');