Question

WITH HighestTradeVolume (SELECT COUNT(t.shares) AS "Total Trades" sl.stock_symbol AS "Stock SYmbol", se.symbol AS "Stock Exchange Symbol", se.name AS "Stock Exchange" FROM stock_exchange se JOIN trade t ON se.stock_ex_id = t.stock_ex_id JOIN stock_listing sl ON t.stock_id = sl.stock_id AND sl.stock_ex_id = t.stock_ex_id GROUP by se.name, se.symbol, sl.stock_symbol ORDER BY se.name, se.symbol, sl.stock_symbol) HAVING SUM(t.shares) = (SELECT MAX(SUM(t.shares)) FROM trade t);

Answer 1

Step-by-step explanation:

SELECT DISTINCT

sl.stock_symbol AS "Stock SYmbol",

se.symbol AS "Stock Exchange Symbol",

se.name AS "Stock Exchange"

COUNT(t.shares) OVER (PARTITION BY sl.stock_symbol,se.symbol,se.name) AS "Total Trades"

MAX(t.trade_amt) OVER (PARTITION BY sl.stock_symbol,se.symbol,se.name) AS "Total Trade"

FROM stock_exchange se

JOIN trade t

ON se.stock_ex_id = t.stock_ex_id

JOIN stock_listing sl

ON t.stock_id = sl.stock_id AND sl.stock_ex_id = t.stock_ex_id

ORDER BY se.name, se.symbol, sl.stock_symbol;