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

  • Select an option

    Learn more about clone URLs
  • Save neomantra/c443b5dab7fc56f87c8a05c343adaad8 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save neomantra/c443b5dab7fc56f87c8a05c343adaad8 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. neomantra revised this gist Sep 6, 2025. 2 changed files with 1 addition and 1 deletion.
    0 queries.sql → top_exch_by_volume.sql
    View file
    File renamed without changes.
    2 changes: 1 addition & 1 deletion top_exch_by_volume_dbn_work.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -19,6 +19,6 @@ done
    dbn-go-file parquet *.dbn.zst

    # run duckdb saving DB in file work.duckdb
    # follow the SQL in queries.sql
    # follow the SQL in top_exch_by_volume.sql
    duckdb work.duckdb

  2. neomantra renamed this gist Sep 6, 2025. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion bash_work.sh → top_exch_by_volume_dbn_work.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,5 @@
    # install dbn-go if you haven't
    # https://github.com/NimbleMarkets/dbn-go/blob/main/cmd/README.md#installation
    #
    brew install

    # make new working dir
  3. neomantra revised this gist Sep 6, 2025. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions bash_work.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +1,5 @@
    # install dbn-go if you haven't
    # https://github.com/NimbleMarkets/dbn-go/blob/main/cmd/README.md#installation
    brew install

    # make new working dir
  4. neomantra created this gist Sep 6, 2025.
    23 changes: 23 additions & 0 deletions bash_work.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,23 @@
    # 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 queries.sql
    duckdb work.duckdb

    14 changes: 14 additions & 0 deletions queries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,14 @@
    # 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;