billwallis

/*
    Using FILTER in a window (OVER)
    
    PostgreSQL version: 16.2
    
    Bill Wallis, 2025-05-12
*/

drop table if exists events;
create table events (

billwallis

#!/your-path-to/.local/bin/uv run --script

# /// script
# requires-python = ">=3.11"
# dependencies = [
#     "wakepy",
# ]
# ///

"""

billwallis

/*
    DuckDB cross-data-source query
    
    DuckDB version: v1.1.1
    
    Bill Wallis, 2024-12-15


    This is just for illustration: the files are purposefully not included in this Gist
*/

billwallis

billwallis

/* flatten bus route */
with bus_stops(from_stop, to_stop) as (
              select 'BM',  'WQS' from dual
    union all select 'CR',  'BM'  from dual
    union all select 'CS',  'LL'  from dual
    union all select 'GG',  'CS'  from dual
    union all select 'LL',  'OS'  from dual
    union all select 'OS',  'CR'  from dual
    union all select 'WQS', 'GG'  from dual
)

billwallis

/*
    Recursive CTEs for data generation

    DuckDB version: 1.0.0

    Bill Wallis, 2024-10-18
*/


select version();

billwallis

Creating tables...

Timing SQLite queries...
Start time: 2024-08-31 09:23:05.697240
----------------------------------------
100%|██████████| 10000/10000 [00:02<00:00, 3370.72it/s]
query-1.sql: 0.00008558s (50.1%)
query-2.sql: 0.00008527s (49.9%)
----------------------------------------
End time: 2024-08-31 09:23:08.676550

billwallis

/*
    Using ROLLUP for subtotals

    DuckDB version: 1.0.0

    Bill Wallis, 2024-08-04
*/


select version();

billwallis

/*
    The WINDOW clause needs some more love

    DuckDB version: 1.0.0

    Bill Wallis, 2024-08-02
*/


select version();

billwallis

/*
    SQL Indexes (specifically SQLite)

    SQLite version: 3.45.1

    Bill Wallis, 2024-07-27

    https://sqlite.org/queryplanner.html
    https://use-the-index-luke.com/
*/