Stock Trading by Committee Members

Custom SQL query returning 953 rows (hide)

SELECT cm.full_name, cm.party, cm.state, c.name AS committee_name, cmem.title AS committee_role, COUNT(DISTINCT st.id) AS total_trades, COUNT(DISTINCT st.ticker) AS unique_tickers, 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 committee_memberships cmem JOIN committees c ON cmem.committee_id = c.committee_id JOIN congress_members cm ON cmem.bioguide_id = cm.bioguide_id JOIN stock_trades st ON cmem.bioguide_id = st.bioguide_id WHERE st.ticker IS NOT NULL AND st.ticker != '' GROUP BY cmem.bioguide_id, cmem.committee_id HAVING total_trades >= 10 ORDER BY total_trades DESC

Edit SQL