-- Generate 1 million rows directly in DuckDB
SET memory_limit = '4GB';

CREATE TABLE sales AS
SELECT 
    row_number() OVER () AS order_id,
    '2025-01-01'::DATE + (random() * 365)::INTEGER AS order_date,
    'CUST_' || lpad((random() * 5000)::INTEGER::VARCHAR, 5, '0') AS customer_id,
    (CASE (random() * 4)::INTEGER
        WHEN 0 THEN 'Electronics'
        WHEN 1 THEN 'Clothing'
        WHEN 2 THEN 'Food & Beverage'
        WHEN 3 THEN 'Home & Garden'
        ELSE 'Books'
    END) AS product_category,
    'Product_' || lpad((random() * 200)::INTEGER::VARCHAR, 3, '0') AS product_name,
    (random() * 10 + 1)::INTEGER AS quantity,
    round((random() * 500 + 10)::NUMERIC, 2) AS unit_price,
    round((quantity * unit_price)::NUMERIC, 2) AS total_amount,
    (CASE (random() * 4)::INTEGER
        WHEN 0 THEN 'North'
        WHEN 1 THEN 'East'
        WHEN 2 THEN 'South'
        WHEN 3 THEN 'West'
        ELSE 'Central'
    END) AS region,
    (CASE (random() * 3)::INTEGER
        WHEN 0 THEN 'Credit Card'
        WHEN 1 THEN 'PayPal'
        WHEN 2 THEN 'Bank Transfer'
        ELSE 'COD'
    END) AS payment_method
FROM range(1000000);

-- Verify
SELECT count(*) AS total_rows FROM sales;
-- Output: 1000000

-- Export to CSV
COPY sales TO '/tmp/sales_2026.csv' (FORMAT CSV, HEADER true);

-- Direct CSV query, zero import
SELECT 
    region,
    count(*) AS order_count,
    round(sum(total_amount)) AS total_revenue,
    round(avg(total_amount), 2) AS avg_order_value
FROM read_csv('/tmp/sales_2026.csv', 
    header = true, 
    columns = {
        'order_id': 'INTEGER',
        'order_date': 'DATE',
        'customer_id': 'VARCHAR',
        'product_category': 'VARCHAR',
        'product_name': 'VARCHAR',
        'quantity': 'INTEGER',
        'unit_price': 'DECIMAL(10,2)',
        'total_amount': 'DECIMAL(10,2)',
        'region': 'VARCHAR',
        'payment_method': 'VARCHAR'
    })
WHERE order_date >= '2025-06-01'
GROUP BY region
ORDER BY total_revenue DESC;

-- Import into DuckDB internal storage (faster for repeated queries)
CREATE TABLE sales_imported AS 
SELECT * FROM read_csv('/tmp/sales_2026.csv', header = true);
-- Import time: ~1.2 seconds

-- 1. Monthly sales trend
SELECT 
    strftime(order_date, '%Y-%m') AS month,
    count(*) AS orders,
    count(DISTINCT customer_id) AS unique_customers,
    round(sum(total_amount)) AS revenue,
    round(avg(total_amount), 2) AS avg_order
FROM sales_imported
GROUP BY month
ORDER BY month;

-- 2. Product category ranking
SELECT 
    product_category,
    count(*) AS orders,
    round(sum(total_amount)) AS revenue,
    round(avg(total_amount), 2) AS avg_order_value,
    sum(quantity) AS total_units
FROM sales_imported
GROUP BY product_category
ORDER BY revenue DESC;

-- 3. Region × Month cross-analysis (CUBE query)
SELECT 
    region,
    strftime(order_date, '%Y-%m') AS month,
    count(*) AS orders,
    round(sum(total_amount)) AS revenue
FROM sales_imported
GROUP BY CUBE(region, month)
ORDER BY region, month;

-- 4. TOP 10 high-value customers
SELECT 
    customer_id,
    count(*) AS order_count,
    round(sum(total_amount)) AS total_spent,
    round(avg(total_amount), 2) AS avg_order_value,
    max(order_date) AS last_order_date
FROM sales_imported
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 10;

-- 1. 30-day moving average of daily revenue
SELECT 
    order_date,
    round(sum(total_amount)) AS daily_revenue,
    round(avg(sum(total_amount)) OVER (
        ORDER BY order_date 
        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ), 2) AS moving_avg_30d
FROM sales_imported
GROUP BY order_date
ORDER BY order_date;

-- 2. Month-over-month comparison
WITH monthly AS (
    SELECT 
        strftime(order_date, '%Y-%m') AS month,
        round(sum(total_amount)) AS revenue
    FROM sales_imported
    GROUP BY month
)
SELECT 
    month,
    revenue,
    lag(revenue) OVER (ORDER BY month) AS prev_month,
    round((revenue - lag(revenue) OVER (ORDER BY month)) / 
          lag(revenue) OVER (ORDER BY month) * 100, 2) AS mom_change_pct
FROM monthly
ORDER BY month;

-- 3. Cohort-style customer first/last purchase analysis
SELECT 
    customer_id,
    min(order_date) AS first_purchase,
    max(order_date) AS last_purchase,
    count(*) AS total_orders,
    datediff('day', min(order_date), max(order_date)) AS lifetime_days,
    round(sum(total_amount)) AS lifetime_value
FROM sales_imported
GROUP BY customer_id
HAVING count(*) >= 5
ORDER BY lifetime_value DESC;

-- Export aggregation results as CSV
COPY (
    SELECT 
        product_category,
        region,
        strftime(order_date, '%Y-%m') AS month,
        count(*) AS orders,
        round(sum(total_amount)) AS revenue
    FROM sales_imported
    GROUP BY product_category, region, month
    ORDER BY revenue DESC
) TO '/tmp/sales_report.csv' (FORMAT CSV, HEADER true);

-- Export as Parquet (7:1 compression ratio)
COPY sales_imported TO '/tmp/sales_2026.parquet' (FORMAT PARQUET);
-- CSV: 85 MB → Parquet: 12 MB

-- Export JSON
COPY (
    SELECT region, round(sum(total_amount)) AS revenue
    FROM sales_imported GROUP BY region
) TO '/tmp/region_summary.json' (FORMAT JSON);

-- Scenario: 100 daily-partitioned CSV files
-- File naming: sales_2025-01-01.csv ... sales_2025-04-10.csv

-- Merge and aggregate in one line
SELECT 
    strftime(order_date, '%Y-%m') AS month,
    product_category,
    count(*) AS orders,
    round(sum(total_amount)) AS revenue
FROM read_csv('/data/sales_*.csv', 
    header = true, 
    union_by_name = true  -- auto-match columns by name
)
WHERE order_date >= '2025-01-01'
GROUP BY month, product_category
ORDER BY month, revenue DESC;

-- Merge all files into one big table
CREATE TABLE all_sales AS
SELECT * FROM read_csv('/data/sales_*.csv', 
    header = true, 
    union_by_name = true);

-- DuckDB defaults to all available memory — you can cap it
SET memory_limit = '1GB';
SET temp_directory = '/tmp/duckdb_tmp';  -- spills to disk when needed

-- Bad: read all columns, then filter
SELECT region, sum(total_amount)
FROM read_csv('/data/huge_file.csv', header = true)
WHERE region = 'East';

-- Good: projection pushdown (read only required columns)
SELECT region, sum(total_amount)
FROM read_csv('/data/huge_file.csv', 
    header = true,
    columns = {'region': 'VARCHAR', 'total_amount': 'DECIMAL(10,2)'}
)
WHERE region = 'East';

-- CSV → Parquet (one-time investment, lifelong benefit)
COPY tbl TO '/data/optimized.parquet' (FORMAT PARQUET);

-- Querying Parquet is 3-10x faster than CSV
SELECT region, count(*) 
FROM read_parquet('/data/optimized.parquet')
GROUP BY region;

-- Parquet supports predicate pushdown
SELECT * FROM read_parquet('/data/optimized.parquet')
WHERE order_date BETWEEN '2025-06-01' AND '2025-06-30';

-- Process in quarterly batches
CREATE TABLE result AS
SELECT * FROM read_csv_auto('/data/huge_dataset.csv')
WHERE order_date >= '2025-01-01' 
  AND order_date < '2025-04-01';

-- Merge batches with UNION ALL
CREATE TABLE yearly_result AS
SELECT * FROM read_csv_auto('/data/huge_dataset.csv')
WHERE order_date >= '2025-01-01' AND order_date < '2025-04-01'
UNION ALL
SELECT * FROM read_csv_auto('/data/huge_dataset.csv')
WHERE order_date >= '2025-04-01' AND order_date < '2025-07-01'
UNION ALL
SELECT * FROM read_csv_auto('/data/huge_dataset.csv')
WHERE order_date >= '2025-07-01' AND order_date < '2025-10-01'
UNION ALL
SELECT * FROM read_csv_auto('/data/huge_dataset.csv')
WHERE order_date >= '2025-10-01' AND order_date < '2026-01-01';

-- Create a view that reads files on-the-fly
CREATE VIEW sales_view AS
SELECT * FROM read_csv('/data/sales_*.csv', header = true, union_by_name = true);

-- Query the view — DuckDB reads files at query time
SELECT region, sum(total_amount) 
FROM sales_view
GROUP BY region;
-- Zero storage cost, but re-reads files on each query

#!/usr/bin/env python3
"""
weekly_sales_report.py - DuckDB-powered automated weekly report generator
Usage: python3 weekly_sales_report.py
"""
import duckdb
import datetime

today = datetime.date.today()
monday = today - datetime.timedelta(days=today.weekday())
last_monday = monday - datetime.timedelta(days=7)

# Connect to DuckDB (in-memory mode)
conn = duckdb.connect()

# Install extensions
conn.execute("INSTALL httpfs; LOAD httpfs;")

print(f"📊 Generating weekly report: {last_monday} → {monday}")

# Read and aggregate this week's data
result = conn.execute(f"""
    SELECT 
        product_category,
        region,
        count(*) AS orders,
        count(DISTINCT customer_id) AS customers,
        round(sum(total_amount), 2) AS revenue,
        round(avg(total_amount), 2) AS avg_order_value
    FROM read_csv('/data/sales_*.csv', header = true, union_by_name = true)
    WHERE order_date >= '{last_monday}' 
      AND order_date < '{monday}'
    GROUP BY product_category, region
    ORDER BY revenue DESC
""").fetchdf()

# Export as CSV (Excel-compatible)
result.to_csv('/tmp/weekly_report.csv', index=False)
print(f"✅ Report generated: /tmp/weekly_report.csv")
print(f"📈 {len(result)} rows total")

# Regional rankings
print("\n🏆 Regional Rankings:")
region_stats = conn.execute(f"""
    SELECT region, round(sum(total_amount), 2) AS revenue
    FROM read_csv('/data/sales_*.csv', header = true, union_by_name = true)
    WHERE order_date >= '{last_monday}' AND order_date < '{monday}'
    GROUP BY region ORDER BY revenue DESC
""").fetchdf()
print(region_stats.to_string(index=False))

conn.close()

-- Check the query plan
EXPLAIN ANALYZE SELECT ...;

-- Verify column types are correct
-- Avoid VARCHAR for numeric columns
SELECT typeof(column_name) FROM read_csv(...);

-- Use the right file format
-- CSV < Parquet < DuckDB internal table (fastest)

-- Set a temp directory for spill-to-disk
SET temp_directory = '/disk/tmp';

-- Cap memory usage
SET memory_limit = '2GB';

-- Use streaming (don't cache intermediate results)
SELECT * FROM read_csv('huge.csv') 
WHERE ...  -- predicate pushdown, filter early
;