Data Definition Language (DDL)

CREATE TABLE and DROP TABLE statements for defining and removing database schema.

CREATE TABLE - Define New Table

Create new tables with explicit schema or from query results.

Basic CREATE TABLE

-- Create table with explicit columns and types
CREATE TABLE prices (
    TICKER VARCHAR,
    DATE DATE,
    PRICE DOUBLE,
    VOLUME LONG
);

-- Create table with multiple columns
CREATE TABLE companies (
    TICKER VARCHAR,
    company_name VARCHAR,
    sector VARCHAR,
    industry VARCHAR,
    market_cap DOUBLE
);

CREATE TABLE AS - From Query

-- Create table from SELECT query
CREATE TABLE price_summary AS (
    SELECT
        TICKER,
        COUNT(*) as num_records,
        AVG(PRICE) as avg_price,
        MIN(PRICE) as min_price,
        MAX(PRICE) as max_price,
        STD(PRICE) as std_price
    FROM prices
    GROUP BY TICKER
);

-- Create table with transformations
CREATE TABLE daily_returns AS (
    SELECT
        TICKER,
        DATE,
        PRICE,
        PRICE / LAG(PRICE, 1) - 1 as daily_return
    FROM prices
    GROUP BY TICKER
);

CREATE TABLE with Computed Columns

-- Create enriched table
CREATE TABLE prices_enhanced AS (
    SELECT
        TICKER,
        DATE,
        PRICE,
        -- Previous values
        LAG(PRICE, 1) as prev_price,
        LAG(PRICE, 5) as price_5d_ago,

        -- Returns
        PRICE / LAG(PRICE, 1) - 1 as daily_return,
        PRICE / LAG(PRICE, 5) - 1 as weekly_return,

        -- Moving averages
        EMA(PRICE, 0.095) as ema_20,
        EMA(PRICE, 0.039) as ema_50,

        -- Rankings
        WRANK(PRICE, 10) as price_decile
    FROM prices
    GROUP BY TICKER
);

CREATE TABLE for Aggregated Data

-- Monthly aggregation table
CREATE TABLE monthly_prices AS (
    SELECT
        TICKER,
        TO_YEAR(DATE) as year,
        TO_MONTH(DATE) as month,
        COUNT(*) as num_days,
        FIRST(PRICE) as open_price,
        LAST(PRICE) as close_price,
        MIN(PRICE) as low_price,
        MAX(PRICE) as high_price,
        AVG(PRICE) as avg_price,
        STD(PRICE) as std_price,
        LAST(PRICE) / FIRST(PRICE) - 1 as monthly_return
    FROM prices
    GROUP BY TICKER, TO_YEAR(DATE), TO_MONTH(DATE)
);

DROP TABLE - Remove Table

Remove tables from the database.

Basic DROP TABLE

-- Drop single table
DROP TABLE prices;

-- Drop multiple tables
DROP TABLE temp_data;
DROP TABLE staging_table;

Warning

DROP TABLE permanently removes the table and all its data. This operation cannot be undone. Always ensure you have backups before dropping tables with important data.

Drop and Recreate Pattern

-- Drop existing table and create new one
DROP TABLE price_summary;

CREATE TABLE price_summary AS (
    SELECT
        TICKER,
        AVG(PRICE) as avg_price,
        STD(PRICE) as std_price
    FROM prices
    GROUP BY TICKER
);

Common Table Patterns

Temporary Result Tables

-- Create temporary result table for complex analysis
CREATE TABLE temp_results AS (
    SELECT
        TICKER,
        DATE,
        PRICE,
        daily_return: PRICE / LAG(PRICE, 1) - 1
    FROM prices
    GROUP BY TICKER
);

-- Further analysis on temp table
CREATE TABLE signals AS (
    SELECT
        TICKER,
        DATE,
        daily_return,
        CASE
            WHEN daily_return > 0.02 THEN 'Strong Buy'
            WHEN daily_return > 0 THEN 'Buy'
            WHEN daily_return < -0.02 THEN 'Strong Sell'
            WHEN daily_return < 0 THEN 'Sell'
            ELSE 'Hold'
        END as signal
    FROM temp_results
);

-- Clean up
DROP TABLE temp_results;

Lookup Tables

-- Create lookup table for company metadata
CREATE TABLE company_info (
    TICKER VARCHAR,
    company_name VARCHAR,
    sector VARCHAR,
    industry VARCHAR,
    country VARCHAR
);

-- Insert data
INSERT INTO company_info (TICKER, company_name, sector, industry, country)
VALUES
    ('AAPL', 'Apple Inc.', 'Technology', 'Consumer Electronics', 'USA'),
    ('MSFT', 'Microsoft Corp.', 'Technology', 'Software', 'USA'),
    ('GOOGL', 'Alphabet Inc.', 'Technology', 'Internet', 'USA');

-- Use in queries with table lookup syntax
UPDATE
    company: company_info[TICKER].company_name,
    sector: company_info[TICKER].sector
FROM prices;

Feature Tables

-- Create table with all features for modeling
CREATE TABLE features AS (
    SELECT
        TICKER,
        DATE,
        PRICE,

        -- Lag features
        LAG(PRICE, 1) as price_lag1,
        LAG(PRICE, 5) as price_lag5,
        LAG(PRICE, 20) as price_lag20,

        -- Returns
        PRICE / LAG(PRICE, 1) - 1 as return_1d,
        PRICE / LAG(PRICE, 5) - 1 as return_5d,
        PRICE / LAG(PRICE, 20) - 1 as return_20d,

        -- Moving averages
        EMA(PRICE, 0.095) as ema_20,
        EMA(PRICE, 0.039) as ema_50,

        -- Volatility
        STD(PRICE / LAG(PRICE, 1) - 1) as volatility_20d,

        -- Rankings
        WRANK(PRICE, 10) as price_rank,
        WRANK(PRICE / LAG(PRICE, 20) - 1, 10) as momentum_rank,

        -- Signals
        CASE WHEN PRICE > EMA(PRICE, 0.095) THEN 1 ELSE 0 END as above_ema20,
        CASE WHEN EMA(PRICE, 0.095) > EMA(PRICE, 0.039) THEN 1 ELSE 0 END as golden_cross

    FROM prices
    GROUP BY TICKER
);

CREATE VIEW - Define Query Views

Views store SELECT queries as named database objects. Unlike tables, views don't store data themselves - they execute the underlying query each time they're referenced.

Basic CREATE VIEW

-- Create a view for expensive products
CREATE VIEW expensive_products AS (
    SELECT * FROM products WHERE price > 100
);

-- Create view with calculated columns
CREATE VIEW daily_returns AS (
    SELECT
        TICKER,
        DATE,
        PRICE,
        PRICE / LAG(PRICE, 1) - 1 as daily_return
    FROM prices
    GROUP BY TICKER
);

DROP VIEW - Remove View

-- Drop single view
DROP VIEW expensive_products;

-- Drop multiple views
DROP VIEW daily_returns;
DROP VIEW monthly_summary;

List Views

-- List all views in database
.views

Note

Views in AVA store the SELECT query definition. The query is executed each time the view is referenced. Views are useful for simplifying complex queries and providing consistent data access patterns.

View Use Cases

-- View for filtered data
CREATE VIEW active_users AS (
    SELECT * FROM users WHERE status = 'active'
);

-- View for aggregated metrics
CREATE VIEW ticker_stats AS (
    SELECT
        TICKER,
        COUNT(*) as num_records,
        AVG(PRICE) as avg_price,
        MIN(PRICE) as min_price,
        MAX(PRICE) as max_price,
        STD(PRICE) as price_volatility
    FROM prices
    GROUP BY TICKER
);

-- View with complex calculations
CREATE VIEW technical_indicators AS (
    SELECT
        TICKER,
        DATE,
        PRICE,
        EMA(PRICE, 0.095) as ema_20,
        EMA(PRICE, 0.039) as ema_50,
        WRANK(PRICE, 10) as price_decile
    FROM prices
    GROUP BY TICKER
);

Practical Examples

Example 1: Data Pipeline with Tables

-- Step 1: Load raw data
.load '/data/bt/20251208W/POLARIS1/data/prices.csv' as prices_raw

-- Step 2: Create cleaned table
CREATE TABLE prices_clean AS (
    SELECT
        TICKER,
        DATE,
        PRICE
    FROM prices_raw
    WHERE PRICE IS NOT NULL
      AND PRICE > 0
      AND DATE IS NOT NULL
);

-- Step 3: Create enriched table with features
CREATE TABLE prices_features AS (
    SELECT
        TICKER,
        DATE,
        PRICE,
        LAG(PRICE, 1) as prev_price,
        PRICE / LAG(PRICE, 1) - 1 as daily_return,
        EMA(PRICE, 0.095) as ema_20,
        WRANK(PRICE, 10) as price_decile
    FROM prices_clean
    GROUP BY TICKER
);

-- Step 4: Create summary table
CREATE TABLE ticker_summary AS (
    SELECT
        TICKER,
        COUNT(*) as num_records,
        FIRST(DATE) as start_date,
        LAST(DATE) as end_date,
        AVG(PRICE) as avg_price,
        STD(daily_return) as volatility,
        LAST(PRICE) / FIRST(PRICE) - 1 as total_return
    FROM prices_features
    GROUP BY TICKER
);

-- Step 5: Save results
.save prices_features AS '/data/processed/prices_features.adb' parameter(format=binary)
.save ticker_summary AS '/data/summary/ticker_summary.csv'

-- Step 6: Clean up intermediate tables
DROP TABLE prices_raw;
DROP TABLE prices_clean;

Example 2: Multi-Table Analysis

-- Load multiple datasets
.load '/data/prices.csv' as prices
.load '/data/dividends.csv' as dividends
.load '/data/splits.csv' as splits

-- Create adjusted prices table
CREATE TABLE prices_adjusted AS (
    SELECT
        p.TICKER,
        p.DATE,
        p.PRICE,
        -- Get split adjustment
        COALESCE(s.split_factor, 1.0) as split_adj,
        -- Apply adjustment
        p.PRICE * COALESCE(s.split_factor, 1.0) as adjusted_price
    FROM prices p
    LEFT JOIN (
        SELECT
            TICKER,
            effective_date,
            CUMPROD(split_ratio) as split_factor
        FROM splits
        GROUP BY TICKER
    ) s ON p.TICKER = s.TICKER AND p.DATE >= s.effective_date
);

-- Create dividend-adjusted returns
CREATE TABLE total_returns AS (
    SELECT
        p.TICKER,
        p.DATE,
        p.adjusted_price,
        LAG(p.adjusted_price, 1) as prev_price,
        COALESCE(d.dividend_amount, 0) as dividend,
        -- Total return including dividends
        (p.adjusted_price + COALESCE(d.dividend_amount, 0)) /
            LAG(p.adjusted_price, 1) - 1 as total_return
    FROM prices_adjusted p
    LEFT JOIN dividends d
        ON p.TICKER = d.TICKER
       AND p.DATE = d.ex_date
    GROUP BY p.TICKER
);

Example 3: Backtesting Framework

-- Load historical prices
.load '/data/prices.csv' as prices

-- Create features table
CREATE TABLE features AS (
    SELECT
        TICKER,
        DATE,
        PRICE,
        PRICE / LAG(PRICE, 20) - 1 as momentum_20,
        WRANK(PRICE / LAG(PRICE, 20) - 1, 10) as momentum_rank,
        EMA(PRICE, 0.095) as ema_20,
        CASE WHEN PRICE > EMA(PRICE, 0.095) THEN 1 ELSE 0 END as trend_signal
    FROM prices
    GROUP BY TICKER
);

-- Create signals table
CREATE TABLE signals AS (
    SELECT
        TICKER,
        DATE,
        momentum_rank,
        trend_signal,
        -- Combined signal: buy top 3 deciles with positive trend
        CASE
            WHEN momentum_rank >= 7 AND trend_signal = 1 THEN 1
            WHEN momentum_rank <= 3 THEN 0
            ELSE NULL
        END as position
    FROM features
);

-- Create returns table
CREATE TABLE backtest_returns AS (
    SELECT
        s.TICKER,
        s.DATE,
        s.position,
        f.PRICE,
        LEAD(f.PRICE, 1) / f.PRICE - 1 as forward_return,
        -- Position return (only when position = 1)
        CASE
            WHEN s.position = 1 THEN LEAD(f.PRICE, 1) / f.PRICE - 1
            ELSE NULL
        END as strategy_return
    FROM signals s
    INNER JOIN features f ON s.TICKER = f.TICKER AND s.DATE = f.DATE
    GROUP BY s.TICKER
);

-- Performance summary
SELECT
    COUNT(*) as num_trades,
    AVG(strategy_return) as avg_return,
    STD(strategy_return) as volatility,
    AVG(strategy_return) / STD(strategy_return) as sharpe_ratio,
    AVG(CASE WHEN strategy_return > 0 THEN 1.0 ELSE 0.0 END) as win_rate
FROM backtest_returns
WHERE strategy_return IS NOT NULL;

Example 4: Python API Integration

import avapy

# Load data
avapy.asql(".load '/data/prices.csv' as prices")

# Create enhanced table
avapy.asql("""
    CREATE TABLE prices_enhanced AS (
        SELECT
            TICKER,
            DATE,
            PRICE,
            PRICE / LAG(PRICE, 1) - 1 as daily_return,
            EMA(PRICE, 0.095) as ema_20,
            WRANK(PRICE, 10) as price_decile
        FROM prices
        GROUP BY TICKER
    )
""")

# Query the new table
result = avapy.asql("""
    SELECT
        TICKER,
        AVG(daily_return) as avg_return,
        STD(daily_return) as volatility
    FROM prices_enhanced
    GROUP BY TICKER
""")

print(result)

# Save to file
avapy.asql(".save prices_enhanced AS '/data/prices_enhanced.adb' parameter(format=binary)")

# Clean up
avapy.asql("DROP TABLE prices")
avapy.asql("DROP TABLE prices_enhanced")