Types through CTEs, window functions and views

On this page

On DuckDB:

  • COUNT and RANK give BIGINT, so int64; DuckDB widens SUM of a BIGINT to HUGEINT, which arrives as *big.Int
  • GROUP BY lists every selected gulls column, as DuckDB does not infer them from the primary key
  • The ids come from sequences, as DuckDB has no auto-incrementing key

Reporting queries are rarely a plain SELECT from one table. They build intermediate results with WITH, walk hierarchies with a recursive CTE, rank and total rows with window functions, and read from views. Seagull follows each column's type and nullability through all of these, so a report gets the same typed row struct as a simple lookup, and you do not have to restate what the SQL already says.

This example keeps a colony of gulls, some of them the chicks of others, and a record of the snacks each has stolen. It reads the colony back three ways.

The schema

The migration creates two tables and a view. Seagull reads the view's own SELECT when it replays the migration, so it knows the type of every column the view returns, and a query can read from the view as if it were a table.

db/migrations/0001_gulls.up.sql

CREATE SEQUENCE gull_ids;

CREATE SEQUENCE theft_ids;

CREATE TABLE gulls (
    id        BIGINT  PRIMARY KEY DEFAULT nextval('gull_ids'),
    name      VARCHAR NOT NULL,
    parent_id BIGINT  REFERENCES gulls (id),
    beach     VARCHAR NOT NULL
);

CREATE TABLE thefts (
    id        BIGINT  PRIMARY KEY DEFAULT nextval('theft_ids'),
    gull_id   BIGINT  NOT NULL REFERENCES gulls (id),
    snack     VARCHAR NOT NULL,
    stolen_on VARCHAR NOT NULL
);

CREATE VIEW beach_records AS
SELECT
    gulls.beach,
    COUNT(DISTINCT gulls.id) AS gulls,
    COUNT(thefts.id) AS thefts,
    MAX(thefts.stolen_on) AS last_theft
FROM gulls
LEFT JOIN thefts ON thefts.gull_id = gulls.id
GROUP BY gulls.beach;

A recursive CTE

FamilyTree starts from one gull and finds its descendants. The first part of the CTE picks the starting gull, at generation 0; the second joins each gull's chicks to the rows found so far, one generation further down, until there are none left.

db/queries/gulls.sql

-- sgl.query(FamilyTree, many)
WITH RECURSIVE family AS (
    SELECT id, name, parent_id, 0 AS generation
    FROM gulls
    WHERE id = $1
    UNION ALL
    SELECT gulls.id, gulls.name, gulls.parent_id, family.generation + 1
    FROM gulls
    JOIN family ON gulls.parent_id = family.id
)
SELECT family.name, family.generation, parents.name AS parent
FROM family
LEFT JOIN gulls AS parents ON parents.id = family.parent_id
ORDER BY family.generation, family.id;

internal/store

type FamilyTreeRow struct {
	Name       string  `json:"name"`
	Generation int32   `json:"generation"`
	Parent     *string `json:"parent"`
}

func (queries *Queries) FamilyTree(ctx context.Context, id int64) ([]FamilyTreeRow, error) {
	rows, err := queries.reader.QueryContext(ctx, familyTreeSQL, id)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []FamilyTreeRow
	for rows.Next() {
		var row FamilyTreeRow
		if err := rows.Scan(&row.Name, &row.Generation, &row.Parent); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

generation starts as the integer 0 and grows by generation + 1, so it is an int32. parent comes from the LEFT JOIN back to gulls, which finds no parent for the gull the tree starts from, so it is a *string.

A CTE and window functions

TheftLeague counts each gull's thefts in a CTE, then ranks the gulls on each beach and totals the beach, with two window functions over the CTE's rows:

db/queries/gulls.sql

-- sgl.query(TheftLeague, many)
WITH totals AS (
    SELECT gulls.name, gulls.beach, COUNT(thefts.id) AS thefts
    FROM gulls
    LEFT JOIN thefts ON thefts.gull_id = gulls.id
    GROUP BY gulls.id, gulls.name, gulls.beach
)
SELECT
    name,
    beach,
    thefts,
    RANK() OVER (PARTITION BY beach ORDER BY thefts DESC) AS beach_rank,
    SUM(thefts) OVER (PARTITION BY beach) AS beach_thefts
FROM totals
ORDER BY beach, beach_rank, name;

internal/store

type TheftLeagueRow struct {
	Name        string   `json:"name"`
	Beach       string   `json:"beach"`
	Thefts      int64    `json:"thefts"`
	BeachRank   int64    `json:"beach_rank"`
	BeachThefts *big.Int `json:"beach_thefts"`
}

type Thefts struct {
	ID       int64  `json:"id"`
	GullID   int64  `json:"gull_id"`
	Snack    string `json:"snack"`
	StolenOn string `json:"stolen_on"`
}

func (queries *Queries) TheftLeague(ctx context.Context) ([]TheftLeagueRow, error) {
	rows, err := queries.reader.QueryContext(ctx, theftLeagueSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []TheftLeagueRow
	for rows.Next() {
		var row TheftLeagueRow
		if err := rows.Scan(&row.Name, &row.Beach, &row.Thefts, &row.BeachRank, &row.BeachThefts); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

The count carries through the CTE as a BIGINT, so int64, and RANK() is a BIGINT that is never NULL. DuckDB widens the SUM() of a BIGINT to a 128-bit HUGEINT, so BeachThefts is a *big.Int, where nil would stand for NULL.

A view

BeachRecords reads the view like a table:

db/queries/gulls.sql

-- sgl.query(BeachRecords, many)
SELECT beach, gulls, thefts, last_theft FROM beach_records ORDER BY beach;

internal/store

type BeachRecordsRow struct {
	Beach     string  `json:"beach"`
	Gulls     int64   `json:"gulls"`
	Thefts    int64   `json:"thefts"`
	LastTheft *string `json:"last_theft"`
}

type Gulls struct {
	ID       int64  `json:"id"`
	Name     string `json:"name"`
	ParentID *int64 `json:"parent_id"`
	Beach    string `json:"beach"`
}

type Thefts struct {
	ID       int64  `json:"id"`
	GullID   int64  `json:"gull_id"`
	Snack    string `json:"snack"`
	StolenOn string `json:"stolen_on"`
}

func (queries *Queries) BeachRecords(ctx context.Context) ([]BeachRecordsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, beachRecordsSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []BeachRecordsRow
	for rows.Next() {
		var row BeachRecordsRow
		if err := rows.Scan(&row.Beach, &row.Gulls, &row.Thefts, &row.LastTheft); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

Its columns keep the types the view's own SELECT gives them. The counts are int64, and MAX(stolen_on) keeps the column's text type, so LastTheft is a *string, nil for a beach with no thefts.

Using it

The program hatches six gulls across three beaches, records seven thefts, and prints the family tree of the first gull, the theft league and the beach records.

example.go

package main

import (
	"context"
	"database/sql"
	"fmt"
	"io"

	"seagull.sh/seagull/examples/duckdb/019_ctes_and_windows/internal/store"
)

// run hatches a colony of gulls, records their snack thefts and reads them back three
// ways. Seagull follows each column's type and nullability through the SQL:
//
//   - FamilyTree's recursive CTE starts from one gull and joins its chicks generation by
//     generation; generation is an integer from 0 AS generation and generation + 1, and
//     parent is *string because the LEFT JOIN finds no parent for the first gull;
//   - TheftLeague counts thefts in a CTE, then ranks gulls on each beach with RANK() and
//     totals the beach with SUM() OVER; RANK is a BIGINT that is never NULL, so int64.
//     DuckDB widens the SUM of a BIGINT to a 128-bit HUGEINT, which arrives as *big.Int,
//     and nil stands for NULL, as SQL sums no rows to NULL;
//   - BeachRecords reads the beach_records view from the migration as if it were a table,
//     and its columns keep the types the view's own SELECT gives them.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives the three listings.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	gulls := []store.HatchGullParams{
		{Name: "Steven", ParentID: nil, Beach: "Porthmeor"},
		{Name: "Gwen", ParentID: new(int64(1)), Beach: "Porthmeor"},
		{Name: "Jago", ParentID: new(int64(1)), Beach: "Porthminster"},
		{Name: "Kerensa", ParentID: new(int64(2)), Beach: "Porthmeor"},
		{Name: "Tristan", ParentID: nil, Beach: "Porthminster"},
		{Name: "Morwenna", ParentID: nil, Beach: "Gwithian"},
	}
	for _, gull := range gulls {
		if err := queries.HatchGull(ctx, gull); err != nil {
			return fmt.Errorf("hatching %s: %w", gull.Name, err)
		}
	}
	thefts := []store.RecordTheftParams{
		{GullID: 1, Snack: "chips", StolenOn: "2026-07-01"},
		{GullID: 1, Snack: "pasty", StolenOn: "2026-07-03"},
		{GullID: 1, Snack: "ice cream", StolenOn: "2026-07-04"},
		{GullID: 2, Snack: "chips", StolenOn: "2026-07-02"},
		{GullID: 3, Snack: "pasty", StolenOn: "2026-07-02"},
		{GullID: 3, Snack: "chips", StolenOn: "2026-07-05"},
		{GullID: 5, Snack: "sandwich", StolenOn: "2026-07-03"},
	}
	for _, theft := range thefts {
		if err := queries.RecordTheft(ctx, theft); err != nil {
			return fmt.Errorf("recording a theft: %w", err)
		}
	}

	family, err := queries.FamilyTree(ctx, 1)
	if err != nil {
		return fmt.Errorf("tracing Steven's family: %w", err)
	}
	fmt.Fprintln(output, "Steven's family:")
	for _, member := range family {
		fmt.Fprintf(output, "  generation %d  %-8s parent %s\n", member.Generation, member.Name, orNull(member.Parent))
	}

	league, err := queries.TheftLeague(ctx)
	if err != nil {
		return fmt.Errorf("ranking thieves: %w", err)
	}
	fmt.Fprintln(output, "theft league:")
	for _, entry := range league {
		fmt.Fprintf(output, "  %-13s #%d %-8s %d of %s\n", entry.Beach, entry.BeachRank, entry.Name, entry.Thefts, entry.BeachThefts)
	}

	records, err := queries.BeachRecords(ctx)
	if err != nil {
		return fmt.Errorf("reading beach records: %w", err)
	}
	fmt.Fprintln(output, "beach records:")
	for _, record := range records {
		fmt.Fprintf(output, "  %-13s gulls %d, thefts %d, last theft %s\n", record.Beach, record.Gulls, record.Thefts, orNull(record.LastTheft))
	}
	return nil
}

// orNull formats a nullable field, or returns NULL when the field is nil.
//
// Takes value (*T) which is a nullable field.
//
// Returns string which is the text to print.
func orNull[T any](value *T) string {
	if value == nil {
		return "NULL"
	}
	return fmt.Sprint(*value)
}

What it prints

Steven's family:
  generation 0  Steven   parent NULL
  generation 1  Gwen     parent Steven
  generation 1  Jago     parent Steven
  generation 2  Kerensa  parent Gwen
theft league:
  Gwithian      #1 Morwenna 0 of 0
  Porthmeor     #1 Steven   3 of 4
  Porthmeor     #2 Gwen     1 of 4
  Porthmeor     #3 Kerensa  0 of 4
  Porthminster  #1 Jago     2 of 3
  Porthminster  #2 Tristan  1 of 3
beach records:
  Gwithian      gulls 1, thefts 0, last theft NULL
  Porthmeor     gulls 3, thefts 4, last theft 2026-07-04
  Porthminster  gulls 2, thefts 3, last theft 2026-07-05

When Seagull cannot type an expression, its field is any. An sgl.column directive with type: pins it to the type you know it has; Type overrides shows how.

Across engines

Only these Go types of TheftLeague differ between engines; the rest are the same everywhere.

DuckDBSQLitePostgreSQLMySQLClickHouse
thefts columnint64int64int64int64·
beach_rank columnint64int64int64int64uint64
beach_thefts column*big.Int*int64*apd.Decimal*apd.Decimaluint64
stolen column····uint64

A · marks a parameter or column that version of the example does not have.

CockroachDB and TimescaleDB run the PostgreSQL version unchanged.

MariaDB runs the MySQL version unchanged.

Compare the whole example side by side with SQLite, PostgreSQL, MySQL and ClickHouse.

Run it locally

From the directory you cloned the Seagull repository into:

cd seagull/examples/duckdb && go run ./019_ctes_and_windows

Its test runs it too, and checks what it prints against output.txt:

cd seagull/examples/duckdb && go test ./019_ctes_and_windows

Needs cgo and a C compiler (the DuckDB driver).

The files

Every file of the example, as it is in the Seagull repository.

sgl.yaml

# Copyright 2026 PolitePixels Limited
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

# This project stands against fascism, authoritarianism, and all forms of
# oppression. We built this to empower people, not to enable those who would
# strip others of their rights and dignity.

version: 1

databases:
  - name: colony
    dialect: duckdb
    migrations: db/migrations
    queries: db/queries
    go:
      package: store
      out: internal/store

db/migrations/0001_gulls.up.sql

CREATE SEQUENCE gull_ids;

CREATE SEQUENCE theft_ids;

CREATE TABLE gulls (
    id        BIGINT  PRIMARY KEY DEFAULT nextval('gull_ids'),
    name      VARCHAR NOT NULL,
    parent_id BIGINT  REFERENCES gulls (id),
    beach     VARCHAR NOT NULL
);

CREATE TABLE thefts (
    id        BIGINT  PRIMARY KEY DEFAULT nextval('theft_ids'),
    gull_id   BIGINT  NOT NULL REFERENCES gulls (id),
    snack     VARCHAR NOT NULL,
    stolen_on VARCHAR NOT NULL
);

CREATE VIEW beach_records AS
SELECT
    gulls.beach,
    COUNT(DISTINCT gulls.id) AS gulls,
    COUNT(thefts.id) AS thefts,
    MAX(thefts.stolen_on) AS last_theft
FROM gulls
LEFT JOIN thefts ON thefts.gull_id = gulls.id
GROUP BY gulls.beach;

db/migrations/0001_gulls.down.sql

DROP VIEW beach_records;
DROP TABLE thefts;
DROP TABLE gulls;
DROP SEQUENCE theft_ids;
DROP SEQUENCE gull_ids;

db/queries/gulls.sql

-- sgl.query(HatchGull, exec)
INSERT INTO gulls (name, parent_id, beach) VALUES ($1, $2, $3);

-- sgl.query(RecordTheft, exec)
INSERT INTO thefts (gull_id, snack, stolen_on) VALUES ($1, $2, $3);

-- sgl.query(FamilyTree, many)
WITH RECURSIVE family AS (
    SELECT id, name, parent_id, 0 AS generation
    FROM gulls
    WHERE id = $1
    UNION ALL
    SELECT gulls.id, gulls.name, gulls.parent_id, family.generation + 1
    FROM gulls
    JOIN family ON gulls.parent_id = family.id
)
SELECT family.name, family.generation, parents.name AS parent
FROM family
LEFT JOIN gulls AS parents ON parents.id = family.parent_id
ORDER BY family.generation, family.id;

-- sgl.query(TheftLeague, many)
WITH totals AS (
    SELECT gulls.name, gulls.beach, COUNT(thefts.id) AS thefts
    FROM gulls
    LEFT JOIN thefts ON thefts.gull_id = gulls.id
    GROUP BY gulls.id, gulls.name, gulls.beach
)
SELECT
    name,
    beach,
    thefts,
    RANK() OVER (PARTITION BY beach ORDER BY thefts DESC) AS beach_rank,
    SUM(thefts) OVER (PARTITION BY beach) AS beach_thefts
FROM totals
ORDER BY beach, beach_rank, name;

-- sgl.query(BeachRecords, many)
SELECT beach, gulls, thefts, last_theft FROM beach_records ORDER BY beach;

example.go

package main

import (
	"context"
	"database/sql"
	"fmt"
	"io"

	"seagull.sh/seagull/examples/duckdb/019_ctes_and_windows/internal/store"
)

// run hatches a colony of gulls, records their snack thefts and reads them back three
// ways. Seagull follows each column's type and nullability through the SQL:
//
//   - FamilyTree's recursive CTE starts from one gull and joins its chicks generation by
//     generation; generation is an integer from 0 AS generation and generation + 1, and
//     parent is *string because the LEFT JOIN finds no parent for the first gull;
//   - TheftLeague counts thefts in a CTE, then ranks gulls on each beach with RANK() and
//     totals the beach with SUM() OVER; RANK is a BIGINT that is never NULL, so int64.
//     DuckDB widens the SUM of a BIGINT to a 128-bit HUGEINT, which arrives as *big.Int,
//     and nil stands for NULL, as SQL sums no rows to NULL;
//   - BeachRecords reads the beach_records view from the migration as if it were a table,
//     and its columns keep the types the view's own SELECT gives them.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives the three listings.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	gulls := []store.HatchGullParams{
		{Name: "Steven", ParentID: nil, Beach: "Porthmeor"},
		{Name: "Gwen", ParentID: new(int64(1)), Beach: "Porthmeor"},
		{Name: "Jago", ParentID: new(int64(1)), Beach: "Porthminster"},
		{Name: "Kerensa", ParentID: new(int64(2)), Beach: "Porthmeor"},
		{Name: "Tristan", ParentID: nil, Beach: "Porthminster"},
		{Name: "Morwenna", ParentID: nil, Beach: "Gwithian"},
	}
	for _, gull := range gulls {
		if err := queries.HatchGull(ctx, gull); err != nil {
			return fmt.Errorf("hatching %s: %w", gull.Name, err)
		}
	}
	thefts := []store.RecordTheftParams{
		{GullID: 1, Snack: "chips", StolenOn: "2026-07-01"},
		{GullID: 1, Snack: "pasty", StolenOn: "2026-07-03"},
		{GullID: 1, Snack: "ice cream", StolenOn: "2026-07-04"},
		{GullID: 2, Snack: "chips", StolenOn: "2026-07-02"},
		{GullID: 3, Snack: "pasty", StolenOn: "2026-07-02"},
		{GullID: 3, Snack: "chips", StolenOn: "2026-07-05"},
		{GullID: 5, Snack: "sandwich", StolenOn: "2026-07-03"},
	}
	for _, theft := range thefts {
		if err := queries.RecordTheft(ctx, theft); err != nil {
			return fmt.Errorf("recording a theft: %w", err)
		}
	}

	family, err := queries.FamilyTree(ctx, 1)
	if err != nil {
		return fmt.Errorf("tracing Steven's family: %w", err)
	}
	fmt.Fprintln(output, "Steven's family:")
	for _, member := range family {
		fmt.Fprintf(output, "  generation %d  %-8s parent %s\n", member.Generation, member.Name, orNull(member.Parent))
	}

	league, err := queries.TheftLeague(ctx)
	if err != nil {
		return fmt.Errorf("ranking thieves: %w", err)
	}
	fmt.Fprintln(output, "theft league:")
	for _, entry := range league {
		fmt.Fprintf(output, "  %-13s #%d %-8s %d of %s\n", entry.Beach, entry.BeachRank, entry.Name, entry.Thefts, entry.BeachThefts)
	}

	records, err := queries.BeachRecords(ctx)
	if err != nil {
		return fmt.Errorf("reading beach records: %w", err)
	}
	fmt.Fprintln(output, "beach records:")
	for _, record := range records {
		fmt.Fprintf(output, "  %-13s gulls %d, thefts %d, last theft %s\n", record.Beach, record.Gulls, record.Thefts, orNull(record.LastTheft))
	}
	return nil
}

// orNull formats a nullable field, or returns NULL when the field is nil.
//
// Takes value (*T) which is a nullable field.
//
// Returns string which is the text to print.
func orNull[T any](value *T) string {
	if value == nil {
		return "NULL"
	}
	return fmt.Sprint(*value)
}

main.go

// Command 019_ctes_and_windows applies its migrations to an in-memory DuckDB database,
// then runs the example in example.go: a gull colony is read through a recursive CTE, a
// CTE feeding window functions, and a view.
//
// Run it with `go run .`, or `go run . -db example.duckdb` to keep the database in a
// file.
package main

import (
	"context"
	"database/sql"
	"embed"
	"flag"
	"fmt"
	"log"
	"os"

	_ "github.com/duckdb/duckdb-go/v2"

	"seagull.sh/seagull/sdk/migrate"
	"seagull.sh/seagull/sdk/migrate/migrate_backend_duckdb"
)

// migrations holds the migration files, compiled into the binary.
//
//go:embed db/migrations
var migrations embed.FS

func main() {
	path := flag.String("db", "", "DuckDB database file; an in-memory database when empty")
	flag.Parse()
	if err := execute(context.Background(), *path); err != nil {
		log.Fatal(err)
	}
}

// execute opens and migrates the DuckDB database at path, or an in-memory one when path
// is empty, and runs the example against it.
//
// Takes path (string) which is the database file, or empty.
//
// Returns error when the database cannot be prepared or the example fails.
func execute(ctx context.Context, path string) error {
	database, err := sql.Open("duckdb", path)
	if err != nil {
		return err
	}
	defer func() { _ = database.Close() }()

	migrator, err := migrate.NewMigrator(database, migrate_backend_duckdb.New(), migrations, "db/migrations")
	if err != nil {
		return err
	}
	if _, err := migrator.Up(ctx); err != nil {
		return fmt.Errorf("migrating: %w", err)
	}
	return run(ctx, database, os.Stdout)
}

internal/store/gulls.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"context"
	"math/big"
)

const beachRecordsSQL = `SELECT beach, gulls, thefts, last_theft FROM beach_records ORDER BY beach;`

type BeachRecordsRow struct {
	Beach     string  `json:"beach"`
	Gulls     int64   `json:"gulls"`
	Thefts    int64   `json:"thefts"`
	LastTheft *string `json:"last_theft"`
}

func (queries *Queries) BeachRecords(ctx context.Context) ([]BeachRecordsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, beachRecordsSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []BeachRecordsRow
	for rows.Next() {
		var row BeachRecordsRow
		if err := rows.Scan(&row.Beach, &row.Gulls, &row.Thefts, &row.LastTheft); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

const familyTreeSQL = `WITH RECURSIVE family AS (
    SELECT id, name, parent_id, 0 AS generation
    FROM gulls
    WHERE id = $1
    UNION ALL
    SELECT gulls.id, gulls.name, gulls.parent_id, family.generation + 1
    FROM gulls
    JOIN family ON gulls.parent_id = family.id
)
SELECT family.name, family.generation, parents.name AS parent
FROM family
LEFT JOIN gulls AS parents ON parents.id = family.parent_id
ORDER BY family.generation, family.id;`

type FamilyTreeRow struct {
	Name       string  `json:"name"`
	Generation int32   `json:"generation"`
	Parent     *string `json:"parent"`
}

func (queries *Queries) FamilyTree(ctx context.Context, id int64) ([]FamilyTreeRow, error) {
	rows, err := queries.reader.QueryContext(ctx, familyTreeSQL, id)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []FamilyTreeRow
	for rows.Next() {
		var row FamilyTreeRow
		if err := rows.Scan(&row.Name, &row.Generation, &row.Parent); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

const hatchGullSQL = `INSERT INTO gulls (name, parent_id, beach) VALUES ($1, $2, $3);`

type HatchGullParams struct {
	Name     string
	ParentID *int64
	Beach    string
}

func (queries *Queries) HatchGull(ctx context.Context, params HatchGullParams) error {
	_, err := queries.writer.ExecContext(ctx, hatchGullSQL, params.Name, params.ParentID, params.Beach)
	return err
}

const recordTheftSQL = `INSERT INTO thefts (gull_id, snack, stolen_on) VALUES ($1, $2, $3);`

type RecordTheftParams struct {
	GullID   int64
	Snack    string
	StolenOn string
}

func (queries *Queries) RecordTheft(ctx context.Context, params RecordTheftParams) error {
	_, err := queries.writer.ExecContext(ctx, recordTheftSQL, params.GullID, params.Snack, params.StolenOn)
	return err
}

const theftLeagueSQL = `WITH totals AS (
    SELECT gulls.name, gulls.beach, COUNT(thefts.id) AS thefts
    FROM gulls
    LEFT JOIN thefts ON thefts.gull_id = gulls.id
    GROUP BY gulls.id, gulls.name, gulls.beach
)
SELECT
    name,
    beach,
    thefts,
    RANK() OVER (PARTITION BY beach ORDER BY thefts DESC) AS beach_rank,
    SUM(thefts) OVER (PARTITION BY beach) AS beach_thefts
FROM totals
ORDER BY beach, beach_rank, name;`

type TheftLeagueRow struct {
	Name        string   `json:"name"`
	Beach       string   `json:"beach"`
	Thefts      int64    `json:"thefts"`
	BeachRank   int64    `json:"beach_rank"`
	BeachThefts *big.Int `json:"beach_thefts"`
}

func (queries *Queries) TheftLeague(ctx context.Context) ([]TheftLeagueRow, error) {
	rows, err := queries.reader.QueryContext(ctx, theftLeagueSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []TheftLeagueRow
	for rows.Next() {
		var row TheftLeagueRow
		if err := rows.Scan(&row.Name, &row.Beach, &row.Thefts, &row.BeachRank, &row.BeachThefts); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

internal/store/models.go

// Code generated by sgl. DO NOT EDIT.

package store

type Gulls struct {
	ID       int64  `json:"id"`
	Name     string `json:"name"`
	ParentID *int64 `json:"parent_id"`
	Beach    string `json:"beach"`
}

type Thefts struct {
	ID       int64  `json:"id"`
	GullID   int64  `json:"gull_id"`
	Snack    string `json:"snack"`
	StolenOn string `json:"stolen_on"`
}

internal/store/querier.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"context"
	"database/sql"
)

type DBTX interface {
	ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
	QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
	QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
}

type dbtxWrapper interface {
	WrapDBTX(inner any) any
}

type Queries struct {
	reader  DBTX
	writer  DBTX
	wrapper dbtxWrapper
}

func New(db DBTX) *Queries {
	wrapper, _ := db.(dbtxWrapper)
	return &Queries{reader: db, writer: db, wrapper: wrapper}
}

func NewWithReplica(writer DBTX, reader DBTX) *Queries {
	wrapper, _ := writer.(dbtxWrapper)
	return &Queries{reader: reader, writer: writer, wrapper: wrapper}
}

func (queries *Queries) WithTx(transaction *sql.Tx) *Queries {
	if queries == nil {
		return &Queries{reader: transaction, writer: transaction, wrapper: nil}
	}
	db := queries.wrapDBTX(transaction)
	return &Queries{reader: db, writer: db, wrapper: queries.wrapper}
}

func (queries *Queries) wrapDBTX(db DBTX) DBTX {
	if queries.wrapper == nil {
		return db
	}
	wrapped, ok := queries.wrapper.WrapDBTX(db).(DBTX)
	if !ok {
		return db
	}
	return wrapped
}

func (queries *Queries) RunInTx(ctx context.Context, db *sql.DB, fn func(*Queries) error) error {
	transaction, err := db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer transaction.Rollback()
	if err = fn(queries.WithTx(transaction)); err != nil {
		return err
	}
	return transaction.Commit()
}

output.txt

Steven's family:
  generation 0  Steven   parent NULL
  generation 1  Gwen     parent Steven
  generation 1  Jago     parent Steven
  generation 2  Kerensa  parent Gwen
theft league:
  Gwithian      #1 Morwenna 0 of 0
  Porthmeor     #1 Steven   3 of 4
  Porthmeor     #2 Gwen     1 of 4
  Porthmeor     #3 Kerensa  0 of 4
  Porthminster  #1 Jago     2 of 3
  Porthminster  #2 Tristan  1 of 3
beach records:
  Gwithian      gulls 1, thefts 0, last theft NULL
  Porthmeor     gulls 3, thefts 4, last theft 2026-07-04
  Porthminster  gulls 2, thefts 3, last theft 2026-07-05

Also on other engines

SQLiteA gull colony read through a recursive CTE, a CTE feeding RANK and SUM window functions, and a view, with types and nullability carried through each.PostgreSQLA gull colony read through a recursive CTE, a CTE feeding RANK and SUM window functions, and a view, with types and nullability carried through each; SUM of a bigint arrives as a decimal.CockroachDBA gull colony read through a recursive CTE, a CTE feeding RANK and SUM window functions, and a view, with types and nullability carried through each; SUM of a bigint arrives as a decimal.TimescaleDBA gull colony read through a recursive CTE, a CTE feeding RANK and SUM window functions, and a view, with types and nullability carried through each; SUM of a bigint arrives as a decimal.MySQLA gull colony read through a recursive CTE, a CTE feeding RANK and SUM window functions, and a view, with types and nullability carried through each.MariaDBA gull colony read through a recursive CTE, a CTE feeding RANK and SUM window functions, and a view, with types and nullability carried through each.ClickHouseA gull colony read through a recursive CTE, a CTE feeding rank() and sum() window functions, and a view, with types and nullability carried through each.