DuckDB cross-data-source query

duckdb-cross-data-source-query.sql

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

attach '...\daily_tracker\tracker.db'              as tracker (type sqlite,   read_only);
attach 'dbname=... host=... user=... password=...' as work_hr (type postgres, read_only);
install spatial;
load spatial;


with

dates(work_date) as (
    values
        ('2024-01-01'::date),
        ('2024-01-02'::date),
        ('2024-01-03'::date),
        ('2024-01-04'::date),
        ('2024-01-05'::date),
),

/* SQLite: roll the work logs up to a daily grain */
worked_hours(work_date, hours_worked) as (
    select
        date_time::date,
        (sum(interval) / 60)::decimal(4, 2),
    from tracker.tracker
    group by all
),

/* PostgreSQL: keep only my absences */
absences(work_date, absence_hours) as (
    select absence_date, hours
    from work_hr.employee_management.absences
    where employee_id = 54775
),

/* Excel: keep only my billable hours */
billable_hours(work_date, billable_hours) as (
    select bill_date, billable_hours
    from st_read('...\Billable Hours MASTER v2.xlsx')
    where employee_id = 54775
),

/* Web: explode the UK bank holiday JSON into a table */
bank_holidays(title, work_date, notes, bunting) as (
    select unnest("england-and-wales".events, recursive:=true)
    from 'https://www.gov.uk/bank-holidays.json'
)

select
    work_date,
    coalesce(worked_hours.hours_worked, 0) as hours_worked,
    coalesce(absences.absence_hours, 0) as absence_hours,
    billable_hours.billable_hours,
    bank_holidays.work_date is not null as is_bank_holiday,
from dates
    left join worked_hours   using (work_date)
    left join absences       using (work_date)
    left join billable_hours using (work_date)
    left join bank_holidays  using (work_date)
order by work_date
;

This is part of the following LinkedIn post:

• https://www.linkedin.com/posts/bill-wallis_sqlwithbill-sql-activity-7274336361300557825-a2KX