stock_trades_by_ticker (view)

Data license: Public Domain (U.S. Government data) · Data source: Federal Register API & Regulations.gov API

3,962 rows sorted by trade_count descending

✎ View and edit SQL

This data as json, CSV (advanced)

Next page

CREATE VIEW stock_trades_by_ticker AS
SELECT
    st.ticker,
    st.asset_description,
    COUNT(*) AS trade_count,
    COUNT(DISTINCT st.bioguide_id) AS trader_count,
    GROUP_CONCAT(DISTINCT cm.full_name) AS traders,
    SUM(CASE WHEN st.transaction_type LIKE '%Purchase%' THEN 1 ELSE 0 END) AS purchases,
    SUM(CASE WHEN st.transaction_type LIKE '%Sale%' THEN 1 ELSE 0 END) AS sales,
    MIN(st.transaction_date) AS first_trade,
    MAX(st.transaction_date) AS last_trade
FROM stock_trades st
LEFT JOIN congress_members cm ON st.bioguide_id = cm.bioguide_id
WHERE st.ticker IS NOT NULL AND st.ticker != ''
GROUP BY st.ticker
ORDER BY trade_count DESC;