Skip to content

Instantly share code, notes, and snippets.

@neomantra

neomantra/top_exch_by_volume.sql

Last active September 6, 2025 01:36
Show Gist options
  • Save neomantra/c443b5dab7fc56f87c8a05c343adaad8 to your computer and use it in GitHub Desktop.
Save neomantra/c443b5dab7fc56f87c8a05c343adaad8 to your computer and use it in GitHub Desktop.
Download ZIP
Find exchange with top volume for each ticker with DataBento, dbn-go, and DuckDB
Raw
top_exch_by_volume.sql
# within DuckDB run these commands
# load the parquet ohlcv files, which start with syms
CREATE TABLE ohlcvs AS SELECT * FROM './syms*.parquet';
# load the publishers parquet
CREATE TABLE publishers AS SELECT * FROM './publishers.parquet';
# query for the top publisher_id for each date,ticker
# QUALIFY is DuckDB dialect
SELECT date(ts_event),symbol,volume,close,P.dataset FROM ohlcvs O
JOIN publishers P ON P.publisher_id = O.publisher_id
QUALIFY ROW_NUMBER() OVER (PARTITION BY ts_event, symbol ORDER BY volume DESC) = 1
ORDER BY symbol,ts_event;
Raw
top_exch_by_volume_dbn_work.sh
# install dbn-go if you haven't
#
brew install
# make new working dir
mkdir work ; cd work
# put tickers in file
echo "RBLX\nIBM\AAPL" > syms.txt
# grep removes consolidated EQUS
# add --force to the get-range to remove cost check
# there will be errors one may ignore
for dd in $(dbn-go-hist datasets | grep -v EQUS | grep -v BASIC) ; do echo $dd ;
dbn-go-hist get-range --dataset $dd --schema ohlcv-1d --file syms.txt --start 2025-09-01 --end 2025-09-05 -o syms.$dd.ohlcv-1d.20250901_202060905.dbn.zst
done
# convert all those files to parquet
dbn-go-file parquet *.dbn.zst
# run duckdb saving DB in file work.duckdb
# follow the SQL in top_exch_by_volume.sql
duckdb work.duckdb
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment