queries.sql

-- ============================================================
-- EXECUTION QUALITY QUERIES
-- Analytical SQL for equity order flow and TCA
-- ============================================================


-- ============================================================
-- 1. IMPLEMENTATION SHORTFALL BY ORDER
-- The core TCA metric: how much did execution cost vs arrival?
-- ============================================================

SELECT
    o.order_id,
    o.order_date,
    o.instrument_id,
    o.side,
    o.order_qty,
    b.arrival_price,
    SUM(f.fill_qty)                                         AS filled_qty,
    SUM(f.fill_qty * f.fill_price) / SUM(f.fill_qty)       AS avg_fill_price,
    -- Implementation shortfall in bps
    -- For buys: positive = cost (bought higher than arrival)
    -- For sells: sign is flipped
    CASE o.side
        WHEN 'buy'  THEN (SUM(f.fill_qty * f.fill_price) / SUM(f.fill_qty) - b.arrival_price)
                          / b.arrival_price * 10000
        WHEN 'sell' THEN (b.arrival_price - SUM(f.fill_qty * f.fill_price) / SUM(f.fill_qty))
                          / b.arrival_price * 10000
    END                                                     AS is_bps,
    -- Fill rate
    ROUND(100.0 * SUM(f.fill_qty) / o.order_qty, 1)        AS fill_rate_pct
FROM orders o
JOIN fills f ON f.order_id = o.order_id
JOIN benchmarks b ON b.order_id = o.order_id
GROUP BY o.order_id, o.order_date, o.instrument_id, o.side,
         o.order_qty, b.arrival_price;


-- ============================================================
-- 2. VENUE ANALYSIS — FILL RATE & PRICE QUALITY BY VENUE
-- Which venues are delivering the best execution?
-- ============================================================

SELECT
    v.venue_name,
    v.venue_type,
    COUNT(DISTINCT f.fill_id)                               AS total_fills,
    COUNT(DISTINCT f.order_id)                               AS orders_touched,
    SUM(f.fill_notional)                                     AS total_notional,
    ROUND(AVG(f.fill_qty), 0)                                AS avg_fill_size,
    ROUND(100.0 * SUM(f.is_passive) / COUNT(*), 1)          AS passive_fill_pct,
    ROUND(AVG(f.commission_bps), 2)                          AS avg_commission_bps
FROM fills f
JOIN venues v ON v.venue_id = f.venue_id
GROUP BY v.venue_name, v.venue_type
ORDER BY total_notional DESC;


-- ============================================================
-- 3. VENUE PRICE IMPROVEMENT vs ARRIVAL
-- Are dark pools actually delivering price improvement?
-- ============================================================

SELECT
    v.venue_type,
    COUNT(f.fill_id)                                         AS fills,
    ROUND(AVG(
        CASE o.side
            WHEN 'buy'  THEN (b.arrival_price - f.fill_price) / b.arrival_price * 10000
            WHEN 'sell' THEN (f.fill_price - b.arrival_price) / b.arrival_price * 10000
        END
    ), 2)                                                    AS avg_price_improvement_bps,
    ROUND(AVG(f.commission_bps), 2)                          AS avg_commission_bps,
    -- Net benefit = price improvement minus commissions
    ROUND(AVG(
        CASE o.side
            WHEN 'buy'  THEN (b.arrival_price - f.fill_price) / b.arrival_price * 10000
            WHEN 'sell' THEN (f.fill_price - b.arrival_price) / b.arrival_price * 10000
        END
    ) - AVG(f.commission_bps), 2)                            AS net_benefit_bps
FROM fills f
JOIN orders o ON o.order_id = f.order_id
JOIN venues v ON v.venue_id = f.venue_id
JOIN benchmarks b ON b.order_id = o.order_id
GROUP BY v.venue_type
ORDER BY net_benefit_bps DESC;


-- ============================================================
-- 4. ALGO STRATEGY PERFORMANCE
-- TWAP vs VWAP vs IS vs liquidity-seeking — which works best?
-- ============================================================

SELECT
    COALESCE(o.algo_strategy, 'MANUAL')                      AS strategy,
    COUNT(DISTINCT o.order_id)                                AS order_count,
    ROUND(AVG(o.pct_adv), 2)                                 AS avg_pct_adv,
    ROUND(AVG(
        CASE o.side
            WHEN 'buy'  THEN (agg.avg_price - b.arrival_price) / b.arrival_price * 10000
            WHEN 'sell' THEN (b.arrival_price - agg.avg_price) / b.arrival_price * 10000
        END
    ), 2)                                                    AS avg_is_bps,
    ROUND(AVG(
        CASE o.side
            WHEN 'buy'  THEN (agg.avg_price - b.vwap) / b.vwap * 10000
            WHEN 'sell' THEN (b.vwap - agg.avg_price) / b.vwap * 10000
        END
    ), 2)                                                    AS avg_vs_vwap_bps,
    ROUND(AVG(agg.fill_rate), 1)                             AS avg_fill_rate_pct
FROM orders o
JOIN benchmarks b ON b.order_id = o.order_id
JOIN (
    SELECT
        f.order_id,
        SUM(f.fill_qty * f.fill_price) / SUM(f.fill_qty)   AS avg_price,
        ROUND(100.0 * SUM(f.fill_qty) / o2.order_qty, 1)   AS fill_rate
    FROM fills f
    JOIN orders o2 ON o2.order_id = f.order_id
    GROUP BY f.order_id, o2.order_qty
) agg ON agg.order_id = o.order_id
GROUP BY COALESCE(o.algo_strategy, 'MANUAL')
ORDER BY avg_is_bps ASC;


-- ============================================================
-- 5. SLIPPAGE BY ORDER SIZE BUCKET
-- Does execution quality degrade for larger orders?
-- ============================================================

SELECT
    CASE
        WHEN o.pct_adv < 1   THEN '< 1% ADV'
        WHEN o.pct_adv < 5   THEN '1–5% ADV'
        WHEN o.pct_adv < 10  THEN '5–10% ADV'
        WHEN o.pct_adv < 25  THEN '10–25% ADV'
        ELSE '25%+ ADV'
    END                                                      AS size_bucket,
    COUNT(DISTINCT o.order_id)                                AS orders,
    ROUND(AVG(
        CASE o.side
            WHEN 'buy'  THEN (agg.avg_price - b.arrival_price) / b.arrival_price * 10000
            WHEN 'sell' THEN (b.arrival_price - agg.avg_price) / b.arrival_price * 10000
        END
    ), 2)                                                    AS avg_is_bps,
    ROUND(AVG(agg.fill_rate), 1)                             AS avg_fill_rate_pct,
    ROUND(SUM(agg.total_notional), 0)                        AS total_notional
FROM orders o
JOIN benchmarks b ON b.order_id = o.order_id
JOIN (
    SELECT
        f.order_id,
        SUM(f.fill_qty * f.fill_price) / SUM(f.fill_qty)   AS avg_price,
        SUM(f.fill_notional)                                 AS total_notional,
        ROUND(100.0 * SUM(f.fill_qty) / o2.order_qty, 1)   AS fill_rate
    FROM fills f
    JOIN orders o2 ON o2.order_id = f.order_id
    GROUP BY f.order_id, o2.order_qty
) agg ON agg.order_id = o.order_id
GROUP BY size_bucket
ORDER BY MIN(o.pct_adv);


-- ============================================================
-- 6. TIME-OF-DAY EXECUTION PATTERNS
-- When during the day are we getting the best/worst fills?
-- ============================================================

SELECT
    CASE
        WHEN SUBSTR(f.fill_time, 1, 2) < '08' THEN 'Pre-open'
        WHEN SUBSTR(f.fill_time, 1, 5) BETWEEN '08:00' AND '08:30' THEN 'Open auction'
        WHEN SUBSTR(f.fill_time, 1, 5) BETWEEN '08:30' AND '12:00' THEN 'Morning continuous'
        WHEN SUBSTR(f.fill_time, 1, 5) BETWEEN '12:00' AND '14:00' THEN 'Midday'
        WHEN SUBSTR(f.fill_time, 1, 5) BETWEEN '14:00' AND '16:00' THEN 'Afternoon (US open)'
        WHEN SUBSTR(f.fill_time, 1, 5) BETWEEN '16:00' AND '16:35' THEN 'Close auction'
        ELSE 'After hours'
    END                                                      AS session_period,
    COUNT(f.fill_id)                                         AS fill_count,
    ROUND(SUM(f.fill_notional), 0)                           AS notional,
    ROUND(100.0 * SUM(f.is_passive) / COUNT(*), 1)          AS passive_pct,
    ROUND(AVG(f.commission_bps), 2)                          AS avg_commission_bps
FROM fills f
GROUP BY session_period
ORDER BY MIN(f.fill_time);


-- ============================================================
-- 7. CLIENT-LEVEL EXECUTION SUMMARY
-- What does each client's execution quality look like?
-- ============================================================

SELECT
    c.client_name,
    c.client_type,
    c.tier,
    COUNT(DISTINCT o.order_id)                               AS total_orders,
    ROUND(SUM(agg.total_notional), 0)                        AS total_notional,
    ROUND(AVG(
        CASE o.side
            WHEN 'buy'  THEN (agg.avg_price - b.arrival_price) / b.arrival_price * 10000
            WHEN 'sell' THEN (b.arrival_price - agg.avg_price) / b.arrival_price * 10000
        END
    ), 2)                                                    AS avg_is_bps,
    ROUND(AVG(agg.fill_rate), 1)                             AS avg_fill_rate_pct,
    -- Proportion of orders using algos
    ROUND(100.0 * SUM(CASE WHEN o.algo_strategy IS NOT NULL THEN 1 ELSE 0 END)
          / COUNT(DISTINCT o.order_id), 1)                   AS algo_usage_pct
FROM orders o
JOIN clients c ON c.client_id = o.client_id
JOIN benchmarks b ON b.order_id = o.order_id
JOIN (
    SELECT
        f.order_id,
        SUM(f.fill_qty * f.fill_price) / SUM(f.fill_qty)   AS avg_price,
        SUM(f.fill_notional)                                 AS total_notional,
        ROUND(100.0 * SUM(f.fill_qty) / o2.order_qty, 1)   AS fill_rate
    FROM fills f
    JOIN orders o2 ON o2.order_id = f.order_id
    GROUP BY f.order_id, o2.order_qty
) agg ON agg.order_id = o.order_id
GROUP BY c.client_name, c.client_type, c.tier
ORDER BY total_notional DESC;


-- ============================================================
-- 8. BROKER SCORECARD
-- Comparing execution quality across brokers
-- ============================================================

SELECT
    br.broker_name,
    br.broker_type,
    COUNT(DISTINCT o.order_id)                               AS orders_handled,
    ROUND(SUM(agg.total_notional), 0)                        AS total_notional,
    ROUND(AVG(
        CASE o.side
            WHEN 'buy'  THEN (agg.avg_price - b.arrival_price) / b.arrival_price * 10000
            WHEN 'sell' THEN (b.arrival_price - agg.avg_price) / b.arrival_price * 10000
        END
    ), 2)                                                    AS avg_is_bps,
    ROUND(AVG(
        CASE o.side
            WHEN 'buy'  THEN (agg.avg_price - b.vwap) / b.vwap * 10000
            WHEN 'sell' THEN (b.vwap - agg.avg_price) / b.vwap * 10000
        END
    ), 2)                                                    AS avg_vs_vwap_bps,
    ROUND(AVG(agg.fill_rate), 1)                             AS avg_fill_rate_pct,
    ROUND(AVG(agg.avg_commission), 2)                        AS avg_commission_bps
FROM orders o
JOIN brokers br ON br.broker_id = o.broker_id
JOIN benchmarks b ON b.order_id = o.order_id
JOIN (
    SELECT
        f.order_id,
        SUM(f.fill_qty * f.fill_price) / SUM(f.fill_qty)   AS avg_price,
        SUM(f.fill_notional)                                 AS total_notional,
        ROUND(100.0 * SUM(f.fill_qty) / o2.order_qty, 1)   AS fill_rate,
        AVG(f.commission_bps)                                AS avg_commission
    FROM fills f
    JOIN orders o2 ON o2.order_id = f.order_id
    GROUP BY f.order_id, o2.order_qty
) agg ON agg.order_id = o.order_id
GROUP BY br.broker_name, br.broker_type
ORDER BY avg_is_bps ASC;


-- ============================================================
-- 9. DAILY EXECUTION COST TREND
-- Monitoring execution quality over time
-- ============================================================

SELECT
    o.order_date,
    COUNT(DISTINCT o.order_id)                               AS orders,
    ROUND(SUM(agg.total_notional), 0)                        AS daily_notional,
    ROUND(AVG(
        CASE o.side
            WHEN 'buy'  THEN (agg.avg_price - b.arrival_price) / b.arrival_price * 10000
            WHEN 'sell' THEN (b.arrival_price - agg.avg_price) / b.arrival_price * 10000
        END
    ), 2)                                                    AS avg_is_bps,
    ROUND(AVG(o.pct_adv), 2)                                 AS avg_order_size_pct_adv
FROM orders o
JOIN benchmarks b ON b.order_id = o.order_id
JOIN (
    SELECT
        order_id,
        SUM(fill_qty * fill_price) / SUM(fill_qty)          AS avg_price,
        SUM(fill_notional)                                   AS total_notional
    FROM fills
    GROUP BY order_id
) agg ON agg.order_id = o.order_id
GROUP BY o.order_date
ORDER BY o.order_date;


-- ============================================================
-- 10. MARKET IMPACT — SPREAD CAPTURE ANALYSIS
-- How much of the spread are we capturing vs giving up?
-- ============================================================

SELECT
    i.market_cap_band,
    i.sector,
    COUNT(DISTINCT o.order_id)                               AS orders,
    ROUND(AVG(i.avg_spread_bps), 2)                          AS avg_quoted_spread_bps,
    ROUND(AVG(
        CASE o.side
            WHEN 'buy'  THEN (agg.avg_price - b.arrival_price) / b.arrival_price * 10000
            WHEN 'sell' THEN (b.arrival_price - agg.avg_price) / b.arrival_price * 10000
        END
    ), 2)                                                    AS avg_is_bps,
    -- Spread capture: IS as proportion of half-spread
    ROUND(AVG(
        CASE o.side
            WHEN 'buy'  THEN (agg.avg_price - b.arrival_price) / b.arrival_price * 10000
            WHEN 'sell' THEN (b.arrival_price - agg.avg_price) / b.arrival_price * 10000
        END
    ) / (AVG(i.avg_spread_bps) / 2) * 100, 1)               AS cost_as_pct_half_spread
FROM orders o
JOIN instruments i ON i.instrument_id = o.instrument_id
JOIN benchmarks b ON b.order_id = o.order_id
JOIN (
    SELECT
        order_id,
        SUM(fill_qty * fill_price) / SUM(fill_qty)          AS avg_price
    FROM fills
    GROUP BY order_id
) agg ON agg.order_id = o.order_id
GROUP BY i.market_cap_band, i.sector
ORDER BY i.market_cap_band, avg_is_bps DESC;