UK bank holidays 📅

Note

This question is specific to DuckDB:

• https://duckdb.org/

Question

Using DuckDB, parse the UK bank holiday endpoint into an SQL table.

• https://www.gov.uk/bank-holidays.json

Each row in the output should correspond to a single event, and the column headers (below) should map directly to the JSON properties with the same names:

• division
• title
• date
• notes
• bunting

Here's a starting point:

from 'https://www.gov.uk/bank-holidays.json'

The solution can be found at:

• uk-bank-holidays.md

---

Sample input

with bank_holidays("england-and-wales", "scotland", "northern-ireland") as (
    values (
        {
            division: 'england-and-wales',
            events: [
                {title: 'New Year’s Day', date: '2018-01-01', notes: '', bunting: true},
                {title: 'Good Friday',    date: '2018-03-30', notes: '', bunting: false},
            ],
        },
        {
            division: 'scotland',
            events: [
                {title: 'New Year’s Day', date: '2018-01-01', notes: '', bunting: true},
                {title: '2nd January',    date: '2018-01-02', notes: '', bunting: true},
            ],
        },
        {
            division: 'northern-ireland',
            events: [
                {title: 'New Year’s Day',   date: '2018-01-01', notes: '',               bunting: true},
                {title: 'St Patrick’s Day', date: '2018-03-19', notes: 'Substitute day', bunting: true},
            ],
        },
    )
)

Sample output

division	title	date	notes	bunting
england-and-wales	New Year’s Day	2018-01-01		true
england-and-wales	Good Friday	2018-03-30		false
scotland	New Year’s Day	2018-01-01		true
scotland	2nd January	2018-01-02		true
northern-ireland	New Year’s Day	2018-01-01		true
northern-ireland	St Patrick’s Day	2018-03-19	Substitute day	true

solution(division, title, date, notes, bunting) as (
    values
        ('england-and-wales', 'New Year’s Day',   '2018-01-01', '',               true),
        ('england-and-wales', 'Good Friday',      '2018-03-30', '',               false),
        ('scotland',          'New Year’s Day',   '2018-01-01', '',               true),
        ('scotland',          '2nd January',      '2018-01-02', '',               true),
        ('northern-ireland',  'New Year’s Day',   '2018-01-01', '',               true),
        ('northern-ireland',  'St Patrick’s Day', '2018-03-19', 'Substitute day', true)
)

Hint 1

Use UNPIVOT to move the separate columns for each division into a single column.

Hint 2

Use UNNEST to explode the event JSON into separate rows and columns.