advancedsql

package
v0.1.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Oct 11, 2026 License: Apache-2.0 Imports: 0 Imported by: 0

README

advancedsql

An external catalogue of advanced PostgreSQL queries, translated into sqlb section by section. Six sections, six test files, 53 examples, and the section numbers in the test names are the catalogue's own so the examples can be read in the order they were written.

File Section
window1_test.go · window2_test.go Window functions, in two parts
cte_test.go Common table expressions
exercises_test.go Practice exercises, including islands-and-gaps
misc_test.go Miscellaneous notes and interview-style solutions
schema_test.go The tables the rest build against

The question it answers

The grammar catalogues answer "does this construct render". They cannot answer the one that matters: can this builder write a real analyst's query, rather than one row of a syntax table.

The finding is worth stating, because it is the kind of result that only counts if it was possible for it to come out badly. Every query here needed only constructs that already existed, plus AssertNotNull for a window result compared with a non-null column. Nothing was added to the library to make the translation work.

Translation rules, stated once

  • SELECT * and SELECT e.* bind the table's known columns. sqlb never emits a star, so a new column cannot silently change an old scan.
  • A window result the source compared with a non-null column goes through sqlb.AssertNotNull, with the run-time reason written beside it.
  • Two source statements are not valid SQL as written — a doubled OVER in 1.2, and a SELECT with no FROM in section 3. The tests render the statement the surrounding prose described, and say so in a comment.

Limits

These tests prove rendering and the compile-time checks. None of this SQL has run against a Postgres server from here; execution is contrib/driver-pgx's job, and the difference has already cost this repository eleven constructs that rendered cleanly and that Postgres rejects.

go test ./examples/pgsurface/advancedsql -v

Documentation

Overview

Package advancedsql translates an external catalogue of advanced PostgreSQL queries into sqlb and checks the rendered SQL.

The catalogue covered window functions in two parts, CTEs, a set of practice exercises including islands-and-gaps, miscellaneous notes, and a group of interview-style solutions. Its six sections are the six test files, and the section numbers in the test names are the catalogue's own, so the examples can be read in the order they were written.

The point of the package is a question the grammar catalogues cannot answer: can this builder write a real analyst's query rather than one row of a syntax table. Every query here needed only constructs that already existed, plus AssertNotNull for a window result compared with a non-null column.

Translation rules, stated once:

  • "select *" and "select e.*" bind the table's known columns. sqlb never emits a star, so a new column cannot silently change an old scan.
  • A window result that the source compared with a non-null column goes through sqlb.AssertNotNull, with the run-time reason beside it.
  • Two of the source statements are not valid SQL as written: a doubled OVER in 1.2, and a SELECT with no FROM in section 3. The tests render the statement the surrounding prose described and say so in a comment.

The tests prove rendering and compile-time checks. None of this SQL has run against a Postgres server from here.

Example (S1_1_maxOverAll)

1.1 the OVER clause with no parameter: a window of the whole table. 

show(sel().Columns(sqlb.Bindings(EmpBindings(&sink.emp), []sqlb.Binding{
	sqlb.Bind(sqlb.As(sqlb.Over(sqlb.Max(Salary), sqlb.NewWindow()), "max_salary"), &sink.i32),
})...).From(Employee).Build())

Output:
SELECT "e"."emp_id", "e"."emp_name", "e"."dept_name", "e"."salary", max("e"."salary") OVER () AS "max_salary" FROM "employee" AS "e"
Example (S1_1_maxOverPartition)

1.1 max salary per department beside every employee row. 

w := sqlb.NewWindow().PartitionBy(DeptName)
show(sel().Columns(sqlb.Bindings(EmpBindings(&sink.emp), []sqlb.Binding{
	sqlb.Bind(sqlb.As(sqlb.Over(sqlb.Max(Salary), w), "max_salary_per_dept"), &sink.i32),
})...).From(Employee).Build())

Output:
SELECT "e"."emp_id", "e"."emp_name", "e"."dept_name", "e"."salary", max("e"."salary") OVER (PARTITION BY "e"."dept_name") AS "max_salary_per_dept" FROM "employee" AS "e"
Example (S1_1_maxPerDept)

1.1 max salary per department with GROUP BY. 

show(sel().Columns(
	sqlb.Bind(DeptName, &sink.s),
	sqlb.Bind(sqlb.As(sqlb.Max(Salary), "max_salary_per_department"), &sink.i32),
).From(Employee).GroupBy(DeptName).Build())

Output:
SELECT "e"."dept_name", max("e"."salary") AS "max_salary_per_department" FROM "employee" AS "e" GROUP BY "e"."dept_name"
Example (S1_1_maxSalary)

1.1 max salary over the table. 

show(sel().Columns(sqlb.Bind(sqlb.As(sqlb.Max(Salary), "max_salary"), &sink.i32)).From(Employee).Build())

Output:
SELECT max("e"."salary") AS "max_salary" FROM "employee" AS "e"
Example (S1_2_empsalaryRank)

1.2 rank per department by salary, the Postgres manual's example. 

w := sqlb.NewWindow().PartitionBy(ESDepname).OrderBy(sqlb.OrderBy(ESSalary, sqlb.SortDesc))
show(sel().Columns(
	sqlb.Bind(ESDepname, &sink.s), sqlb.Bind(ESEmpno, &sink.i32), sqlb.Bind(ESSalary, &sink.i32),
	sqlb.Bind(sqlb.Rank().Over(w), &sink.i64),
).From(EmpSalary).Build())

Output:
SELECT "es"."depname", "es"."empno", "es"."salary", rank() OVER (PARTITION BY "es"."depname" ORDER BY "es"."salary" DESC) FROM "empsalary" AS "es"
Example (S1_2_firstTwoPerDept)

1.2 the first two employees per department, through a derived table. 

w := sqlb.NewWindow().PartitionBy(DeptName).OrderBy(sqlb.OrderBy(EmpID, sqlb.SortAsc))
inner := sel().Exprs(EmpID, EmpName, DeptName, Salary, sqlb.As(sqlb.RowNumber().Over(w), "rn")).From(Employee)
sq := sqlb.Derived(inner, "sq")
show(sel().Columns(
	sqlb.Bind(cteCol[int32](sq, "emp_id"), &sink.i32),
	sqlb.Bind(cteCol[string](sq, "emp_name"), &sink.s),
	sqlb.Bind(cteCol[string](sq, "dept_name"), &sink.s2),
	sqlb.Bind(cteCol[int32](sq, "salary"), &sink.i32),
	sqlb.Bind(cteCol[int64](sq, "rn"), &sink.i64),
).From(sq).Where(sqlb.Less(cteCol[int64](sq, "rn"), 3)).Build())

Output:
SELECT "sq"."emp_id", "sq"."emp_name", "sq"."dept_name", "sq"."salary", "sq"."rn" FROM (SELECT "e"."emp_id", "e"."emp_name", "e"."dept_name", "e"."salary", row_number() OVER (PARTITION BY "e"."dept_name" ORDER BY "e"."emp_id" ASC) AS "rn" FROM "employee" AS "e") AS "sq" WHERE ("sq"."rn" < $1)
args: [3]
Example (S1_2_rowNumberAll)

1.2 row_number over the whole table. 

show(sel().Columns(sqlb.Bindings(EmpBindings(&sink.emp), []sqlb.Binding{
	sqlb.Bind(sqlb.As(sqlb.RowNumber().Over(sqlb.NewWindow()), "rn"), &sink.i64),
})...).From(Employee).Build())

Output:
SELECT "e"."emp_id", "e"."emp_name", "e"."dept_name", "e"."salary", row_number() OVER () AS "rn" FROM "employee" AS "e"
Example (S1_2_rowNumberOrdered)

1.2 row_number ordered inside the partition. 

w := sqlb.NewWindow().PartitionBy(DeptName).OrderBy(sqlb.OrderBy(EmpID, sqlb.SortAsc))
show(sel().Columns(sqlb.Bindings(EmpBindings(&sink.emp), []sqlb.Binding{
	sqlb.Bind(sqlb.As(sqlb.RowNumber().Over(w), "rn"), &sink.i64),
})...).From(Employee).Build())

Output:
SELECT "e"."emp_id", "e"."emp_name", "e"."dept_name", "e"."salary", row_number() OVER (PARTITION BY "e"."dept_name" ORDER BY "e"."emp_id" ASC) AS "rn" FROM "employee" AS "e"
Example (S1_2_rowNumberPerDept)

1.2 row_number per department. The README writes "over(partition by dept_name) over()", which is not valid SQL; this is the statement the prose describes. 

show(sel().Columns(sqlb.Bindings(EmpBindings(&sink.emp), []sqlb.Binding{
	sqlb.Bind(sqlb.As(sqlb.RowNumber().Over(sqlb.NewWindow().PartitionBy(DeptName)), "rn"), &sink.i64),
})...).From(Employee).Build())

Output:
SELECT "e"."emp_id", "e"."emp_name", "e"."dept_name", "e"."salary", row_number() OVER (PARTITION BY "e"."dept_name") AS "rn" FROM "employee" AS "e"
Example (S1_3_namedWindow)

1.3 the same through a named window. 

w := sqlb.NewWindow().PartitionBy(DeptName).OrderBy(sqlb.OrderBy(Salary, sqlb.SortDesc))
show(sel().Columns(sqlb.Bindings(EmpBindings(&sink.emp), []sqlb.Binding{
	sqlb.Bind(sqlb.As(sqlb.Rank().OverNamed("w"), "rnk"), &sink.i64),
	sqlb.Bind(sqlb.As(sqlb.DenseRank().OverNamed("w"), "dense_rnk"), &sink.i64),
})...).From(Employee).Window("w", w).Build())

Output:
SELECT "e"."emp_id", "e"."emp_name", "e"."dept_name", "e"."salary", rank() OVER "w" AS "rnk", dense_rank() OVER "w" AS "dense_rnk" FROM "employee" AS "e" WINDOW "w" AS (PARTITION BY "e"."dept_name" ORDER BY "e"."salary" DESC)
Example (S1_3_rank)

1.3 rank by salary within department. 

w := sqlb.NewWindow().PartitionBy(DeptName).OrderBy(sqlb.OrderBy(Salary, sqlb.SortDesc))
show(sel().Columns(sqlb.Bindings(EmpBindings(&sink.emp), []sqlb.Binding{
	sqlb.Bind(sqlb.As(sqlb.Rank().Over(w), "rnk"), &sink.i64),
})...).From(Employee).Build())

Output:
SELECT "e"."emp_id", "e"."emp_name", "e"."dept_name", "e"."salary", rank() OVER (PARTITION BY "e"."dept_name" ORDER BY "e"."salary" DESC) AS "rnk" FROM "employee" AS "e"
Example (S1_3_rankAndDenseRank)

1.3 rank and dense_rank side by side. 

w := sqlb.NewWindow().PartitionBy(DeptName).OrderBy(sqlb.OrderBy(Salary, sqlb.SortDesc))
show(sel().Columns(sqlb.Bindings(EmpBindings(&sink.emp), []sqlb.Binding{
	sqlb.Bind(sqlb.As(sqlb.Rank().Over(w), "rnk"), &sink.i64),
	sqlb.Bind(sqlb.As(sqlb.DenseRank().Over(w), "dense_rnk"), &sink.i64),
})...).From(Employee).Build())

Output:
SELECT "e"."emp_id", "e"."emp_name", "e"."dept_name", "e"."salary", rank() OVER (PARTITION BY "e"."dept_name" ORDER BY "e"."salary" DESC) AS "rnk", dense_rank() OVER (PARTITION BY "e"."dept_name" ORDER BY "e"."salary" DESC) AS "dense_rnk" FROM "employee" AS "e"
Example (S1_3_sumAvgOverNamed)

1.3 aggregates over a named window, from the Postgres manual. 

w := sqlb.NewWindow().PartitionBy(ESDepname).OrderBy(sqlb.OrderBy(ESSalary, sqlb.SortDesc))
show(sel().Columns(
	sqlb.Bind(sqlb.OverNamed(sqlb.Sum(ESSalary), "w"), &sink.i32),
	sqlb.Bind(sqlb.OverNamed(sqlb.Avg(ESSalary), "w"), &sink.f64),
).From(EmpSalary).Window("w", w).Build())

Output:
SELECT sum("es"."salary") OVER "w", avg("es"."salary") OVER "w" FROM "empsalary" AS "es" WINDOW "w" AS (PARTITION BY "es"."depname" ORDER BY "es"."salary" DESC)
Example (S1_3_topThree)

1.3 top three salaries per department. 

w := sqlb.NewWindow().PartitionBy(DeptName).OrderBy(sqlb.OrderBy(Salary, sqlb.SortDesc))
inner := sel().Exprs(EmpID, EmpName, DeptName, Salary, sqlb.As(sqlb.Rank().Over(w), "rnk")).From(Employee)
sq := sqlb.Derived(inner, "sq")
rnk := cteCol[int64](sq, "rnk")
show(sel().Columns(
	sqlb.Bind(cteCol[string](sq, "emp_name"), &sink.s),
	sqlb.Bind(rnk, &sink.i64),
).From(sq).Where(sqlb.Less(rnk, 4)).Build())

Output:
SELECT "sq"."emp_name", "sq"."rnk" FROM (SELECT "e"."emp_id", "e"."emp_name", "e"."dept_name", "e"."salary", rank() OVER (PARTITION BY "e"."dept_name" ORDER BY "e"."salary" DESC) AS "rnk" FROM "employee" AS "e") AS "sq" WHERE ("sq"."rnk" < $1)
args: [4]
Example (S1_4_lag)

1.4 lag: the previous employee's salary, nullable on the first row. 

w := sqlb.NewWindow().PartitionBy(DeptName).OrderBy(sqlb.OrderBy(EmpID, sqlb.SortAsc))
show(sel().Columns(sqlb.Bindings(EmpBindings(&sink.emp), []sqlb.Binding{
	sqlb.Bind(sqlb.As(pg.Lag(Salary, 1).Over(w), "prev_emp_salary"), &sink.i32p),
})...).From(Employee).Build())

Output:
SELECT "e"."emp_id", "e"."emp_name", "e"."dept_name", "e"."salary", lag("e"."salary", $1) OVER (PARTITION BY "e"."dept_name" ORDER BY "e"."emp_id" ASC) AS "prev_emp_salary" FROM "employee" AS "e"
args: [1]
Example (S1_4_lagCase)

1.4 CASE over lag comparisons. The lag result is *int32, so the salary column is reinterpreted with Nullable for the comparison; the CASE has no ELSE, so its result is *string. 

w := sqlb.NewWindow().PartitionBy(DeptName).OrderBy(sqlb.OrderBy(EmpID, sqlb.SortAsc))
prev := pg.Lag(Salary, 1).OverNamed("w")
salRange := sqlb.Case[string]().
	When(sqlb.GreaterExpr(sqlb.Nullable(Salary), prev), sqlb.Param("Higher than previous employee")).
	When(sqlb.LessExpr(sqlb.Nullable(Salary), prev), sqlb.Param("Lower than previous employee")).
	When(sqlb.EqExpr(sqlb.Nullable(Salary), prev), sqlb.Param("Same as previous employee")).
	End()
show(sel().Columns(sqlb.Bindings(EmpBindings(&sink.emp), []sqlb.Binding{
	sqlb.Bind(sqlb.As(prev, "prev_emp_salary"), &sink.i32p),
	sqlb.Bind(sqlb.As(salRange, "sal_range"), &sink.sp),
})...).From(Employee).Window("w", w).Build())

Output:
SELECT "e"."emp_id", "e"."emp_name", "e"."dept_name", "e"."salary", lag("e"."salary", $1) OVER "w" AS "prev_emp_salary", CASE WHEN "e"."salary" > lag("e"."salary", $2) OVER "w" THEN $3 WHEN "e"."salary" < lag("e"."salary", $4) OVER "w" THEN $5 WHEN "e"."salary" = lag("e"."salary", $6) OVER "w" THEN $7 END AS "sal_range" FROM "employee" AS "e" WINDOW "w" AS (PARTITION BY "e"."dept_name" ORDER BY "e"."emp_id" ASC)
args: [1 1 Higher than previous employee 1 Lower than previous employee 1 Same as previous employee]
Example (S1_4_lagWithDefault)

1.4 lag by two rows with a default, so the result is not null. 

w := sqlb.NewWindow().PartitionBy(DeptName).OrderBy(sqlb.OrderBy(EmpID, sqlb.SortAsc))
show(sel().Columns(sqlb.Bindings(EmpBindings(&sink.emp), []sqlb.Binding{
	sqlb.Bind(sqlb.As(pg.LagDefault(Salary, 2, sqlb.Param[int32](0)).Over(w), "prev_emp_salary"), &sink.i32),
})...).From(Employee).Build())

Output:
SELECT "e"."emp_id", "e"."emp_name", "e"."dept_name", "e"."salary", lag("e"."salary", $1, $2) OVER (PARTITION BY "e"."dept_name" ORDER BY "e"."emp_id" ASC) AS "prev_emp_salary" FROM "employee" AS "e"
args: [2 0]
Example (S1_4_lead)

1.4 lead. 

w := sqlb.NewWindow().PartitionBy(DeptName).OrderBy(sqlb.OrderBy(EmpID, sqlb.SortAsc))
show(sel().Columns(sqlb.Bindings(EmpBindings(&sink.emp), []sqlb.Binding{
	sqlb.Bind(sqlb.As(pg.Lead(Salary, 1).Over(w), "next_emp_salary"), &sink.i32p),
})...).From(Employee).Build())

Output:
SELECT "e"."emp_id", "e"."emp_name", "e"."dept_name", "e"."salary", lead("e"."salary", $1) OVER (PARTITION BY "e"."dept_name" ORDER BY "e"."emp_id" ASC) AS "next_emp_salary" FROM "employee" AS "e"
args: [1]
Example (S1_4_paymentCase)

1.4 the payment variant with an IS NULL branch and an ELSE. 

w := sqlb.NewWindow().PartitionBy(PayCustomer).OrderBy(sqlb.OrderBy(PayDate, sqlb.SortAsc))
prev := pg.Lag(PayAmount, 1).OverNamed("w")
amountRange := sqlb.Case[string]().
	When(sqlb.GreaterExpr(sqlb.Nullable(PayAmount), prev), sqlb.Param("Higher than previous amount")).
	When(sqlb.LessExpr(sqlb.Nullable(PayAmount), prev), sqlb.Param("Lower than previous amount")).
	When(sqlb.IsNull(prev), sqlb.Param("No info")).
	Else(sqlb.Param("The same as previous amount"))
show(sel().Columns(
	sqlb.Bind(PayID, &sink.i32),
	sqlb.Bind(sqlb.As(PayAmount, "current_amount"), &sink.f64),
	sqlb.Bind(sqlb.As(prev, "prev_amount"), &sink.f64p),
	sqlb.Bind(sqlb.As(amountRange, "amount_range"), &sink.s),
).From(Payment).Window("w", w).Build())

Output:
SELECT "p"."payment_id", "p"."amount" AS "current_amount", lag("p"."amount", $1) OVER "w" AS "prev_amount", CASE WHEN "p"."amount" > lag("p"."amount", $2) OVER "w" THEN $3 WHEN "p"."amount" < lag("p"."amount", $4) OVER "w" THEN $5 WHEN lag("p"."amount", $6) OVER "w" IS NULL THEN $7 ELSE $8 END AS "amount_range" FROM "payment" AS "p" WINDOW "w" AS (PARTITION BY "p"."customer_id" ORDER BY "p"."payment_date" ASC)
args: [1 1 Higher than previous amount 1 Lower than previous amount 1 No info The same as previous amount]
Example (S2_1_firstValue)

2.1 first_value: the most expensive product per category. 

show(sel().Columns(sqlb.Bindings(ProdBindings(&sink.prod), []sqlb.Binding{
	sqlb.Bind(sqlb.As(sqlb.FirstValue(ProdName).Over(byPriceDesc), "most_exp_per_category"), &sink.sp),
})...).From(Product).Build())

Output:
SELECT "p"."product_name", "p"."product_category", "p"."brand", "p"."price", first_value("p"."product_name") OVER (PARTITION BY "p"."product_category" ORDER BY "p"."price" DESC) AS "most_exp_per_category" FROM "product" AS "p"
Example (S2_1_lastValueDefaultFrame)

2.1 last_value with the default frame, which is the trap the next section fixes. 

show(sel().Columns(sqlb.Bindings(ProdBindings(&sink.prod), []sqlb.Binding{
	sqlb.Bind(sqlb.As(sqlb.LastValue(ProdName).Over(byPriceDesc), "least_exp_product"), &sink.sp),
})...).From(Product).Build())

Output:
SELECT "p"."product_name", "p"."product_category", "p"."brand", "p"."price", last_value("p"."product_name") OVER (PARTITION BY "p"."product_category" ORDER BY "p"."price" DESC) AS "least_exp_product" FROM "product" AS "p"
Example (S2_1_lowestPaymentCTE)

2.1 the lowest payment per customer through a CTE and rank = 1. 

w := sqlb.NewWindow().PartitionBy(PayCustomer).OrderBy(sqlb.OrderBy(PayAmount, sqlb.SortAsc))
body := sel().Exprs(PayID, PayCustomer, PayAmount, sqlb.As(sqlb.Rank().Over(w), "rank")).From(Payment)
rk := sqlb.Table{Name: "rk", Alias: "rk"}
rank := sqlb.Col[int64](rk, "rank")
customer := sqlb.Col[int32](rk, "customer_id")
show(sel().With("rk", body).Columns(
	sqlb.Bind(sqlb.Col[int32](rk, "payment_id"), &sink.i32),
	sqlb.Bind(customer, &sink.i32),
	sqlb.Bind(sqlb.Col[float64](rk, "amount"), &sink.f64),
	sqlb.Bind(rank, &sink.i64),
).From(rk).Where(sqlb.Eq(rank, 1)).Order(sqlb.OrderBy(customer, sqlb.SortAsc)).Build())

Output:
WITH "rk" AS (SELECT "p"."payment_id", "p"."customer_id", "p"."amount", rank() OVER (PARTITION BY "p"."customer_id" ORDER BY "p"."amount" ASC) AS "rank" FROM "payment" AS "p") SELECT "rk"."payment_id", "rk"."customer_id", "rk"."amount", "rk"."rank" FROM "rk" AS "rk" WHERE ("rk"."rank" = $1) ORDER BY "rk"."customer_id" ASC
args: [1]
Example (S2_2_frames)

2.2 frame clauses on last_value. 

frames := []sqlb.Window{
	byPriceDesc.Frame(sqlb.FrameRange, sqlb.UnboundedPreceding(), sqlb.CurrentRow()),
	byPriceDesc.Frame(sqlb.FrameRange, sqlb.UnboundedPreceding(), sqlb.UnboundedFollowing()),
	byPriceDesc.Frame(sqlb.FrameRows, sqlb.UnboundedPreceding(), sqlb.UnboundedFollowing()),
	byPriceDesc.Frame(sqlb.FrameRows, sqlb.Preceding(2), sqlb.Following(2)),
}
for _, w := range frames {
	show(sel().Columns(sqlb.Bindings(ProdBindings(&sink.prod), []sqlb.Binding{
		sqlb.Bind(sqlb.LastValue(ProdName).Over(w), &sink.sp),
	})...).From(Product).Build())
}

Output:
SELECT "p"."product_name", "p"."product_category", "p"."brand", "p"."price", last_value("p"."product_name") OVER (PARTITION BY "p"."product_category" ORDER BY "p"."price" DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM "product" AS "p"
SELECT "p"."product_name", "p"."product_category", "p"."brand", "p"."price", last_value("p"."product_name") OVER (PARTITION BY "p"."product_category" ORDER BY "p"."price" DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM "product" AS "p"
SELECT "p"."product_name", "p"."product_category", "p"."brand", "p"."price", last_value("p"."product_name") OVER (PARTITION BY "p"."product_category" ORDER BY "p"."price" DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM "product" AS "p"
SELECT "p"."product_name", "p"."product_category", "p"."brand", "p"."price", last_value("p"."product_name") OVER (PARTITION BY "p"."product_category" ORDER BY "p"."price" DESC ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) FROM "product" AS "p"
Example (S2_2_lowestPaymentFrame)

2.2 the lowest amount per customer with an explicit full frame. 

w := sqlb.NewWindow().PartitionBy(PayCustomer).OrderBy(sqlb.OrderBy(PayAmount, sqlb.SortDesc)).
	Frame(sqlb.FrameRange, sqlb.UnboundedPreceding(), sqlb.UnboundedFollowing())
show(sel().Columns(
	sqlb.Bind(PayID, &sink.i32), sqlb.Bind(PayCustomer, &sink.i32), sqlb.Bind(PayAmount, &sink.f64),
	sqlb.Bind(sqlb.As(sqlb.LastValue(PayAmount).Over(w), "lowest_payment"), &sink.f64p),
).From(Payment).Build())

Output:
SELECT "p"."payment_id", "p"."customer_id", "p"."amount", last_value("p"."amount") OVER (PARTITION BY "p"."customer_id" ORDER BY "p"."amount" DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "lowest_payment" FROM "payment" AS "p"
Example (S2_3_namedWindowWithFrame)

2.3 one named window with a frame, used by first_value and last_value. 

w := byPriceDesc.Frame(sqlb.FrameRange, sqlb.UnboundedPreceding(), sqlb.UnboundedFollowing())
show(sel().Columns(sqlb.Bindings(ProdBindings(&sink.prod), []sqlb.Binding{
	sqlb.Bind(sqlb.As(sqlb.FirstValue(ProdName).OverNamed("w"), "most_exp_product"), &sink.sp),
	sqlb.Bind(sqlb.As(sqlb.LastValue(ProdName).OverNamed("w"), "least_exp_product"), &sink.sp2),
})...).From(Product).Window("w", w).Build())

Output:
SELECT "p"."product_name", "p"."product_category", "p"."brand", "p"."price", first_value("p"."product_name") OVER "w" AS "most_exp_product", last_value("p"."product_name") OVER "w" AS "least_exp_product" FROM "product" AS "p" WINDOW "w" AS (PARTITION BY "p"."product_category" ORDER BY "p"."price" DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
Example (S2_4_nthValue)

2.4 nth_value with the default frame and with a named full frame. 

show(sel().Columns(sqlb.Bindings(ProdBindings(&sink.prod), []sqlb.Binding{
	sqlb.Bind(sqlb.As(sqlb.NthValue(ProdName, 2).Over(byPriceDesc), "second_most_expensive"), &sink.sp),
})...).From(Product).Build())
w := byPriceDesc.Frame(sqlb.FrameRange, sqlb.UnboundedPreceding(), sqlb.UnboundedFollowing())
show(sel().Columns(sqlb.Bindings(ProdBindings(&sink.prod), []sqlb.Binding{
	sqlb.Bind(sqlb.As(sqlb.NthValue(ProdName, 2).OverNamed("w"), "second_most_expensive"), &sink.sp),
})...).From(Product).Window("w", w).Build())

Output:
SELECT "p"."product_name", "p"."product_category", "p"."brand", "p"."price", nth_value("p"."product_name", $1) OVER (PARTITION BY "p"."product_category" ORDER BY "p"."price" DESC) AS "second_most_expensive" FROM "product" AS "p"
args: [2]
SELECT "p"."product_name", "p"."product_category", "p"."brand", "p"."price", nth_value("p"."product_name", $1) OVER "w" AS "second_most_expensive" FROM "product" AS "p" WINDOW "w" AS (PARTITION BY "p"."product_category" ORDER BY "p"."price" DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
args: [2]
Example (S2_5_ntile)

2.5 ntile into three buckets, labelled in the outer query. 

body := sel().Exprs(ProdName, ProdCat, ProdPrice, sqlb.As(pg.Ntile(3).Over(sqlb.NewWindow().OrderBy(sqlb.OrderBy(ProdPrice, sqlb.SortDesc))), "bucket")).
	From(Product).Where(sqlb.Eq(ProdCat, "Phone"))
pb := sqlb.Table{Name: "phone_bucket", Alias: "phone_bucket"}
bucket := sqlb.Col[int32](pb, "bucket")
label := sqlb.Case[string]().
	When(sqlb.Eq(bucket, 1), sqlb.Param("Expensive Phone")).
	When(sqlb.Eq(bucket, 2), sqlb.Param("Mid Phone")).
	When(sqlb.Eq(bucket, 3), sqlb.Param("Cheaper Phone")).
	End()
show(sel().With("phone_bucket", body).Columns(
	sqlb.Bind(sqlb.Col[string](pb, "product_name"), &sink.s),
	sqlb.Bind(label, &sink.sp),
).From(pb).Build())

Output:
WITH "phone_bucket" AS (SELECT "p"."product_name", "p"."product_category", "p"."price", ntile($1) OVER (ORDER BY "p"."price" DESC) AS "bucket" FROM "product" AS "p" WHERE ("p"."product_category" = $2)) SELECT "phone_bucket"."product_name", CASE WHEN "phone_bucket"."bucket" = $3 THEN $4 WHEN "phone_bucket"."bucket" = $5 THEN $6 WHEN "phone_bucket"."bucket" = $7 THEN $8 END FROM "phone_bucket" AS "phone_bucket"
args: [3 Phone 1 Expensive Phone 2 Mid Phone 3 Cheaper Phone]
Example (S2_6_cumeDist)

2.6 cume_dist, raw and rounded to a percentage. 

w := sqlb.NewWindow().OrderBy(sqlb.OrderBy(ProdPrice, sqlb.SortDesc))
show(sel().Columns(sqlb.Bindings(ProdBindings(&sink.prod), []sqlb.Binding{
	sqlb.Bind(sqlb.As(pg.CumeDist().Over(w), "cume_dist_by_price"), &sink.f64),
})...).From(Product).Build())
pct := pg.Round(sqlb.Mul(pg.CumeDist().Over(w), sqlb.Param(100.0)), 2)
show(sel().Columns(sqlb.Bindings(ProdBindings(&sink.prod), []sqlb.Binding{
	sqlb.Bind(sqlb.As(pct, "cume_dist_by_price"), &sink.f64),
})...).From(Product).Build())

Output:
SELECT "p"."product_name", "p"."product_category", "p"."brand", "p"."price", cume_dist() OVER (ORDER BY "p"."price" DESC) AS "cume_dist_by_price" FROM "product" AS "p"
SELECT "p"."product_name", "p"."product_category", "p"."brand", "p"."price", round((cume_dist() OVER (ORDER BY "p"."price" DESC) * $1)::numeric, $2) AS "cume_dist_by_price" FROM "product" AS "p"
args: [100 2]
Example (S2_6_cumeDistCTE)

2.6 the top 30 percent by price, with a percent sign appended. 

w := sqlb.NewWindow().OrderBy(sqlb.OrderBy(ProdPrice, sqlb.SortDesc))
pct := pg.Round(sqlb.Mul(pg.CumeDist().Over(w), sqlb.Param(100.0)), 2)
body := sel().Exprs(ProdName, ProdCat, ProdPrice, sqlb.As(pct, "cume_dist_by_price")).From(Product)
cd := sqlb.Table{Name: "cume_dist_t", Alias: "cume_dist_t"}
cdPct := sqlb.Col[float64](cd, "cume_dist_by_price")
show(sel().With("cume_dist_t", body).Columns(
	sqlb.Bind(sqlb.Col[string](cd, "product_name"), &sink.s),
	sqlb.Bind(sqlb.As(pg.ConcatOp(sqlb.Cast[string](cdPct, "text"), sqlb.Param("%")), "cume_dist_pct"), &sink.s2),
).From(cd).Where(sqlb.LessEq(cdPct, 30)).Build())

Output:
WITH "cume_dist_t" AS (SELECT "p"."product_name", "p"."product_category", "p"."price", round((cume_dist() OVER (ORDER BY "p"."price" DESC) * $1)::numeric, $2) AS "cume_dist_by_price" FROM "product" AS "p") SELECT "cume_dist_t"."product_name", (CAST("cume_dist_t"."cume_dist_by_price" AS text) || $3) AS "cume_dist_pct" FROM "cume_dist_t" AS "cume_dist_t" WHERE ("cume_dist_t"."cume_dist_by_price" <= $4)
args: [100 2 % 30]
Example (S2_7_percentRank)

2.7 percent_rank, rounded, and looked up for one product. 

w := sqlb.NewWindow().OrderBy(sqlb.OrderBy(ProdPrice, sqlb.SortAsc))
pct := pg.Round(sqlb.Mul(pg.PercentRank().Over(w), sqlb.Param(100.0)), 2)
show(sel().Columns(sqlb.Bindings(ProdBindings(&sink.prod), []sqlb.Binding{
	sqlb.Bind(sqlb.As(pct, "pct_rank"), &sink.f64),
})...).From(Product).Build())
body := sel().Exprs(ProdName, ProdCat, ProdPrice, sqlb.As(pct, "pct_rank")).From(Product)
pr := sqlb.Table{Name: "pct_rank", Alias: "pct_rank"}
name := sqlb.Col[string](pr, "product_name")
show(sel().With("pct_rank", body).Columns(
	sqlb.Bind(name, &sink.s),
	sqlb.Bind(sqlb.Col[float64](pr, "pct_rank"), &sink.f64),
).From(pr).Where(sqlb.Eq(name, "Galaxy Z Fold 3")).Build())

Output:
SELECT "p"."product_name", "p"."product_category", "p"."brand", "p"."price", round((percent_rank() OVER (ORDER BY "p"."price" ASC) * $1)::numeric, $2) AS "pct_rank" FROM "product" AS "p"
args: [100 2]
WITH "pct_rank" AS (SELECT "p"."product_name", "p"."product_category", "p"."price", round((percent_rank() OVER (ORDER BY "p"."price" ASC) * $1)::numeric, $2) AS "pct_rank" FROM "product" AS "p") SELECT "pct_rank"."product_name", "pct_rank"."pct_rank" FROM "pct_rank" AS "pct_rank" WHERE ("pct_rank"."product_name" = $3)
args: [100 2 Galaxy Z Fold 3]
Example (S3_aboveAverage)

3 employees above the average salary. The README compares "salary > average_salary" against an unnamed column; here the CTE column is named. 

body := sel().Exprs(sqlb.As(sqlb.Cast[int32](sqlb.Avg(Salary), "int"), "avg_salary")).From(Employee)
avg := sqlb.Table{Name: "average_salary", Alias: "average_salary"}
show(sel().With("average_salary", body).
	Columns(EmpBindings(&sink.emp)...).
	From(Employee).
	Join(sqlb.Join{Type: sqlb.JoinCross, Table: avg}).
	Where(sqlb.GreaterExpr(Salary, sqlb.Col[int32](avg, "avg_salary"))).Build())

Output:
WITH "average_salary" AS (SELECT CAST(avg("e"."salary") AS int) AS "avg_salary" FROM "employee" AS "e") SELECT "e"."emp_id", "e"."emp_name", "e"."dept_name", "e"."salary" FROM "employee" AS "e" CROSS JOIN "average_salary" AS "average_salary" WHERE ("e"."salary" > "average_salary"."avg_salary")
Example (S3_revenueChain)

3 a three-CTE chain over payments, with an ORDER BY inside a CTE body. 

rev := sqlb.Table{Name: "revenue", Alias: "revenue"}
revCustomer := sqlb.Col[int32](rev, "customer_id")
revRevenue := sqlb.Col[float64](rev, "revenue")
rps := sqlb.Table{Name: "revenue_per_store", Alias: "revenue_per_store"}
rpsCustomer := sqlb.Col[int32](rps, "customer_id")
rpsRevenue := sqlb.Col[float64](rps, "revenue_per_store")
avg := sqlb.Table{Name: "avg_revenue", Alias: "avg_revenue"}
avgRevenue := sqlb.Col[float64](avg, "avg_revenue")
show(sel().
	WithCTE(sqlb.CTE{Name: "revenue", Columns: []string{"customer_id", "revenue"},
		Body: sel().Exprs(PayCustomer, sqlb.As(sqlb.Mul(sqlb.Cast[float64](PayRental, "float8"), PayAmount), "revenue")).From(Payment)}).
	WithCTE(sqlb.CTE{Name: "revenue_per_store", Columns: []string{"customer_id", "revenue_per_store"},
		Body: sel().Exprs(revCustomer, sqlb.As(sqlb.Sum(revRevenue), "revenue_per_store")).From(rev).GroupBy(revCustomer).Order(sqlb.OrderBy(revCustomer, sqlb.SortAsc))}).
	WithCTE(sqlb.CTE{Name: "avg_revenue", Columns: []string{"avg_revenue"},
		Body: sel().Exprs(sqlb.As(pg.Round(sqlb.Avg(rpsRevenue), 2), "avg_revenue")).From(rps)}).
	Columns(sqlb.Bind(rpsCustomer, &sink.i32), sqlb.Bind(rpsRevenue, &sink.f64)).
	From(rps).Join(sqlb.Join{Type: sqlb.JoinCross, Table: avg}).
	Where(sqlb.GreaterExpr(rpsRevenue, avgRevenue)).Build())

Output:
WITH "revenue" ("customer_id", "revenue") AS (SELECT "p"."customer_id", (CAST("p"."rental_id" AS float8) * "p"."amount") AS "revenue" FROM "payment" AS "p"), "revenue_per_store" ("customer_id", "revenue_per_store") AS (SELECT "revenue"."customer_id", sum("revenue"."revenue") AS "revenue_per_store" FROM "revenue" AS "revenue" GROUP BY "revenue"."customer_id" ORDER BY "revenue"."customer_id" ASC), "avg_revenue" ("avg_revenue") AS (SELECT round(avg("revenue_per_store"."revenue_per_store")::numeric, $1) AS "avg_revenue" FROM "revenue_per_store" AS "revenue_per_store") SELECT "revenue_per_store"."customer_id", "revenue_per_store"."revenue_per_store" FROM "revenue_per_store" AS "revenue_per_store" CROSS JOIN "avg_revenue" AS "avg_revenue" WHERE ("revenue_per_store"."revenue_per_store" > "avg_revenue"."avg_revenue")
args: [2]
Example (S3_salesCTEs)

3 the same with two CTEs that name their columns. The README's final SELECT has no FROM; this is the statement the prose describes. 

sps := sqlb.Table{Name: "sales_per_store", Alias: "sales_per_store"}
spsSales := sqlb.Col[float64](sps, "sales_per_store")
avg := sqlb.Table{Name: "avg_sales", Alias: "avg_sales"}
avgSales := sqlb.Col[float64](avg, "avg_sales")
show(sel().
	WithCTE(sqlb.CTE{Name: "sales_per_store", Columns: []string{"store_id", "sales_per_store"},
		Body: sel().Exprs(SalesStore, sqlb.As(sqlb.Sum(SalesCost), "sales_per_store")).From(Sales).GroupBy(SalesStore)}).
	WithCTE(sqlb.CTE{Name: "avg_sales", Columns: []string{"avg_sales"},
		Body: sel().Exprs(sqlb.As(pg.Round(sqlb.Avg(spsSales), 2), "avg_sales")).From(sps)}).
	Columns(sqlb.Bind(sqlb.Col[int32](sps, "store_id"), &sink.i32), sqlb.Bind(spsSales, &sink.f64)).
	From(sps).Join(sqlb.Join{Type: sqlb.JoinCross, Table: avg}).
	Where(sqlb.GreaterExpr(spsSales, avgSales)).Build())

Output:
WITH "sales_per_store" ("store_id", "sales_per_store") AS (SELECT "s"."store_id", sum("s"."cost") AS "sales_per_store" FROM "sales" AS "s" GROUP BY "s"."store_id"), "avg_sales" ("avg_sales") AS (SELECT round(avg("sales_per_store"."sales_per_store")::numeric, $1) AS "avg_sales" FROM "sales_per_store" AS "sales_per_store") SELECT "sales_per_store"."store_id", "sales_per_store"."sales_per_store" FROM "sales_per_store" AS "sales_per_store" CROSS JOIN "avg_sales" AS "avg_sales" WHERE ("sales_per_store"."sales_per_store" > "avg_sales"."avg_sales")
args: [2]
Example (S3_salesDerivedTables)

3 the same problem three ways: sales per store, the average, and the comparison through nested derived tables. 

perStore := sel().Exprs(SalesStore, sqlb.As(sqlb.Sum(SalesCost), "sales_per_store")).From(Sales).GroupBy(SalesStore)
show(sel().Columns(sqlb.Bind(SalesStore, &sink.i32), sqlb.Bind(sqlb.As(sqlb.Sum(SalesCost), "sales_per_store"), &sink.f64)).
	From(Sales).GroupBy(SalesStore).Build())

sps := sqlb.Derived(perStore, "sales_per_store")
spsSales := cteCol[float64](sps, "sales_per_store")
avgSales := sel().Exprs(sqlb.As(pg.Round(sqlb.Avg(spsSales), 2), "avg_sales")).From(sps)
show(sel().Columns(sqlb.Bind(sqlb.As(pg.Round(sqlb.Avg(spsSales), 2), "avg_sales"), &sink.f64)).From(sps).Build())

avg := sqlb.Derived(avgSales, "avg_sales")
show(sel().Columns(
	sqlb.Bind(cteCol[int32](sps, "store_id"), &sink.i32),
	sqlb.Bind(spsSales, &sink.f64),
	sqlb.Bind(cteCol[float64](avg, "avg_sales"), &sink.f64),
).From(sps).
	Join(sqlb.Join{Type: sqlb.JoinInner, Source: avg, On: []sqlb.Predicate{sqlb.GreaterExpr(spsSales, cteCol[float64](avg, "avg_sales"))}}).
	Build())

Output:
SELECT "s"."store_id", sum("s"."cost") AS "sales_per_store" FROM "sales" AS "s" GROUP BY "s"."store_id"
SELECT round(avg("sales_per_store"."sales_per_store")::numeric, $1) AS "avg_sales" FROM (SELECT "s"."store_id", sum("s"."cost") AS "sales_per_store" FROM "sales" AS "s" GROUP BY "s"."store_id") AS "sales_per_store"
args: [2]
SELECT "sales_per_store"."store_id", "sales_per_store"."sales_per_store", "avg_sales"."avg_sales" FROM (SELECT "s"."store_id", sum("s"."cost") AS "sales_per_store" FROM "sales" AS "s" GROUP BY "s"."store_id") AS "sales_per_store" INNER JOIN (SELECT round(avg("sales_per_store"."sales_per_store")::numeric, $1) AS "avg_sales" FROM (SELECT "s"."store_id", sum("s"."cost") AS "sales_per_store" FROM "sales" AS "s" GROUP BY "s"."store_id") AS "sales_per_store") AS "avg_sales" ON ("sales_per_store"."sales_per_store" > "avg_sales"."avg_sales")
args: [2]
Example (S4_1_allUsers)

4.1 every row of users. 

show(sel().Columns(sqlb.Bind(UserID, &sink.i32), sqlb.Bind(UserName, &sink.s), sqlb.Bind(UserEmail, &sink.s2)).From(Users).Build())

Output:
SELECT "u"."user_id", "u"."user_name", "u"."email" FROM "users" AS "u"
Example (S4_1_duplicatesGroupBy)

4.1 duplicates through GROUP BY and HAVING. 

show(sel().Columns(
	sqlb.Bind(sqlb.Max(UserID), &sink.i32), sqlb.Bind(UserName, &sink.s), sqlb.Bind(UserEmail, &sink.s2),
).From(Users).GroupBy(UserName, UserEmail).
	Having(sqlb.Greater(sqlb.CountOf(UserName), 1), sqlb.Greater(sqlb.CountOf(UserEmail), 1)).Build())

Output:
SELECT max("u"."user_id"), "u"."user_name", "u"."email" FROM "users" AS "u" GROUP BY "u"."user_name", "u"."email" HAVING (count("u"."user_name") > $1 AND count("u"."email") > $2)
args: [1 1]
Example (S4_1_duplicatesRowNumber)

4.1 duplicates through row_number in a CTE. 

w := sqlb.NewWindow().PartitionBy(UserName, UserEmail).OrderBy(sqlb.OrderBy(UserID, sqlb.SortAsc))
body := sel().Exprs(UserID, UserName, UserEmail, sqlb.As(sqlb.RowNumber().Over(w), "rn")).From(Users).Order(sqlb.OrderBy(UserID, sqlb.SortAsc))
dups := sqlb.Table{Name: "rn_dups", Alias: "rn_dups"}
name := sqlb.Col[string](dups, "user_name")
email := sqlb.Col[string](dups, "email")
rn := sqlb.Col[int64](dups, "rn")
show(sel().With("rn_dups", body).Columns(
	sqlb.Bind(name, &sink.s), sqlb.Bind(email, &sink.s2), sqlb.Bind(sqlb.As(sqlb.Max(rn), "nr_of_dups"), &sink.i64),
).From(dups).Where(sqlb.Greater(rn, 1)).GroupBy(name, email).Build())

Output:
WITH "rn_dups" AS (SELECT "u"."user_id", "u"."user_name", "u"."email", row_number() OVER (PARTITION BY "u"."user_name", "u"."email" ORDER BY "u"."user_id" ASC) AS "rn" FROM "users" AS "u" ORDER BY "u"."user_id" ASC) SELECT "rn_dups"."user_name", "rn_dups"."email", max("rn_dups"."rn") AS "nr_of_dups" FROM "rn_dups" AS "rn_dups" WHERE ("rn_dups"."rn" > $1) GROUP BY "rn_dups"."user_name", "rn_dups"."email"
args: [1]
Example (S4_2_secondRow)

4.2 the second row by emp_id. 

body := sel().Exprs(EmpID, EmpName, DeptName, Salary, sqlb.As(sqlb.RowNumber().Over(sqlb.NewWindow().OrderBy(sqlb.OrderBy(EmpID, sqlb.SortAsc))), "rn")).From(Employee)
rnT := sqlb.Table{Name: "rn", Alias: "rn"}
rn := sqlb.Col[int64](rnT, "rn")
show(sel().With("rn", body).Columns(
	sqlb.Bind(sqlb.Col[string](rnT, "emp_name"), &sink.s), sqlb.Bind(rn, &sink.i64),
).From(rnT).Where(sqlb.Eq(rn, 2)).Build())

Output:
WITH "rn" AS (SELECT "e"."emp_id", "e"."emp_name", "e"."dept_name", "e"."salary", row_number() OVER (ORDER BY "e"."emp_id" ASC) AS "rn" FROM "employee" AS "e") SELECT "rn"."emp_name", "rn"."rn" FROM "rn" AS "rn" WHERE ("rn"."rn" = $1)
args: [2]
Example (S4_3_highLowCTEs)

4.3 highest or lowest salary per department, first through two CTEs and a comma join. Postgres names max(salary) "max", so the CTE column is "max". 

mas := sqlb.Table{Name: "max_salary", Alias: "mas"}
mis := sqlb.Table{Name: "min_salary", Alias: "mis"}
masDept, masMax := sqlb.Col[string](mas, "dept_name"), sqlb.Col[int32](mas, "max")
misDept, misMin := sqlb.Col[string](mis, "dept_name"), sqlb.Col[int32](mis, "min")
desc := sqlb.Case[string]().When(sqlb.EqExpr(Salary, masMax), sqlb.Param("Highest Salary")).Else(sqlb.Param("Lowest Salary"))
show(sel().
	With("max_salary", sel().Exprs(DeptName, sqlb.Max(Salary)).From(Employee).GroupBy(DeptName)).
	With("min_salary", sel().Exprs(DeptName, sqlb.Min(Salary)).From(Employee).GroupBy(DeptName)).
	Columns(sqlb.Bindings(EmpBindings(&sink.emp), []sqlb.Binding{sqlb.Bind(sqlb.As(desc, "description"), &sink.s)})...).
	From(Employee).
	Join(sqlb.Join{Type: sqlb.JoinCross, Table: mas}, sqlb.Join{Type: sqlb.JoinCross, Table: mis}).
	Where(sqlb.Or(
		sqlb.And(sqlb.EqExpr(DeptName, masDept), sqlb.EqExpr(Salary, masMax)),
		sqlb.And(sqlb.EqExpr(DeptName, misDept), sqlb.EqExpr(Salary, misMin)),
	)).
	Order(sqlb.OrderBy(DeptName, sqlb.SortAsc), sqlb.OrderBy(Salary, sqlb.SortDesc)).Build())

Output:
WITH "max_salary" AS (SELECT "e"."dept_name", max("e"."salary") FROM "employee" AS "e" GROUP BY "e"."dept_name"), "min_salary" AS (SELECT "e"."dept_name", min("e"."salary") FROM "employee" AS "e" GROUP BY "e"."dept_name") SELECT "e"."emp_id", "e"."emp_name", "e"."dept_name", "e"."salary", CASE WHEN "e"."salary" = "mas"."max" THEN $1 ELSE $2 END AS "description" FROM "employee" AS "e" CROSS JOIN "max_salary" AS "mas" CROSS JOIN "min_salary" AS "mis" WHERE ((("e"."dept_name" = "mas"."dept_name" AND "e"."salary" = "mas"."max") OR ("e"."dept_name" = "mis"."dept_name" AND "e"."salary" = "mis"."min"))) ORDER BY "e"."dept_name" ASC, "e"."salary" DESC
args: [Highest Salary Lowest Salary]
Example (S4_3_highLowRanks)

4.3 the same through two ranks and a first_value delta. first_value over a partition with at least one row is never null, which is the reason for AssertNotNull. 

asc := sqlb.NewWindow().PartitionBy(DeptName).OrderBy(sqlb.OrderBy(Salary, sqlb.SortAsc))
desc := sqlb.NewWindow().PartitionBy(DeptName).OrderBy(sqlb.OrderBy(Salary, sqlb.SortDesc))
body := sel().Exprs(EmpID, EmpName, DeptName, Salary, sqlb.As(sqlb.Rank().Over(asc), "ls"), sqlb.As(sqlb.Rank().Over(desc), "hs")).From(Employee)
lh := sqlb.Table{Name: "lh", Alias: "lh"}
lhDept, lhSalary := sqlb.Col[string](lh, "dept_name"), sqlb.Col[int32](lh, "salary")
ls, hs := sqlb.Col[int64](lh, "ls"), sqlb.Col[int64](lh, "hs")
lhAsc := sqlb.NewWindow().PartitionBy(lhDept).OrderBy(sqlb.OrderBy(lhSalary, sqlb.SortAsc))
lhDesc := sqlb.NewWindow().PartitionBy(lhDept).OrderBy(sqlb.OrderBy(lhSalary, sqlb.SortDesc))
lowHigh := sqlb.Case[string]().When(sqlb.Eq(ls, 1), sqlb.Param("Lowest Salary in Dept")).Else(sqlb.Param("Highest Salary in Dept"))
delta := sqlb.Sub(
	sqlb.AssertNotNull(sqlb.FirstValue(lhSalary).Over(lhDesc)),
	sqlb.AssertNotNull(sqlb.FirstValue(lhSalary).Over(lhAsc)),
)
show(sel().With("lh", body).Columns(
	sqlb.Bind(sqlb.Col[string](lh, "emp_name"), &sink.s), sqlb.Bind(lhDept, &sink.s2), sqlb.Bind(lhSalary, &sink.i32),
	sqlb.Bind(sqlb.As(lowHigh, "low_high"), &sink.s),
	sqlb.Bind(sqlb.As(delta, "delta"), &sink.i32),
).From(lh).Where(sqlb.Or(sqlb.Eq(ls, 1), sqlb.Eq(hs, 1))).Build())

Output:
WITH "lh" AS (SELECT "e"."emp_id", "e"."emp_name", "e"."dept_name", "e"."salary", rank() OVER (PARTITION BY "e"."dept_name" ORDER BY "e"."salary" ASC) AS "ls", rank() OVER (PARTITION BY "e"."dept_name" ORDER BY "e"."salary" DESC) AS "hs" FROM "employee" AS "e") SELECT "lh"."emp_name", "lh"."dept_name", "lh"."salary", CASE WHEN "lh"."ls" = $1 THEN $2 ELSE $3 END AS "low_high", (first_value("lh"."salary") OVER (PARTITION BY "lh"."dept_name" ORDER BY "lh"."salary" DESC) - first_value("lh"."salary") OVER (PARTITION BY "lh"."dept_name" ORDER BY "lh"."salary" ASC)) AS "delta" FROM "lh" AS "lh" WHERE (("lh"."ls" = $4 OR "lh"."hs" = $5))
args: [1 Lowest Salary in Dept Highest Salary in Dept 1 1]
Example (S4_4_selfJoin)

4.4 doctors in the same hospital with a different speciality: a self join. 

show(sel().Columns(
	sqlb.Bind(DocID, &sink.i32), sqlb.Bind(DocName, &sink.s), sqlb.Bind(DocSpec, &sink.s2),
	sqlb.Bind(DocHosp, &sink.s), sqlb.Bind(DocCity, &sink.s2), sqlb.Bind(DocFee, &sink.i32),
).From(Doctors).Join(sqlb.Join{Type: sqlb.JoinInner, Table: DoctorsR, On: []sqlb.Predicate{
	sqlb.EqExpr(DocHosp, DocRHosp),
	sqlb.NotEqExpr(DocSpec, DocRSpec),
}}).Build())

Output:
SELECT "l"."id", "l"."name", "l"."speciality", "l"."hospital", "l"."city", "l"."consultation_fee" FROM "doctors" AS "l" INNER JOIN "doctors" AS "r" ON ("l"."hospital" = "r"."hospital" AND "l"."speciality" <> "r"."speciality")
Example (S4_5_consecutiveLags)

4.5 three consecutive logins through two lags in a named window. 

w := sqlb.NewWindow().OrderBy(sqlb.OrderBy(LoginDate, sqlb.SortAsc), sqlb.OrderBy(LoginID, sqlb.SortAsc))
body := sel().Exprs(LoginID, LoginUser, LoginDate,
	sqlb.As(pg.Lag(LoginUser, 1).OverNamed("w"), "prev_user1"),
	sqlb.As(pg.Lag(LoginUser, 2).OverNamed("w"), "prev_user2"),
).From(Logins).Window("w", w)
l := sqlb.Table{Name: "l", Alias: "l"}
user := sqlb.Col[string](l, "user_name")
prev1, prev2 := sqlb.Col[*string](l, "prev_user1"), sqlb.Col[*string](l, "prev_user2")
show(sel().With("l", body).Columns(
	sqlb.Bind(sqlb.Col[int32](l, "login_id"), &sink.i32), sqlb.Bind(user, &sink.s),
	sqlb.Bind(prev1, &sink.sp), sqlb.Bind(prev2, &sink.sp2),
).From(l).Where(sqlb.EqExpr(sqlb.Nullable(user), prev1), sqlb.EqExpr(prev1, prev2)).Build())

Output:
WITH "l" AS (SELECT "ld"."login_id", "ld"."user_name", "ld"."login_date", lag("ld"."user_name", $1) OVER "w" AS "prev_user1", lag("ld"."user_name", $2) OVER "w" AS "prev_user2" FROM "login_details" AS "ld" WINDOW "w" AS (ORDER BY "ld"."login_date" ASC, "ld"."login_id" ASC)) SELECT "l"."login_id", "l"."user_name", "l"."prev_user1", "l"."prev_user2" FROM "l" AS "l" WHERE ("l"."user_name" = "l"."prev_user1" AND "l"."prev_user1" = "l"."prev_user2")
args: [1 2]
Example (S4_5_islands) 
s, ii := islandsCTEs(sel())
iiID, iiDate, iiHead := sqlb.Col[*int32](ii, "island_id"), sqlb.Col[time.Time](ii, "login_date"), sqlb.Col[*int32](ii, "island_head")
w := sqlb.NewWindow().PartitionBy(iiID).OrderBy(sqlb.OrderBy(iiDate, sqlb.SortAsc)).
	Frame(sqlb.FrameRows, sqlb.UnboundedPreceding(), sqlb.UnboundedFollowing())
show(s.Columns(
	sqlb.Bind(sqlb.Col[string](ii, "user_name"), &sink.s), sqlb.Bind(iiID, &sink.i32p),
	sqlb.Bind(sqlb.Over(sqlb.CountOf(iiID), w), &sink.i64),
).From(ii).Order(sqlb.OrderBy(iiDate, sqlb.SortAsc), sqlb.OrderBy(iiHead, sqlb.SortAsc)).Build())

Output:
WITH "rep" AS (SELECT "ld"."login_id", "ld"."user_name", "ld"."login_date", CASE WHEN lag("ld"."user_name", $1) OVER (ORDER BY "ld"."login_date" ASC) = "ld"."user_name" THEN $2 ELSE $3 END AS "rep" FROM "login_details" AS "ld"), "island_head" AS (SELECT "rep"."login_id", "rep"."user_name", "rep"."login_date", "rep"."rep", CASE WHEN ("rep"."rep" = $4 AND lead("rep"."rep", $5) OVER (ORDER BY "rep"."login_date" ASC) = $6) THEN "rep"."login_id" END AS "island_head" FROM "rep" AS "rep"), "island_id" AS (SELECT "island_head"."login_id", "island_head"."user_name", "island_head"."login_date", "island_head"."rep", "island_head"."island_head", CASE WHEN "island_head"."rep" = $7 THEN max("island_head"."island_head") OVER (ORDER BY "island_head"."login_date" ASC) WHEN "island_head"."rep" = $8 THEN "island_head"."island_head" ELSE NULL END AS "island_id" FROM "island_head" AS "island_head") SELECT "island_id"."user_name", "island_id"."island_id", count("island_id"."island_id") OVER (PARTITION BY "island_id"."island_id" ORDER BY "island_id"."login_date" ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM "island_id" AS "island_id" ORDER BY "island_id"."login_date" ASC, "island_id"."island_head" ASC
args: [1 1 0 0 1 1 1 0]
Example (S4_5_islandsGrouped)

4.5 the islands grouped, so each run is one row. 

s, ii := islandsCTEs(sel())
iiID, iiDate := sqlb.Col[*int32](ii, "island_id"), sqlb.Col[time.Time](ii, "login_date")
w := sqlb.NewWindow().PartitionBy(iiID).OrderBy(sqlb.OrderBy(iiDate, sqlb.SortAsc)).
	Frame(sqlb.FrameRows, sqlb.UnboundedPreceding(), sqlb.UnboundedFollowing())
island := sqlb.Table{Name: "island", Alias: "island"}
isID, isCount, isUser := sqlb.Col[*int32](island, "island_id"), sqlb.Col[int64](island, "consecutive_logins"), sqlb.Col[string](island, "user_name")
show(s.
	With("island", sel().Exprs(iiID, sqlb.Col[string](ii, "user_name"), sqlb.As(sqlb.Over(sqlb.CountOf(iiID), w), "consecutive_logins")).From(ii)).
	Columns(sqlb.Bind(isID, &sink.i32p), sqlb.Bind(isCount, &sink.i64), sqlb.Bind(isUser, &sink.s)).
	From(island).Where(sqlb.NotEq(isCount, 0)).GroupBy(isID, isCount, isUser).
	Order(sqlb.OrderBy(isID, sqlb.SortAsc)).Build())

Output:
WITH "rep" AS (SELECT "ld"."login_id", "ld"."user_name", "ld"."login_date", CASE WHEN lag("ld"."user_name", $1) OVER (ORDER BY "ld"."login_date" ASC) = "ld"."user_name" THEN $2 ELSE $3 END AS "rep" FROM "login_details" AS "ld"), "island_head" AS (SELECT "rep"."login_id", "rep"."user_name", "rep"."login_date", "rep"."rep", CASE WHEN ("rep"."rep" = $4 AND lead("rep"."rep", $5) OVER (ORDER BY "rep"."login_date" ASC) = $6) THEN "rep"."login_id" END AS "island_head" FROM "rep" AS "rep"), "island_id" AS (SELECT "island_head"."login_id", "island_head"."user_name", "island_head"."login_date", "island_head"."rep", "island_head"."island_head", CASE WHEN "island_head"."rep" = $7 THEN max("island_head"."island_head") OVER (ORDER BY "island_head"."login_date" ASC) WHEN "island_head"."rep" = $8 THEN "island_head"."island_head" ELSE NULL END AS "island_id" FROM "island_head" AS "island_head"), "island" AS (SELECT "island_id"."island_id", "island_id"."user_name", count("island_id"."island_id") OVER (PARTITION BY "island_id"."island_id" ORDER BY "island_id"."login_date" ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "consecutive_logins" FROM "island_id" AS "island_id") SELECT "island"."island_id", "island"."consecutive_logins", "island"."user_name" FROM "island" AS "island" WHERE ("island"."consecutive_logins" <> $9) GROUP BY "island"."island_id", "island"."consecutive_logins", "island"."user_name" ORDER BY "island"."island_id" ASC
args: [1 1 0 0 1 1 1 0 0]
Example (S4_5_repeatFlags)

4.5 repeat flags: a CASE over lag, then a CASE over the lag of that flag. 

w := sqlb.NewWindow().OrderBy(sqlb.OrderBy(LoginDate, sqlb.SortAsc))
rep := sqlb.Case[string]().
	When(sqlb.EqExpr(pg.Lag(LoginUser, 1).Over(w), sqlb.Nullable(LoginUser)), sqlb.Param("repeat")).
	Else(sqlb.Param("not repeat"))
repT := sqlb.Table{Name: "rep", Alias: "rep"}
repCol := sqlb.Col[string](repT, "rep")
repDate := sqlb.Col[time.Time](repT, "login_date")
w2 := sqlb.NewWindow().OrderBy(sqlb.OrderBy(repDate, sqlb.SortAsc))
cume := sqlb.Case[int32]().
	When(sqlb.And(sqlb.Eq(repCol, "repeat"), sqlb.EqExpr(pg.Lag(repCol, 1).Over(w2), sqlb.Nullable(repCol))), sqlb.Param[int32](1)).
	Else(sqlb.Param[int32](0))
cr := sqlb.Table{Name: "cume_rep", Alias: "cume_rep"}
cumeCol := sqlb.Col[int32](cr, "cume_rep")
show(sel().
	With("rep", sel().Exprs(LoginID, LoginUser, LoginDate, sqlb.As(rep, "rep")).From(Logins)).
	With("cume_rep", sel().Exprs(sqlb.Col[int32](repT, "login_id"), sqlb.Col[string](repT, "user_name"), repDate, repCol, sqlb.As(cume, "cume_rep")).From(repT)).
	Columns(sqlb.Bind(sqlb.Col[string](cr, "user_name"), &sink.s), sqlb.Bind(cumeCol, &sink.i32)).
	From(cr).Where(sqlb.Eq(cumeCol, 1)).Build())

Output:
WITH "rep" AS (SELECT "ld"."login_id", "ld"."user_name", "ld"."login_date", CASE WHEN lag("ld"."user_name", $1) OVER (ORDER BY "ld"."login_date" ASC) = "ld"."user_name" THEN $2 ELSE $3 END AS "rep" FROM "login_details" AS "ld"), "cume_rep" AS (SELECT "rep"."login_id", "rep"."user_name", "rep"."login_date", "rep"."rep", CASE WHEN ("rep"."rep" = $4 AND lag("rep"."rep", $5) OVER (ORDER BY "rep"."login_date" ASC) = "rep"."rep") THEN $6 ELSE $7 END AS "cume_rep" FROM "rep" AS "rep") SELECT "cume_rep"."user_name", "cume_rep"."cume_rep" FROM "cume_rep" AS "cume_rep" WHERE ("cume_rep"."cume_rep" = $8)
args: [1 repeat not repeat repeat 1 1 0 1]
Example (S4_6_interchange)

4.6 interchange adjacent names with lead and lag defaults over the whole table. 

whole := sqlb.NewWindow()
two := sqlb.Param[int32](2)
swapped := sqlb.Case[string]().
	When(sqlb.Eq(sqlb.Mod(StudentID, two), 1), pg.LeadDefault(StudentName, 1, StudentName).Over(whole)).
	When(sqlb.Eq(sqlb.Mod(StudentID, two), 0), pg.LagDefault(StudentName, 1, StudentName).Over(whole)).
	End()
show(sel().Columns(
	sqlb.Bind(StudentID, &sink.i32), sqlb.Bind(StudentName, &sink.s),
	sqlb.Bind(sqlb.As(swapped, "new_student_name"), &sink.sp),
).From(Students).Build())

Output:
SELECT "st"."id", "st"."student_name", CASE WHEN ("st"."id" % $1) = $2 THEN lead("st"."student_name", $3, "st"."student_name") OVER () WHEN ("st"."id" % $4) = $5 THEN lag("st"."student_name", $6, "st"."student_name") OVER () END AS "new_student_name" FROM "students" AS "st"
args: [2 1 1 2 0 1]
Example (S4_7_coldStreaks)

4.7 three or more consecutive cold days: streak heads, island ids, island sizes. 

byID := sqlb.NewWindow().OrderBy(sqlb.OrderBy(WID, sqlb.SortAsc))
// lag is null only on the first row, where the first branch is skipped
// and the second branch covers a cold first day.
streak := sqlb.Case[int32]().
	When(sqlb.And(sqlb.Less(WTemp, 0), sqlb.GreaterEq(sqlb.AssertNotNull(pg.Lag(WTemp, 1).Over(byID)), 0)), WID).
	When(sqlb.Less(WTemp, 0), sqlb.Param[int32](1)).
	End()
st := sqlb.Table{Name: "streak", Alias: "streak"}
stStreak, stID := sqlb.Col[*int32](st, "streak"), sqlb.Col[int32](st, "id")
islandID := sqlb.Case[*int32]().
	When(sqlb.IsNotNull(stStreak), sqlb.Over(sqlb.Max(stStreak), sqlb.NewWindow().OrderBy(sqlb.OrderBy(stID, sqlb.SortAsc)))).
	Else(sqlb.Literal[*int32]("NULL"))
ii := sqlb.Table{Name: "island_id", Alias: "island_id"}
iiID, iiRowID := sqlb.Col[*int32](ii, "island_id"), sqlb.Col[int32](ii, "id")
sizeW := sqlb.NewWindow().PartitionBy(iiID).OrderBy(sqlb.OrderBy(iiRowID, sqlb.SortAsc)).
	Frame(sqlb.FrameRows, sqlb.UnboundedPreceding(), sqlb.UnboundedFollowing())
is := sqlb.Table{Name: "island_size", Alias: "island_size"}
isSize := sqlb.Col[int64](is, "island_size")
show(sel().
	With("streak", sel().Exprs(WID, WCity, WTemp, WDay, sqlb.As(streak, "streak")).From(Weather)).
	With("island_id", sel().Exprs(stID, sqlb.Col[string](st, "city"), sqlb.Col[int32](st, "temperature"), sqlb.Col[time.Time](st, "day"), sqlb.As(islandID, "island_id")).From(st)).
	With("island_size", sel().Exprs(iiRowID, sqlb.Col[string](ii, "city"), sqlb.Col[int32](ii, "temperature"), sqlb.Col[time.Time](ii, "day"), sqlb.As(sqlb.Over(sqlb.CountOf(iiID), sizeW), "island_size")).From(ii)).
	Columns(
		sqlb.Bind(sqlb.Col[int32](is, "id"), &sink.i32), sqlb.Bind(sqlb.Col[string](is, "city"), &sink.s),
		sqlb.Bind(sqlb.Col[int32](is, "temperature"), &sink.i32), sqlb.Bind(sqlb.Col[time.Time](is, "day"), &sink.t),
		sqlb.Bind(isSize, &sink.i64),
	).
	From(is).Where(sqlb.GreaterEq(isSize, 3)).Order(sqlb.OrderBy(sqlb.Col[int32](is, "id"), sqlb.SortAsc)).Build())

Output:
WITH "streak" AS (SELECT "w"."id", "w"."city", "w"."temperature", "w"."day", CASE WHEN ("w"."temperature" < $1 AND lag("w"."temperature", $2) OVER (ORDER BY "w"."id" ASC) >= $3) THEN "w"."id" WHEN "w"."temperature" < $4 THEN $5 END AS "streak" FROM "weather" AS "w"), "island_id" AS (SELECT "streak"."id", "streak"."city", "streak"."temperature", "streak"."day", CASE WHEN "streak"."streak" IS NOT NULL THEN max("streak"."streak") OVER (ORDER BY "streak"."id" ASC) ELSE NULL END AS "island_id" FROM "streak" AS "streak"), "island_size" AS (SELECT "island_id"."id", "island_id"."city", "island_id"."temperature", "island_id"."day", count("island_id"."island_id") OVER (PARTITION BY "island_id"."island_id" ORDER BY "island_id"."id" ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "island_size" FROM "island_id" AS "island_id") SELECT "island_size"."id", "island_size"."city", "island_size"."temperature", "island_size"."day", "island_size"."island_size" FROM "island_size" AS "island_size" WHERE ("island_size"."island_size" >= $6) ORDER BY "island_size"."id" ASC
args: [0 1 0 0 1 3]
Example (S4_8_topAccountsPerMonth)

4.8 top two accounts per month by unique patients: DISTINCT, a count, a rank. 

dp := sqlb.Table{Name: "dp", Alias: "dp"}
dpMonth, dpAccount := sqlb.Col[string](dp, "month"), sqlb.Col[int32](dp, "account_id")
c := sqlb.Table{Name: "c", Alias: "c"}
cMonth, cAccount, cCount := sqlb.Col[string](c, "month"), sqlb.Col[int32](c, "account_id"), sqlb.Col[int64](c, "c")
r := sqlb.Table{Name: "r", Alias: "r"}
rMonth, rRank := sqlb.Col[string](r, "month"), sqlb.Col[int64](r, "rnk")
rankW := sqlb.NewWindow().PartitionBy(cMonth).OrderBy(sqlb.OrderBy(cCount, sqlb.SortDesc), sqlb.OrderBy(cAccount, sqlb.SortAsc))
show(sel().
	With("dp", sel().Distinct().Exprs(sqlb.As(pg.ToChar(PLDate, "month"), "month"), PLAccount, PLPatient).From(PatientLogs).
		Order(sqlb.OrderBy(sqlb.OutputCol[string]("month"), sqlb.SortAsc))).
	With("c", sel().Exprs(dpMonth, dpAccount, sqlb.As(sqlb.CountOf(dpAccount), "c")).From(dp).GroupBy(dpMonth, dpAccount)).
	With("r", sel().Exprs(cMonth, cAccount, cCount, sqlb.As(sqlb.Rank().Over(rankW), "rnk")).From(c)).
	Columns(sqlb.Bind(rMonth, &sink.s), sqlb.Bind(sqlb.Col[int32](r, "account_id"), &sink.i32), sqlb.Bind(sqlb.Col[int64](r, "c"), &sink.i64), sqlb.Bind(rRank, &sink.i64)).
	From(r).Where(sqlb.Less(rRank, 3)).Order(sqlb.OrderBy(rMonth, sqlb.SortAsc), sqlb.OrderBy(rRank, sqlb.SortAsc)).Build())

Output:
WITH "dp" AS (SELECT DISTINCT to_char("pl"."date", $1) AS "month", "pl"."account_id", "pl"."patient_id" FROM "patient_logs" AS "pl" ORDER BY "month" ASC), "c" AS (SELECT "dp"."month", "dp"."account_id", count("dp"."account_id") AS "c" FROM "dp" AS "dp" GROUP BY "dp"."month", "dp"."account_id"), "r" AS (SELECT "c"."month", "c"."account_id", "c"."c", rank() OVER (PARTITION BY "c"."month" ORDER BY "c"."c" DESC, "c"."account_id" ASC) AS "rnk" FROM "c" AS "c") SELECT "r"."month", "r"."account_id", "r"."c", "r"."rnk" FROM "r" AS "r" WHERE ("r"."rnk" < $2) ORDER BY "r"."month" ASC, "r"."rnk" ASC
args: [month 3]
Example (S5_pgTables)

5 a schema-qualified catalogue table. 

pgTables := sqlb.Table{Schema: "pg_catalog", Name: "pg_tables", Alias: "t"}
schema := sqlb.Col[string](pgTables, "schemaname")
show(sel().Columns(sqlb.Bind(schema, &sink.s), sqlb.Bind(sqlb.Col[string](pgTables, "tablename"), &sink.s2)).
	From(pgTables).Where(sqlb.NotEq(schema, "pg_catalog"), sqlb.NotEq(schema, "information_schema")).Build())

Output:
SELECT "t"."schemaname", "t"."tablename" FROM "pg_catalog"."pg_tables" AS "t" WHERE ("t"."schemaname" <> $1 AND "t"."schemaname" <> $2)
args: [pg_catalog information_schema]
Example (S5_quotedIdentifiers)

5 quoted identifiers. sqlb quotes every identifier, so the README's point about case-sensitive names holds without effort. 

t := sqlb.Table{Name: "table1", Alias: "t1"}
col := sqlb.Col[string](t, "column1")
show(sel().Columns(sqlb.Bind(col, &sink.s)).From(t).Where(sqlb.Eq(col, "name1")).Build())

Output:
SELECT "t1"."column1" FROM "table1" AS "t1" WHERE ("t1"."column1" = $1)
args: [name1]
Example (S6_firstValueCTE) 
dt := sqlb.Table{Name: "delta", Alias: "delta"}
evType, delta := sqlb.Col[string](dt, "event_type"), sqlb.Col[int32](dt, "delta")
show(sel().With("delta", deltaBody()).Distinct().Columns(
	sqlb.Bind(evType, &sink.s),
	sqlb.Bind(sqlb.As(sqlb.FirstValue(delta).Over(sqlb.NewWindow().PartitionBy(evType)), "value"), &sink.i32p),
).From(dt).Where(sqlb.IsNotNull(delta)).Order(sqlb.OrderBy(evType, sqlb.SortAsc)).Build())

Output:
WITH "delta" AS (SELECT "e"."event_type", "e"."value", "e"."time", (lag("e"."value", $1) OVER (PARTITION BY "e"."event_type" ORDER BY "e"."time" DESC) - "e"."value") AS "delta" FROM "events" AS "e" ORDER BY "e"."event_type" ASC) SELECT DISTINCT "delta"."event_type", first_value("delta"."delta") OVER (PARTITION BY "delta"."event_type") AS "value" FROM "delta" AS "delta" WHERE ("delta"."delta" IS NOT NULL) ORDER BY "delta"."event_type" ASC
args: [1]
Example (S6_firstValueDerived) 
dt := sqlb.Derived(deltaBody(), "delta")
evType, delta := cteCol[string](dt, "event_type"), cteCol[int32](dt, "delta")
show(sel().Distinct().Columns(
	sqlb.Bind(evType, &sink.s),
	sqlb.Bind(sqlb.As(sqlb.FirstValue(delta).Over(sqlb.NewWindow().PartitionBy(evType)), "value"), &sink.i32p),
).From(dt).Where(sqlb.IsNotNull(delta)).Order(sqlb.OrderBy(evType, sqlb.SortAsc)).Build())

Output:
SELECT DISTINCT "delta"."event_type", first_value("delta"."delta") OVER (PARTITION BY "delta"."event_type") AS "value" FROM (SELECT "e"."event_type", "e"."value", "e"."time", (lag("e"."value", $1) OVER (PARTITION BY "e"."event_type" ORDER BY "e"."time" DESC) - "e"."value") AS "delta" FROM "events" AS "e" ORDER BY "e"."event_type" ASC) AS "delta" WHERE ("delta"."delta" IS NOT NULL) ORDER BY "delta"."event_type" ASC
args: [1]
Example (S6_nthValueDerived) 
dt := sqlb.Derived(deltaBody(), "delta")
evType, delta := cteCol[string](dt, "event_type"), cteCol[int32](dt, "delta")
show(sel().Distinct().Columns(
	sqlb.Bind(evType, &sink.s),
	sqlb.Bind(sqlb.As(sqlb.NthValue(delta, 1).Over(sqlb.NewWindow().PartitionBy(evType)), "value"), &sink.i32p),
).From(dt).Where(sqlb.IsNotNull(delta)).Order(sqlb.OrderBy(evType, sqlb.SortAsc)).Build())

Output:
SELECT DISTINCT "delta"."event_type", nth_value("delta"."delta", $1) OVER (PARTITION BY "delta"."event_type") AS "value" FROM (SELECT "e"."event_type", "e"."value", "e"."time", (lag("e"."value", $2) OVER (PARTITION BY "e"."event_type" ORDER BY "e"."time" DESC) - "e"."value") AS "delta" FROM "events" AS "e" ORDER BY "e"."event_type" ASC) AS "delta" WHERE ("delta"."delta" IS NOT NULL) ORDER BY "delta"."event_type" ASC
args: [1 1]

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.