Bilbottom

/*
    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
*/

Bilbottom

Bilbottom

/* 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
)

Bilbottom

/*
    Recursive CTEs for data generation

    DuckDB version: 1.0.0

    Bill Wallis, 2024-10-18
*/


select version();

Bilbottom

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

Bilbottom

/*
    Using ROLLUP for subtotals

    DuckDB version: 1.0.0

    Bill Wallis, 2024-08-04
*/


select version();

Bilbottom

/*
    The WINDOW clause needs some more love

    DuckDB version: 1.0.0

    Bill Wallis, 2024-08-02
*/


select version();

Bilbottom

/*
    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/
*/

Bilbottom

/*
    Three-valued logic

    DuckDB version: 1.0.0

    Bill Wallis, 2024-07-27

    https://modern-sql.com/concept/three-valued-logic
*/

Bilbottom

/*
    Friendly SQL with DuckDB (part 2)

    DuckDB version: 1.0.0

    Bill Wallis, 2024-07-27

    Demonstrating some DuckDB features described by Alex Monahan at:

        https://duckdb.org/docs/sql/dialect/friendly_sql