billwallis

drop table if exists users;
create table users (
    user_id int primary key,
    username varchar(50) not null,
    last_review_ts datetime not null
);
insert into users (user_id, username, last_review_ts)
values
    (1, 'Alex', '2023-12-01 10:00:00'),
    (2, 'Blake', '2024-01-20 21:30:00'),

billwallis

/*
    SQL's GROUPS BETWEEN alternative

    Bill Wallis, 2025-08-05
*/

drop table if exists trials;
create table trials (
    trial_id int,
    trial_date date,

billwallis

/*
    SQL's RANGE BETWEEN alternative

    Bill Wallis, 2025-08-02
*/

drop table if exists events;
create table events (
    event_id int primary key,
    user_id int not null,

billwallis

# /// script
# dependencies = [
#   "duckdb>=1.3.1",
#   "sqlglot[rs]>=27.2.0",
# ]
# ///

import duckdb
import sqlglot
import sqlglot.optimizer.qualify

billwallis

/*
    Moving logic to data

    DuckDB version: v1.3.1

    Bill Wallis, 2025-07-24
*/
select version();

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.3.2
    
    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
)