periodcompare

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: 5 Imported by: 0

README

periodcompare

A period-comparison report over several fact tables, built with sqlb for Postgres. It is the worked example for the hardest shape a reporting API has: one row per dimension value, measures drawn from four or five different tables, three time periods side by side, dozens of optional filters, computed ratios that the caller can filter and sort on, pagination with a total count, and sources that switch on and off by request.

Every construct here is typed. Every column, join, comparison and scan target is checked by the compiler. Every request value is a bound parameter. The SQL of every example is checked by go test.

The problem

A comparison report answers "how did each vendor, store, product, department, class or fiscal month do this period against the same period last year and the year before, with stock, open orders and ad spend beside it". The data lives in different tables with different grains:

Source Grain Measures
sales_lines one line per sale, dated units, sales, cost, margin, returns, for each period
stock_levels current stock per product and store on hand, on-hand retail
stock_snapshots stock per product and store per date on hand at the end of the previous period
po_lines open purchase order lines on order, on-order retail
ad_spend ad cost per product per day ad cost

The report needs one row per key with all of it, and the key is not a column. It is a different expression per dimension, and some dimensions need a join chain from the product to its category.

What the usual designs cost

Join chain. current FULL OUTER JOIN previous LEFT JOIN stock LEFT JOIN ... on the key. It loses keys: a product that sold last year but not this year, and has stock, has no current row to hang the stock on. Each new source adds a join and a COALESCE(current.key, previous.key, ...) that grows with it.

Wide union. Every source becomes a sub-select with the same column list, in the same order, where a source fills its own measures and writes a typed zero for every other one. UNION ALL, then SUM every column per key. It keeps every key, and it forces the hacks:

  • Every source repeats the full measure list. A new measure means a new 0 AS x in every branch, and a missed one breaks the union at run time because branches line up by position.
  • Same-table periods become separate branches, so the sales table is scanned once per period.
  • A fiscal-month key collides across years, so the previous year's key is offset by a constant and mapped back to a label at the end.
  • Computed ratios are aliases of the outer SELECT, so a numeric filter on them has to be a HAVING on the alias, and the alias name has to be rebuilt from the request string.
  • The total count is a second statement that must repeat the whole union.
  • Filters are mapped to columns per source by name, and a source that lacks the column skips the filter silently.
  • Everything is a string, so an alias typo or a missing join is a database error, not a compile error.

The shape used here

WITH sales AS (
  SELECT <key> AS key,
         sum(qty) FILTER (WHERE sold_on BETWEEN $cur_from AND $cur_to)   AS current_units,
         sum(qty) FILTER (WHERE sold_on BETWEEN $prev_from AND $prev_to) AS previous_units,
         ...
  FROM sales_lines s JOIN <dimension tables> WHERE <row filters> AND sold_on BETWEEN $earliest AND $latest
  GROUP BY <key>),
stock AS (SELECT <key> AS key, sum(on_hand) AS on_hand, ... FROM stock_levels ... GROUP BY <key>),
on_order AS (...), ad_spend AS (...),
keys AS (SELECT key FROM sales UNION SELECT key FROM stock UNION ...),
report AS (
  SELECT k.key,
         COALESCE(sales.current_units, 0) AS current_units, ..., 0 AS third_units,
         (current - previous) / NULLIF(previous, 0) * 100 AS units_pct, ...
  FROM keys k LEFT JOIN sales USING (key) LEFT JOIN stock USING (key) ...)
SELECT r.*, count(*) OVER ()
FROM report r LEFT JOIN labels USING (key)
WHERE r.sell_through > $1
ORDER BY r.roas DESC NULLS LAST, r.key ASC
LIMIT $2 OFFSET $3

Each decision, and the hack it removes:

  • One CTE per source, selecting only its own measures. No zero list per source. A new measure touches one CTE and one line of the report CTE.
  • keys is the union of every source's keys, and report LEFT JOINs each source onto it. Every key survives, as with the wide union, but the join is on a distinct key set, not on the full fact rows.
  • Same-table periods are FILTER aggregates on one scan. The sales table is read once over the widest date span; each period is a filter on the aggregate.
  • A disabled source is not joined, and its measures are typed zeros chosen once, in the report CTE. A ratio whose denominator would come from a disabled source is NULL, not a number computed over a zero that looks like data.
  • Computed metrics are columns of the report CTE. The outer query filters and sorts on them with plain WHERE and ORDER BY, by column name from a closed vocabulary.
  • The total count is count(*) OVER () in the same statement. One round trip, and the count always agrees with the page.
  • The fiscal-month key is the period index, not the label. The same period of three years lands on one row without an offset trick; a labels CTE attaches the current year's label at the end.
  • A filter that a source cannot honour is reported, not skipped. Result.SkippedFilters names it, and Result.DisabledSources names every source that is out.

The pieces

schema.go      tables and typed columns
dimension.go   Dimension: the key expression and the joins it needs, per source; the label CTE
params.go      Params, Filters, Include, Sort, Page; the closed vocabularies
metrics.go     Metric names, their typed report columns, and Row, the scan destination
filters.go     which filter applies to which source, the SKU-set filters, the final filters
sources.go     one function per source, each returning its CTE and its measure names
report.go      Build: keys, report CTE, labels, final filters, sort, page, total
report_test.go five checked examples and two tests

How the parts fit:

  1. Build asks each source for its CTE. A source checks whether the dimension can be served from its columns, builds the key with the dimension's plan, applies the row filters it can honour, and groups by the key.
  2. Build unions the keys of the enabled sources into keys, then writes report as keys LEFT JOIN each source. Every measure goes through measures, which returns either COALESCE(source.col, 0) or a typed zero literal.
  3. The computed metrics are expressions over those measures. Ratios use NULLIF and are typed *float64, so a zero denominator scans as nil.
  4. The outer statement binds every report column into Row, joins the label CTE when the dimension has one, applies the key and numeric filters, sorts by a Metric from the closed set with the key as tiebreak, pages, and adds the window count.

Extending it

  • A new source. Add a table and its columns in schema.go, a function in sources.go that returns its CTE and measure names, and the measures reads plus any computed metric in report.go. Nothing else changes.
  • A new dimension. Add the constant, its serves rule and its plan in dimension.go, and a label CTE if the key is not its own label.
  • A new filter. Add the field to Filters and its rule in rowFilters, stating which sources it applies to. If a source cannot honour it, append to skipped.
  • A new metric. Add the Metric constant, its typed column in metricColumn, the expression in report.go and the field in Row.

What is typed and what is not

Typed by the compiler: every column reference, every join condition, every comparison between a column and a value, every Bind into Row, every aggregate's operand, the dimension and metric vocabularies.

Checked at build time, before the database: unknown dimension, unknown metric in a filter or sort, unknown numeric operator, a fiscal report without a year, a duplicate alias in a statement.

Not checked here: that a column exists in the database, and that the SQL runs. The examples prove rendering. No statement in this package has run against a Postgres server yet.

Known costs

  • A bound value that appears in several aggregates is bound once per use, so a three-period report binds the same dates many times. Postgres does not mind; the argument list is long. A single placeholder per value is an open item in the builder spec.
  • The label CTE for the product dimension groups every product. On a large catalogue it should be filtered to the keys of the page; that is a WHERE key IN (SELECT key FROM report ...) away.

Shape or tool: what fixed the report

It is worth being exact about which part of this result the builder deserves, because the claim "a typed builder fixed the report" is not true and a reader who believes it will draw the wrong lesson.

The query shape fixed the report, and the shape is portable. A keys CTE, one CTE per source, FILTER aggregates for the periods, a report CTE for the computed metrics, a window count. Every engine has these, ClickHouse included with sumIf for FILTER, and any string builder can render them. A team with a string builder could have written this shape. The reason such teams end up with the wide union instead is history, not tooling: a join chain loses keys, the union fixes that under time pressure, and every later feature is added to the union as it stands because nobody pays for a second redesign.

A string builder lets the hacks grow, and a typed builder does not. The difference is not what each can render. It is what each refuses:

Effect Where it comes from
Every key survives, one scan per table, filters on computed metrics, count in the same statement The shape. Portable to any engine and any builder
A disabled source becomes a typed zero or NULL, chosen in one place The Go structure of Build. Possible with strings, natural with typed expressions
A wrong column, join, scan target or sort field fails before the database The typed builder. A string builder cannot do this
No request value in the SQL text, ever The typed builder. A string builder allows it and the quick path takes it
The SQL of every variant is checked by a test The Example convention. Possible with strings; rarely done

The mechanism is the string door. With a string builder, the shortest path to a date filter is a literal in a Sprintf, the shortest path to a sort is the request field capitalised into ORDER BY, and the shortest path to a per-source column is alias + "." + name. Each is quicker than the careful path, so each is taken, and each is a run-time failure waiting for the request that triggers it. With no string door, the careful path is the only path: a table and its columns are declared once, and from then on a join, a filter and a scan target are checked. Refactoring becomes cheap, so the shape gets fixed instead of patched.

The honest claim is therefore narrow. The shape fixes the report. The type system makes the shape cheap to reach, safe to change and impossible to erode with a shortcut. Building the full example here cost two compile errors and one duplicate-alias error from Build; the same three mistakes in a string builder are three failed requests found by running the report.

One caution for a column store. ClickHouse renders a LEFT JOIN miss as a default value, not NULL, unless join_use_nulls is set. The COALESCE in the report CTE is then harmless, but a ratio that should be NULL for a missing source becomes a number. Set the setting, or the honesty of the NULL ratios is lost.

Documentation

Overview

Package periodcompare builds a period-comparison report over several fact tables in Postgres: sales for the current, previous and third-year periods, current stock, stock at a past snapshot date, open purchase orders and advertising spend, all keyed by one chosen dimension.

The shape it avoids is the wide UNION ALL, where every source repeats the full measure list with typed zeros so the branches line up by position. Here each source is one CTE that selects only its own measures, a keys CTE unions the distinct keys, and the report CTE LEFT JOINs each source back onto the keys with COALESCE. Same-table periods are one scan with FILTER instead of one branch per period. Computed metrics live in the report CTE, so the outer query filters and sorts on them by name, and the total row count comes from a window function in the same statement.

Everything is typed: every column, every join, every scan target. A source that cannot serve the chosen dimension is left out, its measures render as typed zeros, and the build result names it.

Example (ClassFilterJoins)

A class filter on a dimension that does not join categories adds the join; on one that does, it reuses it.

package main

import (
	"fmt"
	"strings"
	"time"

	"github.com/ubgo/sqlb/examples/pgsurface/periodcompare"
)

var (
	cur  = periodcompare.DateRange{From: date(2026, 9, 1), To: date(2026, 9, 30)}
	prev = periodcompare.DateRange{From: date(2025, 9, 2), To: date(2025, 10, 1)}
)

func date(y int, m time.Month, d int) time.Time { return time.Date(y, m, d, 0, 0, 0, 0, time.UTC) }

// show prints the SQL split at the CTE boundaries so a reader can follow it.
func show(r periodcompare.Result, err error) {
	if err != nil {
		fmt.Println("error:", err)
		return
	}
	sql := r.Query.SQL
	sql = strings.ReplaceAll(sql, `), "`, "),\n\"")
	sql = strings.ReplaceAll(sql, ") SELECT ", ")\nSELECT ")
	fmt.Println(sql)
	fmt.Println("args:", len(r.Query.Args))
	if len(r.DisabledSources) > 0 {
		fmt.Println("disabled:", r.DisabledSources)
	}
	if len(r.SkippedFilters) > 0 {
		fmt.Println("skipped:", r.SkippedFilters)
	}
}

func main() {
	show(periodcompare.Build(periodcompare.Params{
		Dimension: periodcompare.ByVendor,
		Current:   cur, Previous: prev,
		Filters: periodcompare.Filters{Classes: []string{"Running"}},
	}))
}
Output:
WITH "sales" AS (SELECT "v"."name" AS "key", sum("s"."qty") FILTER (WHERE "s"."sold_on" BETWEEN $1 AND $2) AS "current_units", sum((CAST("s"."qty" AS float8) * "s"."unit_price")) FILTER (WHERE "s"."sold_on" BETWEEN $3 AND $4) AS "current_sales", sum((CAST("s"."qty" AS float8) * "s"."unit_cost")) FILTER (WHERE "s"."sold_on" BETWEEN $5 AND $6) AS "current_cost", sum(((CAST("s"."qty" AS float8) * "s"."unit_price") - (CAST("s"."qty" AS float8) * "s"."unit_cost"))) FILTER (WHERE "s"."sold_on" BETWEEN $7 AND $8) AS "current_margin", sum(CASE WHEN "s"."qty" < $9 THEN (-"s"."qty") ELSE $10 END) FILTER (WHERE "s"."sold_on" BETWEEN $11 AND $12) AS "returned_units", sum("s"."qty") FILTER (WHERE "s"."sold_on" BETWEEN $13 AND $14) AS "previous_units", sum((CAST("s"."qty" AS float8) * "s"."unit_price")) FILTER (WHERE "s"."sold_on" BETWEEN $15 AND $16) AS "previous_sales" FROM "sales_lines" AS "s" INNER JOIN "vendors" AS "v" ON ("v"."id" = "s"."vendor_id") INNER JOIN "products" AS "p" ON ("p"."id" = "s"."product_id") INNER JOIN "categories" AS "c" ON ("c"."id" = "p"."category_id") WHERE ("c"."class_name" = ANY($17) AND "s"."is_gift" = $18 AND "s"."sold_on" BETWEEN $19 AND $20) GROUP BY "v"."name"),
"stock" AS (SELECT "v"."name" AS "key", sum("sl"."on_hand") AS "on_hand", sum((CAST("sl"."on_hand" AS float8) * "sl"."retail_price")) AS "on_hand_retail" FROM "stock_levels" AS "sl" INNER JOIN "vendors" AS "v" ON ("v"."id" = "sl"."vendor_id") INNER JOIN "products" AS "p" ON ("p"."id" = "sl"."product_id") INNER JOIN "categories" AS "c" ON ("c"."id" = "p"."category_id") WHERE ("c"."class_name" = ANY($21)) GROUP BY "v"."name"),
"keys" AS (SELECT "sales"."key" FROM "sales" AS "sales" UNION SELECT "stock"."key" FROM "stock" AS "stock"),
"report" AS (SELECT "k"."key" AS "key", COALESCE("sales"."current_units", 0) AS "current_units", COALESCE("sales"."current_sales", 0) AS "current_sales", COALESCE("sales"."current_cost", 0) AS "current_cost", COALESCE("sales"."current_margin", 0) AS "current_margin", COALESCE("sales"."returned_units", 0) AS "returned_units", COALESCE("sales"."previous_units", 0) AS "previous_units", COALESCE("sales"."previous_sales", 0) AS "previous_sales", 0 AS "third_units", 0 AS "third_sales", COALESCE("stock"."on_hand", 0) AS "on_hand", COALESCE("stock"."on_hand_retail", 0) AS "on_hand_retail", 0 AS "previous_on_hand", 0 AS "on_order", 0 AS "on_order_retail", 0 AS "ad_cost", (CAST(COALESCE("sales"."current_units", 0) AS float8) - CAST(COALESCE("sales"."previous_units", 0) AS float8)) AS "units_change", (((CAST(COALESCE("sales"."current_units", 0) AS float8) - CAST(COALESCE("sales"."previous_units", 0) AS float8)) / NULLIF(CAST(COALESCE("sales"."previous_units", 0) AS float8), 0)) * $22) AS "units_pct", (COALESCE("sales"."current_sales", 0) - COALESCE("sales"."previous_sales", 0)) AS "sales_change", (((COALESCE("sales"."current_sales", 0) - COALESCE("sales"."previous_sales", 0)) / NULLIF(COALESCE("sales"."previous_sales", 0), 0)) * $23) AS "sales_pct", ((COALESCE("sales"."current_margin", 0) / NULLIF(COALESCE("sales"."current_sales", 0), 0)) * $24) AS "margin_pct", ((CAST(COALESCE("sales"."current_units", 0) AS float8) / NULLIF((CAST(COALESCE("sales"."current_units", 0) AS float8) + CAST(COALESCE("stock"."on_hand", 0) AS float8)), 0)) * $25) AS "sell_through", ((CAST(COALESCE("sales"."returned_units", 0) AS float8) / NULLIF((CAST(COALESCE("sales"."current_units", 0) AS float8) + CAST(COALESCE("sales"."returned_units", 0) AS float8)), 0)) * $26) AS "returns_pct", NULL AS "roas" FROM "keys" AS "k" LEFT JOIN "sales" AS "sales" USING ("key") LEFT JOIN "stock" AS "stock" USING ("key"))
SELECT "r"."key", "r"."key", NULL, "r"."current_units", "r"."current_sales", "r"."current_cost", "r"."current_margin", "r"."returned_units", "r"."previous_units", "r"."previous_sales", "r"."third_units", "r"."third_sales", "r"."on_hand", "r"."on_hand_retail", "r"."previous_on_hand", "r"."on_order", "r"."on_order_retail", "r"."ad_cost", "r"."units_change", "r"."units_pct", "r"."sales_change", "r"."sales_pct", "r"."margin_pct", "r"."sell_through", "r"."returns_pct", "r"."roas", count(*) OVER () FROM "report" AS "r" ORDER BY "r"."key" ASC NULLS LAST LIMIT $27 OFFSET $28
args: 28
disabled: [snapshots on_order ad_spend]
Example (FiscalMonth)

Fiscal month: the key is the period index, so the same period of three years lands on one row; the label comes from the current year's calendar.

package main

import (
	"fmt"
	"strings"
	"time"

	"github.com/ubgo/sqlb/examples/pgsurface/periodcompare"
)

var (
	cur   = periodcompare.DateRange{From: date(2026, 9, 1), To: date(2026, 9, 30)}
	prev  = periodcompare.DateRange{From: date(2025, 9, 2), To: date(2025, 10, 1)}
	third = periodcompare.DateRange{From: date(2024, 9, 3), To: date(2024, 10, 2)}
)

func date(y int, m time.Month, d int) time.Time { return time.Date(y, m, d, 0, 0, 0, 0, time.UTC) }

// show prints the SQL split at the CTE boundaries so a reader can follow it.
func show(r periodcompare.Result, err error) {
	if err != nil {
		fmt.Println("error:", err)
		return
	}
	sql := r.Query.SQL
	sql = strings.ReplaceAll(sql, `), "`, "),\n\"")
	sql = strings.ReplaceAll(sql, ") SELECT ", ")\nSELECT ")
	fmt.Println(sql)
	fmt.Println("args:", len(r.Query.Args))
	if len(r.DisabledSources) > 0 {
		fmt.Println("disabled:", r.DisabledSources)
	}
	if len(r.SkippedFilters) > 0 {
		fmt.Println("skipped:", r.SkippedFilters)
	}
}

func main() {
	show(periodcompare.Build(periodcompare.Params{
		Dimension:  periodcompare.ByFiscalMonth,
		FiscalYear: 2026,
		Current:    cur,
		Previous:   prev,
		Third:      third,
		Include:    periodcompare.Include{ThirdYear: true},
		Sort:       periodcompare.Sort{By: periodcompare.MetricKey, Dir: periodcompare.Asc},
	}))
}
Output:
WITH "sales" AS (SELECT CAST("fc"."period_index" AS text) AS "key", sum("s"."qty") FILTER (WHERE "s"."sold_on" BETWEEN $1 AND $2) AS "current_units", sum((CAST("s"."qty" AS float8) * "s"."unit_price")) FILTER (WHERE "s"."sold_on" BETWEEN $3 AND $4) AS "current_sales", sum((CAST("s"."qty" AS float8) * "s"."unit_cost")) FILTER (WHERE "s"."sold_on" BETWEEN $5 AND $6) AS "current_cost", sum(((CAST("s"."qty" AS float8) * "s"."unit_price") - (CAST("s"."qty" AS float8) * "s"."unit_cost"))) FILTER (WHERE "s"."sold_on" BETWEEN $7 AND $8) AS "current_margin", sum(CASE WHEN "s"."qty" < $9 THEN (-"s"."qty") ELSE $10 END) FILTER (WHERE "s"."sold_on" BETWEEN $11 AND $12) AS "returned_units", sum("s"."qty") FILTER (WHERE "s"."sold_on" BETWEEN $13 AND $14) AS "previous_units", sum((CAST("s"."qty" AS float8) * "s"."unit_price")) FILTER (WHERE "s"."sold_on" BETWEEN $15 AND $16) AS "previous_sales", sum("s"."qty") FILTER (WHERE "s"."sold_on" BETWEEN $17 AND $18) AS "third_units", sum((CAST("s"."qty" AS float8) * "s"."unit_price")) FILTER (WHERE "s"."sold_on" BETWEEN $19 AND $20) AS "third_sales" FROM "sales_lines" AS "s" INNER JOIN "fiscal_calendar" AS "fc" ON ("fc"."day" = "s"."sold_on") WHERE ("s"."is_gift" = $21 AND "s"."sold_on" BETWEEN $22 AND $23) GROUP BY CAST("fc"."period_index" AS text)),
"keys" AS (SELECT "sales"."key" FROM "sales" AS "sales"),
"report" AS (SELECT "k"."key" AS "key", COALESCE("sales"."current_units", 0) AS "current_units", COALESCE("sales"."current_sales", 0) AS "current_sales", COALESCE("sales"."current_cost", 0) AS "current_cost", COALESCE("sales"."current_margin", 0) AS "current_margin", COALESCE("sales"."returned_units", 0) AS "returned_units", COALESCE("sales"."previous_units", 0) AS "previous_units", COALESCE("sales"."previous_sales", 0) AS "previous_sales", COALESCE("sales"."third_units", 0) AS "third_units", COALESCE("sales"."third_sales", 0) AS "third_sales", 0 AS "on_hand", 0 AS "on_hand_retail", 0 AS "previous_on_hand", 0 AS "on_order", 0 AS "on_order_retail", 0 AS "ad_cost", (CAST(COALESCE("sales"."current_units", 0) AS float8) - CAST(COALESCE("sales"."previous_units", 0) AS float8)) AS "units_change", (((CAST(COALESCE("sales"."current_units", 0) AS float8) - CAST(COALESCE("sales"."previous_units", 0) AS float8)) / NULLIF(CAST(COALESCE("sales"."previous_units", 0) AS float8), 0)) * $24) AS "units_pct", (COALESCE("sales"."current_sales", 0) - COALESCE("sales"."previous_sales", 0)) AS "sales_change", (((COALESCE("sales"."current_sales", 0) - COALESCE("sales"."previous_sales", 0)) / NULLIF(COALESCE("sales"."previous_sales", 0), 0)) * $25) AS "sales_pct", ((COALESCE("sales"."current_margin", 0) / NULLIF(COALESCE("sales"."current_sales", 0), 0)) * $26) AS "margin_pct", NULL AS "sell_through", ((CAST(COALESCE("sales"."returned_units", 0) AS float8) / NULLIF((CAST(COALESCE("sales"."current_units", 0) AS float8) + CAST(COALESCE("sales"."returned_units", 0) AS float8)), 0)) * $27) AS "returns_pct", NULL AS "roas" FROM "keys" AS "k" LEFT JOIN "sales" AS "sales" USING ("key")),
"labels" AS (SELECT DISTINCT CAST("fc"."period_index" AS text) AS "key", "fc"."period_label" AS "label", NULL AS "image_url" FROM "fiscal_calendar" AS "fc" WHERE ("fc"."fiscal_year" = $28))
SELECT "r"."key", COALESCE("l"."label", "r"."key"),
"l"."image_url", "r"."current_units", "r"."current_sales", "r"."current_cost", "r"."current_margin", "r"."returned_units", "r"."previous_units", "r"."previous_sales", "r"."third_units", "r"."third_sales", "r"."on_hand", "r"."on_hand_retail", "r"."previous_on_hand", "r"."on_order", "r"."on_order_retail", "r"."ad_cost", "r"."units_change", "r"."units_pct", "r"."sales_change", "r"."sales_pct", "r"."margin_pct", "r"."sell_through", "r"."returns_pct", "r"."roas", count(*) OVER () FROM "report" AS "r" LEFT JOIN "labels" AS "l" USING ("key") ORDER BY "r"."key" ASC NULLS LAST LIMIT $29 OFFSET $30
args: 30
disabled: [stock snapshots on_order ad_spend]
Example (ProductEverything)

Everything on: three periods, snapshot, on-order, ad spend, every filter, a numeric filter on a computed metric, sorted by a ratio, page 3.

package main

import (
	"fmt"
	"strings"
	"time"

	"github.com/ubgo/sqlb/examples/pgsurface/periodcompare"
)

var (
	cur   = periodcompare.DateRange{From: date(2026, 9, 1), To: date(2026, 9, 30)}
	prev  = periodcompare.DateRange{From: date(2025, 9, 2), To: date(2025, 10, 1)}
	third = periodcompare.DateRange{From: date(2024, 9, 3), To: date(2024, 10, 2)}
)

func date(y int, m time.Month, d int) time.Time { return time.Date(y, m, d, 0, 0, 0, 0, time.UTC) }

// show prints the SQL split at the CTE boundaries so a reader can follow it.
func show(r periodcompare.Result, err error) {
	if err != nil {
		fmt.Println("error:", err)
		return
	}
	sql := r.Query.SQL
	sql = strings.ReplaceAll(sql, `), "`, "),\n\"")
	sql = strings.ReplaceAll(sql, ") SELECT ", ")\nSELECT ")
	fmt.Println(sql)
	fmt.Println("args:", len(r.Query.Args))
	if len(r.DisabledSources) > 0 {
		fmt.Println("disabled:", r.DisabledSources)
	}
	if len(r.SkippedFilters) > 0 {
		fmt.Println("skipped:", r.SkippedFilters)
	}
}

func main() {
	show(periodcompare.Build(periodcompare.Params{
		Dimension: periodcompare.ByProduct,
		Current:   cur,
		Previous:  prev,
		Third:     third,
		Include:   periodcompare.Include{ThirdYear: true, OnOrder: true, AdSpend: true, Snapshot: true},
		Filters: periodcompare.Filters{
			AllowedVendors:   []string{"v1", "v2"},
			VendorIDs:        []string{"v1"},
			StoreIDs:         []int32{10, 11},
			Departments:      []string{"Shoes"},
			KeywordIDs:       []int64{7},
			ExcludeReturns:   true,
			ExcludeMarkdowns: true,
			KeyIn:            []string{"A1 | Boot"},
			Numeric:          []periodcompare.NumericFilter{{Metric: periodcompare.MetricSellThrough, Op: periodcompare.OpGreater, Value: 20}},
		},
		Sort: periodcompare.Sort{By: periodcompare.MetricROAS, Dir: periodcompare.Desc},
		Page: periodcompare.Page{Size: 25, Number: 3},
	}))
}
Output:
WITH "sales" AS (SELECT concat("p"."code", CAST($1 AS text),
"p"."name") AS "key", sum("s"."qty") FILTER (WHERE "s"."sold_on" BETWEEN $2 AND $3) AS "current_units", sum((CAST("s"."qty" AS float8) * "s"."unit_price")) FILTER (WHERE "s"."sold_on" BETWEEN $4 AND $5) AS "current_sales", sum((CAST("s"."qty" AS float8) * "s"."unit_cost")) FILTER (WHERE "s"."sold_on" BETWEEN $6 AND $7) AS "current_cost", sum(((CAST("s"."qty" AS float8) * "s"."unit_price") - (CAST("s"."qty" AS float8) * "s"."unit_cost"))) FILTER (WHERE "s"."sold_on" BETWEEN $8 AND $9) AS "current_margin", sum(CASE WHEN "s"."qty" < $10 THEN (-"s"."qty") ELSE $11 END) FILTER (WHERE "s"."sold_on" BETWEEN $12 AND $13) AS "returned_units", sum("s"."qty") FILTER (WHERE "s"."sold_on" BETWEEN $14 AND $15) AS "previous_units", sum((CAST("s"."qty" AS float8) * "s"."unit_price")) FILTER (WHERE "s"."sold_on" BETWEEN $16 AND $17) AS "previous_sales", sum("s"."qty") FILTER (WHERE "s"."sold_on" BETWEEN $18 AND $19) AS "third_units", sum((CAST("s"."qty" AS float8) * "s"."unit_price")) FILTER (WHERE "s"."sold_on" BETWEEN $20 AND $21) AS "third_sales" FROM "sales_lines" AS "s" INNER JOIN "products" AS "p" ON ("p"."id" = "s"."product_id") INNER JOIN "categories" AS "c" ON ("c"."id" = "p"."category_id") WHERE ("s"."vendor_id" = ANY($22) AND "s"."vendor_id" = ANY($23) AND "s"."store_id" = ANY($24) AND "c"."department" = ANY($25) AND "s"."product_id" IN (SELECT "kw"."product_id" FROM "product_keywords" AS "kw" WHERE ("kw"."keyword_id" = ANY($26))) AND "s"."qty" > $27 AND "s"."unit_price" >= "s"."list_price" AND "s"."is_gift" = $28 AND "s"."sold_on" BETWEEN $29 AND $30) GROUP BY concat("p"."code", CAST($31 AS text),
"p"."name")),
"stock" AS (SELECT concat("p"."code", CAST($32 AS text),
"p"."name") AS "key", sum("sl"."on_hand") AS "on_hand", sum((CAST("sl"."on_hand" AS float8) * "sl"."retail_price")) AS "on_hand_retail" FROM "stock_levels" AS "sl" INNER JOIN "products" AS "p" ON ("p"."id" = "sl"."product_id") INNER JOIN "categories" AS "c" ON ("c"."id" = "p"."category_id") WHERE ("sl"."vendor_id" = ANY($33) AND "sl"."vendor_id" = ANY($34) AND "sl"."store_id" = ANY($35) AND "c"."department" = ANY($36) AND "sl"."product_id" IN (SELECT "kw"."product_id" FROM "product_keywords" AS "kw" WHERE ("kw"."keyword_id" = ANY($37))) AND "sl"."product_id" IN (SELECT DISTINCT "s"."product_id" FROM "sales_lines" AS "s" WHERE ("s"."sold_on" BETWEEN $38 AND $39 AND "s"."unit_price" >= "s"."list_price"))) GROUP BY concat("p"."code", CAST($40 AS text),
"p"."name")),
"snapshots" AS (SELECT concat("p"."code", CAST($41 AS text),
"p"."name") AS "key", sum("ss"."qty") AS "previous_on_hand" FROM "stock_snapshots" AS "ss" INNER JOIN "products" AS "p" ON ("p"."id" = "ss"."product_id") INNER JOIN "categories" AS "c" ON ("c"."id" = "p"."category_id") WHERE ("ss"."vendor_id" = ANY($42) AND "ss"."vendor_id" = ANY($43) AND "ss"."store_id" = ANY($44) AND "c"."department" = ANY($45) AND "ss"."product_id" IN (SELECT "kw"."product_id" FROM "product_keywords" AS "kw" WHERE ("kw"."keyword_id" = ANY($46))) AND "ss"."product_id" IN (SELECT DISTINCT "s"."product_id" FROM "sales_lines" AS "s" WHERE ("s"."sold_on" BETWEEN $47 AND $48 AND "s"."unit_price" >= "s"."list_price")) AND "ss"."snapshot_date" = $49) GROUP BY concat("p"."code", CAST($50 AS text),
"p"."name")),
"on_order" AS (SELECT concat("p"."code", CAST($51 AS text),
"p"."name") AS "key", sum("po"."qty_due") AS "on_order", sum((CAST("po"."qty_due" AS float8) * "po"."unit_price")) AS "on_order_retail" FROM "po_lines" AS "po" INNER JOIN "products" AS "p" ON ("p"."id" = "po"."product_id") INNER JOIN "categories" AS "c" ON ("c"."id" = "p"."category_id") WHERE ("po"."vendor_id" = ANY($52) AND "po"."vendor_id" = ANY($53) AND "c"."department" = ANY($54) AND "po"."product_id" IN (SELECT "kw"."product_id" FROM "product_keywords" AS "kw" WHERE ("kw"."keyword_id" = ANY($55))) AND "po"."product_id" IN (SELECT DISTINCT "s"."product_id" FROM "sales_lines" AS "s" WHERE ("s"."sold_on" BETWEEN $56 AND $57 AND "s"."unit_price" >= "s"."list_price")) AND "po"."qty_due" > $58 AND "po"."cancelled" = $59) GROUP BY concat("p"."code", CAST($60 AS text),
"p"."name")),
"ad_spend" AS (SELECT concat("p"."code", CAST($61 AS text),
"p"."name") AS "key", sum("ad"."cost") AS "ad_cost" FROM "ad_spend" AS "ad" INNER JOIN "products" AS "p" ON ("p"."id" = "ad"."product_id") INNER JOIN "categories" AS "c" ON ("c"."id" = "p"."category_id") WHERE ("ad"."vendor_id" = ANY($62) AND "ad"."vendor_id" = ANY($63) AND "c"."department" = ANY($64) AND "ad"."product_id" IN (SELECT "kw"."product_id" FROM "product_keywords" AS "kw" WHERE ("kw"."keyword_id" = ANY($65))) AND "ad"."product_id" IN (SELECT DISTINCT "s"."product_id" FROM "sales_lines" AS "s" WHERE ("s"."sold_on" BETWEEN $66 AND $67 AND "s"."unit_price" >= "s"."list_price")) AND "ad"."day" BETWEEN $68 AND $69) GROUP BY concat("p"."code", CAST($70 AS text),
"p"."name")),
"keys" AS (SELECT "sales"."key" FROM "sales" AS "sales" UNION SELECT "stock"."key" FROM "stock" AS "stock" UNION SELECT "snapshots"."key" FROM "snapshots" AS "snapshots" UNION SELECT "on_order"."key" FROM "on_order" AS "on_order" UNION SELECT "ad_spend"."key" FROM "ad_spend" AS "ad_spend"),
"report" AS (SELECT "k"."key" AS "key", COALESCE("sales"."current_units", 0) AS "current_units", COALESCE("sales"."current_sales", 0) AS "current_sales", COALESCE("sales"."current_cost", 0) AS "current_cost", COALESCE("sales"."current_margin", 0) AS "current_margin", COALESCE("sales"."returned_units", 0) AS "returned_units", COALESCE("sales"."previous_units", 0) AS "previous_units", COALESCE("sales"."previous_sales", 0) AS "previous_sales", COALESCE("sales"."third_units", 0) AS "third_units", COALESCE("sales"."third_sales", 0) AS "third_sales", COALESCE("stock"."on_hand", 0) AS "on_hand", COALESCE("stock"."on_hand_retail", 0) AS "on_hand_retail", COALESCE("snapshots"."previous_on_hand", 0) AS "previous_on_hand", COALESCE("on_order"."on_order", 0) AS "on_order", COALESCE("on_order"."on_order_retail", 0) AS "on_order_retail", COALESCE("ad_spend"."ad_cost", 0) AS "ad_cost", (CAST(COALESCE("sales"."current_units", 0) AS float8) - CAST(COALESCE("sales"."previous_units", 0) AS float8)) AS "units_change", (((CAST(COALESCE("sales"."current_units", 0) AS float8) - CAST(COALESCE("sales"."previous_units", 0) AS float8)) / NULLIF(CAST(COALESCE("sales"."previous_units", 0) AS float8), 0)) * $71) AS "units_pct", (COALESCE("sales"."current_sales", 0) - COALESCE("sales"."previous_sales", 0)) AS "sales_change", (((COALESCE("sales"."current_sales", 0) - COALESCE("sales"."previous_sales", 0)) / NULLIF(COALESCE("sales"."previous_sales", 0), 0)) * $72) AS "sales_pct", ((COALESCE("sales"."current_margin", 0) / NULLIF(COALESCE("sales"."current_sales", 0), 0)) * $73) AS "margin_pct", ((CAST(COALESCE("sales"."current_units", 0) AS float8) / NULLIF((CAST(COALESCE("sales"."current_units", 0) AS float8) + CAST(COALESCE("stock"."on_hand", 0) AS float8)), 0)) * $74) AS "sell_through", ((CAST(COALESCE("sales"."returned_units", 0) AS float8) / NULLIF((CAST(COALESCE("sales"."current_units", 0) AS float8) + CAST(COALESCE("sales"."returned_units", 0) AS float8)), 0)) * $75) AS "returns_pct", (COALESCE("sales"."current_sales", 0) / NULLIF(COALESCE("ad_spend"."ad_cost", 0), 0)) AS "roas" FROM "keys" AS "k" LEFT JOIN "sales" AS "sales" USING ("key") LEFT JOIN "stock" AS "stock" USING ("key") LEFT JOIN "snapshots" AS "snapshots" USING ("key") LEFT JOIN "on_order" AS "on_order" USING ("key") LEFT JOIN "ad_spend" AS "ad_spend" USING ("key")),
"labels" AS (SELECT concat("p"."code", CAST($76 AS text),
"p"."name") AS "key", concat("p"."code", CAST($77 AS text),
"p"."name") AS "label", min("p"."image_url") AS "image_url" FROM "products" AS "p" GROUP BY "p"."code", "p"."name")
SELECT "r"."key", COALESCE("l"."label", "r"."key"),
"l"."image_url", "r"."current_units", "r"."current_sales", "r"."current_cost", "r"."current_margin", "r"."returned_units", "r"."previous_units", "r"."previous_sales", "r"."third_units", "r"."third_sales", "r"."on_hand", "r"."on_hand_retail", "r"."previous_on_hand", "r"."on_order", "r"."on_order_retail", "r"."ad_cost", "r"."units_change", "r"."units_pct", "r"."sales_change", "r"."sales_pct", "r"."margin_pct", "r"."sell_through", "r"."returns_pct", "r"."roas", count(*) OVER () FROM "report" AS "r" LEFT JOIN "labels" AS "l" USING ("key") WHERE ("r"."key" = ANY($78) AND "r"."sell_through" > $79) ORDER BY "r"."roas" DESC NULLS LAST, "r"."key" ASC LIMIT $80 OFFSET $81
args: 81
skipped: [on_order: StoreIDs ad_spend: StoreIDs]
Example (StoreOmitsStorelessSources)

Grouping by store: sources without a store column are left out and their measures are typed zeros. The build says so.

package main

import (
	"fmt"
	"strings"
	"time"

	"github.com/ubgo/sqlb/examples/pgsurface/periodcompare"
)

var (
	cur  = periodcompare.DateRange{From: date(2026, 9, 1), To: date(2026, 9, 30)}
	prev = periodcompare.DateRange{From: date(2025, 9, 2), To: date(2025, 10, 1)}
)

func date(y int, m time.Month, d int) time.Time { return time.Date(y, m, d, 0, 0, 0, 0, time.UTC) }

// show prints the SQL split at the CTE boundaries so a reader can follow it.
func show(r periodcompare.Result, err error) {
	if err != nil {
		fmt.Println("error:", err)
		return
	}
	sql := r.Query.SQL
	sql = strings.ReplaceAll(sql, `), "`, "),\n\"")
	sql = strings.ReplaceAll(sql, ") SELECT ", ")\nSELECT ")
	fmt.Println(sql)
	fmt.Println("args:", len(r.Query.Args))
	if len(r.DisabledSources) > 0 {
		fmt.Println("disabled:", r.DisabledSources)
	}
	if len(r.SkippedFilters) > 0 {
		fmt.Println("skipped:", r.SkippedFilters)
	}
}

func main() {
	show(periodcompare.Build(periodcompare.Params{
		Dimension: periodcompare.ByStore,
		Current:   cur,
		Previous:  prev,
		Include:   periodcompare.Include{OnOrder: true, AdSpend: true},
		Filters:   periodcompare.Filters{StoreIDs: []int32{10}},
	}))
}
Output:
WITH "sales" AS (SELECT "st"."name" AS "key", sum("s"."qty") FILTER (WHERE "s"."sold_on" BETWEEN $1 AND $2) AS "current_units", sum((CAST("s"."qty" AS float8) * "s"."unit_price")) FILTER (WHERE "s"."sold_on" BETWEEN $3 AND $4) AS "current_sales", sum((CAST("s"."qty" AS float8) * "s"."unit_cost")) FILTER (WHERE "s"."sold_on" BETWEEN $5 AND $6) AS "current_cost", sum(((CAST("s"."qty" AS float8) * "s"."unit_price") - (CAST("s"."qty" AS float8) * "s"."unit_cost"))) FILTER (WHERE "s"."sold_on" BETWEEN $7 AND $8) AS "current_margin", sum(CASE WHEN "s"."qty" < $9 THEN (-"s"."qty") ELSE $10 END) FILTER (WHERE "s"."sold_on" BETWEEN $11 AND $12) AS "returned_units", sum("s"."qty") FILTER (WHERE "s"."sold_on" BETWEEN $13 AND $14) AS "previous_units", sum((CAST("s"."qty" AS float8) * "s"."unit_price")) FILTER (WHERE "s"."sold_on" BETWEEN $15 AND $16) AS "previous_sales" FROM "sales_lines" AS "s" INNER JOIN "stores" AS "st" ON ("st"."id" = "s"."store_id") WHERE ("s"."store_id" = ANY($17) AND "s"."is_gift" = $18 AND "s"."sold_on" BETWEEN $19 AND $20) GROUP BY "st"."name"),
"stock" AS (SELECT "st"."name" AS "key", sum("sl"."on_hand") AS "on_hand", sum((CAST("sl"."on_hand" AS float8) * "sl"."retail_price")) AS "on_hand_retail" FROM "stock_levels" AS "sl" INNER JOIN "stores" AS "st" ON ("st"."id" = "sl"."store_id") WHERE ("sl"."store_id" = ANY($21)) GROUP BY "st"."name"),
"keys" AS (SELECT "sales"."key" FROM "sales" AS "sales" UNION SELECT "stock"."key" FROM "stock" AS "stock"),
"report" AS (SELECT "k"."key" AS "key", COALESCE("sales"."current_units", 0) AS "current_units", COALESCE("sales"."current_sales", 0) AS "current_sales", COALESCE("sales"."current_cost", 0) AS "current_cost", COALESCE("sales"."current_margin", 0) AS "current_margin", COALESCE("sales"."returned_units", 0) AS "returned_units", COALESCE("sales"."previous_units", 0) AS "previous_units", COALESCE("sales"."previous_sales", 0) AS "previous_sales", 0 AS "third_units", 0 AS "third_sales", COALESCE("stock"."on_hand", 0) AS "on_hand", COALESCE("stock"."on_hand_retail", 0) AS "on_hand_retail", 0 AS "previous_on_hand", 0 AS "on_order", 0 AS "on_order_retail", 0 AS "ad_cost", (CAST(COALESCE("sales"."current_units", 0) AS float8) - CAST(COALESCE("sales"."previous_units", 0) AS float8)) AS "units_change", (((CAST(COALESCE("sales"."current_units", 0) AS float8) - CAST(COALESCE("sales"."previous_units", 0) AS float8)) / NULLIF(CAST(COALESCE("sales"."previous_units", 0) AS float8), 0)) * $22) AS "units_pct", (COALESCE("sales"."current_sales", 0) - COALESCE("sales"."previous_sales", 0)) AS "sales_change", (((COALESCE("sales"."current_sales", 0) - COALESCE("sales"."previous_sales", 0)) / NULLIF(COALESCE("sales"."previous_sales", 0), 0)) * $23) AS "sales_pct", ((COALESCE("sales"."current_margin", 0) / NULLIF(COALESCE("sales"."current_sales", 0), 0)) * $24) AS "margin_pct", ((CAST(COALESCE("sales"."current_units", 0) AS float8) / NULLIF((CAST(COALESCE("sales"."current_units", 0) AS float8) + CAST(COALESCE("stock"."on_hand", 0) AS float8)), 0)) * $25) AS "sell_through", ((CAST(COALESCE("sales"."returned_units", 0) AS float8) / NULLIF((CAST(COALESCE("sales"."current_units", 0) AS float8) + CAST(COALESCE("sales"."returned_units", 0) AS float8)), 0)) * $26) AS "returns_pct", NULL AS "roas" FROM "keys" AS "k" LEFT JOIN "sales" AS "sales" USING ("key") LEFT JOIN "stock" AS "stock" USING ("key"))
SELECT "r"."key", "r"."key", NULL, "r"."current_units", "r"."current_sales", "r"."current_cost", "r"."current_margin", "r"."returned_units", "r"."previous_units", "r"."previous_sales", "r"."third_units", "r"."third_sales", "r"."on_hand", "r"."on_hand_retail", "r"."previous_on_hand", "r"."on_order", "r"."on_order_retail", "r"."ad_cost", "r"."units_change", "r"."units_pct", "r"."sales_change", "r"."sales_pct", "r"."margin_pct", "r"."sell_through", "r"."returns_pct", "r"."roas", count(*) OVER () FROM "report" AS "r" ORDER BY "r"."key" ASC NULLS LAST LIMIT $27 OFFSET $28
args: 28
disabled: [snapshots on_order ad_spend]
Example (VendorBasic)

The smallest report: sales and stock by vendor, two periods.

package main

import (
	"fmt"
	"strings"
	"time"

	"github.com/ubgo/sqlb/examples/pgsurface/periodcompare"
)

var (
	cur  = periodcompare.DateRange{From: date(2026, 9, 1), To: date(2026, 9, 30)}
	prev = periodcompare.DateRange{From: date(2025, 9, 2), To: date(2025, 10, 1)}
)

func date(y int, m time.Month, d int) time.Time { return time.Date(y, m, d, 0, 0, 0, 0, time.UTC) }

// show prints the SQL split at the CTE boundaries so a reader can follow it.
func show(r periodcompare.Result, err error) {
	if err != nil {
		fmt.Println("error:", err)
		return
	}
	sql := r.Query.SQL
	sql = strings.ReplaceAll(sql, `), "`, "),\n\"")
	sql = strings.ReplaceAll(sql, ") SELECT ", ")\nSELECT ")
	fmt.Println(sql)
	fmt.Println("args:", len(r.Query.Args))
	if len(r.DisabledSources) > 0 {
		fmt.Println("disabled:", r.DisabledSources)
	}
	if len(r.SkippedFilters) > 0 {
		fmt.Println("skipped:", r.SkippedFilters)
	}
}

func main() {
	show(periodcompare.Build(periodcompare.Params{
		Dimension: periodcompare.ByVendor,
		Current:   cur,
		Previous:  prev,
		Page:      periodcompare.Page{Size: 50, Number: 1},
	}))
}
Output:
WITH "sales" AS (SELECT "v"."name" AS "key", sum("s"."qty") FILTER (WHERE "s"."sold_on" BETWEEN $1 AND $2) AS "current_units", sum((CAST("s"."qty" AS float8) * "s"."unit_price")) FILTER (WHERE "s"."sold_on" BETWEEN $3 AND $4) AS "current_sales", sum((CAST("s"."qty" AS float8) * "s"."unit_cost")) FILTER (WHERE "s"."sold_on" BETWEEN $5 AND $6) AS "current_cost", sum(((CAST("s"."qty" AS float8) * "s"."unit_price") - (CAST("s"."qty" AS float8) * "s"."unit_cost"))) FILTER (WHERE "s"."sold_on" BETWEEN $7 AND $8) AS "current_margin", sum(CASE WHEN "s"."qty" < $9 THEN (-"s"."qty") ELSE $10 END) FILTER (WHERE "s"."sold_on" BETWEEN $11 AND $12) AS "returned_units", sum("s"."qty") FILTER (WHERE "s"."sold_on" BETWEEN $13 AND $14) AS "previous_units", sum((CAST("s"."qty" AS float8) * "s"."unit_price")) FILTER (WHERE "s"."sold_on" BETWEEN $15 AND $16) AS "previous_sales" FROM "sales_lines" AS "s" INNER JOIN "vendors" AS "v" ON ("v"."id" = "s"."vendor_id") WHERE ("s"."is_gift" = $17 AND "s"."sold_on" BETWEEN $18 AND $19) GROUP BY "v"."name"),
"stock" AS (SELECT "v"."name" AS "key", sum("sl"."on_hand") AS "on_hand", sum((CAST("sl"."on_hand" AS float8) * "sl"."retail_price")) AS "on_hand_retail" FROM "stock_levels" AS "sl" INNER JOIN "vendors" AS "v" ON ("v"."id" = "sl"."vendor_id") GROUP BY "v"."name"),
"keys" AS (SELECT "sales"."key" FROM "sales" AS "sales" UNION SELECT "stock"."key" FROM "stock" AS "stock"),
"report" AS (SELECT "k"."key" AS "key", COALESCE("sales"."current_units", 0) AS "current_units", COALESCE("sales"."current_sales", 0) AS "current_sales", COALESCE("sales"."current_cost", 0) AS "current_cost", COALESCE("sales"."current_margin", 0) AS "current_margin", COALESCE("sales"."returned_units", 0) AS "returned_units", COALESCE("sales"."previous_units", 0) AS "previous_units", COALESCE("sales"."previous_sales", 0) AS "previous_sales", 0 AS "third_units", 0 AS "third_sales", COALESCE("stock"."on_hand", 0) AS "on_hand", COALESCE("stock"."on_hand_retail", 0) AS "on_hand_retail", 0 AS "previous_on_hand", 0 AS "on_order", 0 AS "on_order_retail", 0 AS "ad_cost", (CAST(COALESCE("sales"."current_units", 0) AS float8) - CAST(COALESCE("sales"."previous_units", 0) AS float8)) AS "units_change", (((CAST(COALESCE("sales"."current_units", 0) AS float8) - CAST(COALESCE("sales"."previous_units", 0) AS float8)) / NULLIF(CAST(COALESCE("sales"."previous_units", 0) AS float8), 0)) * $20) AS "units_pct", (COALESCE("sales"."current_sales", 0) - COALESCE("sales"."previous_sales", 0)) AS "sales_change", (((COALESCE("sales"."current_sales", 0) - COALESCE("sales"."previous_sales", 0)) / NULLIF(COALESCE("sales"."previous_sales", 0), 0)) * $21) AS "sales_pct", ((COALESCE("sales"."current_margin", 0) / NULLIF(COALESCE("sales"."current_sales", 0), 0)) * $22) AS "margin_pct", ((CAST(COALESCE("sales"."current_units", 0) AS float8) / NULLIF((CAST(COALESCE("sales"."current_units", 0) AS float8) + CAST(COALESCE("stock"."on_hand", 0) AS float8)), 0)) * $23) AS "sell_through", ((CAST(COALESCE("sales"."returned_units", 0) AS float8) / NULLIF((CAST(COALESCE("sales"."current_units", 0) AS float8) + CAST(COALESCE("sales"."returned_units", 0) AS float8)), 0)) * $24) AS "returns_pct", NULL AS "roas" FROM "keys" AS "k" LEFT JOIN "sales" AS "sales" USING ("key") LEFT JOIN "stock" AS "stock" USING ("key"))
SELECT "r"."key", "r"."key", NULL, "r"."current_units", "r"."current_sales", "r"."current_cost", "r"."current_margin", "r"."returned_units", "r"."previous_units", "r"."previous_sales", "r"."third_units", "r"."third_sales", "r"."on_hand", "r"."on_hand_retail", "r"."previous_on_hand", "r"."on_order", "r"."on_order_retail", "r"."ad_cost", "r"."units_change", "r"."units_pct", "r"."sales_change", "r"."sales_pct", "r"."margin_pct", "r"."sell_through", "r"."returns_pct", "r"."roas", count(*) OVER () FROM "report" AS "r" ORDER BY "r"."key" ASC NULLS LAST LIMIT $25 OFFSET $26
args: 26
disabled: [snapshots on_order ad_spend]

Index

Examples

Constants

View Source
const (
	DefaultPageSize = 100
	MaxPageSize     = 5000
)

Defaults.

Variables

View Source
var (
	ErrUnknownDimension = errors.New("periodcompare: unknown dimension")
	ErrUnknownMetric    = errors.New("periodcompare: unknown metric in filter or sort")
	ErrUnknownOperator  = errors.New("periodcompare: unknown numeric operator")
	ErrFiscalYear       = errors.New("periodcompare: fiscal-month dimension needs FiscalYear")
)

The errors a request can provoke. Each means the caller named something outside a closed set, which is the only way request data can fail here: values bind, and names are matched rather than interpolated.

View Source
var (
	Products   = sqlb.Table{Name: "products", Alias: "p"}
	Vendors    = sqlb.Table{Name: "vendors", Alias: "v"}
	Categories = sqlb.Table{Name: "categories", Alias: "c"}
	Stores     = sqlb.Table{Name: "stores", Alias: "st"}
	Calendar   = sqlb.Table{Name: "fiscal_calendar", Alias: "fc"}
	Keywords   = sqlb.Table{Name: "product_keywords", Alias: "kw"}

	SalesLines     = sqlb.Table{Name: "sales_lines", Alias: "s"}
	StockLevels    = sqlb.Table{Name: "stock_levels", Alias: "sl"}
	StockSnapshots = sqlb.Table{Name: "stock_snapshots", Alias: "ss"}
	POLines        = sqlb.Table{Name: "po_lines", Alias: "po"}
	AdSpend        = sqlb.Table{Name: "ad_spend", Alias: "ad"}
)

Fact and dimension tables. Aliases are per statement; every source is its own CTE, so the product and category aliases repeat across sources without collision.

View Source
var (
	ProductID       = sqlb.Col[string](Products, "id")
	ProductVendorID = sqlb.Col[string](Products, "vendor_id")
	ProductCatID    = sqlb.Col[string](Products, "category_id")
	ProductName     = sqlb.Col[string](Products, "name")
	ProductCode     = sqlb.Col[string](Products, "code")
	ProductImage    = sqlb.Col[*string](Products, "image_url")

	VendorID   = sqlb.Col[string](Vendors, "id")
	VendorName = sqlb.Col[string](Vendors, "name")

	CategoryID   = sqlb.Col[string](Categories, "id")
	Department   = sqlb.Col[string](Categories, "department")
	ClassName    = sqlb.Col[string](Categories, "class_name")
	CategoryName = sqlb.Col[string](Categories, "name")

	StoreID   = sqlb.Col[int32](Stores, "id")
	StoreName = sqlb.Col[string](Stores, "name")

	CalDay         = sqlb.Col[time.Time](Calendar, "day")
	CalFiscalYear  = sqlb.Col[int32](Calendar, "fiscal_year")
	CalPeriodIndex = sqlb.Col[int32](Calendar, "period_index")
	CalPeriodLabel = sqlb.Col[string](Calendar, "period_label")

	KwProductID = sqlb.Col[string](Keywords, "product_id")
	KwKeywordID = sqlb.Col[int64](Keywords, "keyword_id")

	SaleProductID = sqlb.Col[string](SalesLines, "product_id")
	SaleVendorID  = sqlb.Col[string](SalesLines, "vendor_id")
	SaleStoreID   = sqlb.Col[int32](SalesLines, "store_id")
	SaleSoldOn    = sqlb.Col[time.Time](SalesLines, "sold_on")
	SaleQty       = sqlb.Col[int64](SalesLines, "qty")
	SaleUnitPrice = sqlb.Col[float64](SalesLines, "unit_price")
	SaleListPrice = sqlb.Col[float64](SalesLines, "list_price")
	SaleUnitCost  = sqlb.Col[float64](SalesLines, "unit_cost")
	SaleIsGift    = sqlb.Col[bool](SalesLines, "is_gift")

	StockProductID = sqlb.Col[string](StockLevels, "product_id")
	StockVendorID  = sqlb.Col[string](StockLevels, "vendor_id")
	StockStoreID   = sqlb.Col[int32](StockLevels, "store_id")
	StockOnHand    = sqlb.Col[int64](StockLevels, "on_hand")
	StockRetail    = sqlb.Col[float64](StockLevels, "retail_price")

	SnapProductID = sqlb.Col[string](StockSnapshots, "product_id")
	SnapVendorID  = sqlb.Col[string](StockSnapshots, "vendor_id")
	SnapStoreID   = sqlb.Col[int32](StockSnapshots, "store_id")
	SnapDate      = sqlb.Col[time.Time](StockSnapshots, "snapshot_date")
	SnapQty       = sqlb.Col[int64](StockSnapshots, "qty")
	SnapRetail    = sqlb.Col[float64](StockSnapshots, "retail_price")

	POProductID = sqlb.Col[string](POLines, "product_id")
	POVendorID  = sqlb.Col[string](POLines, "vendor_id")
	POQtyDue    = sqlb.Col[int64](POLines, "qty_due")
	POUnitPrice = sqlb.Col[float64](POLines, "unit_price")
	POCancelled = sqlb.Col[bool](POLines, "cancelled")

	AdProductID = sqlb.Col[string](AdSpend, "product_id")
	AdVendorID  = sqlb.Col[string](AdSpend, "vendor_id")
	AdDay       = sqlb.Col[time.Time](AdSpend, "day")
	AdCost      = sqlb.Col[float64](AdSpend, "cost")
)

The typed columns of every table the report reads.

DimensionValues lists every dimension, for validation at the API edge.

Functions

This section is empty.

Types

type DateRange

type DateRange struct {
	From, To time.Time
}

DateRange is inclusive on both ends.

type Dimension

type Dimension string

Dimension is the grouping of the report. A closed set.

const (
	ByVendor      Dimension = "vendor"
	ByStore       Dimension = "store"
	ByProduct     Dimension = "product"
	ByDepartment  Dimension = "department"
	ByClass       Dimension = "class"
	ByFiscalMonth Dimension = "fiscal_month"
)

The dimensions the report can group by. A request names one of these and the value is matched against the set, never interpolated.

type Filters

type Filters struct {
	VendorIDs   []string
	StoreIDs    []int32
	Departments []string
	Classes     []string
	KeywordIDs  []int64
	// AllowedVendors is the tenant restriction: the vendors this caller may
	// see at all. Empty means unrestricted. It is applied to every source.
	AllowedVendors []string
	// ExcludeReturns keeps only positive quantities. Sales only.
	ExcludeReturns bool
	// ExcludeMarkdowns keeps lines sold at list price; IncludeMarkdowns keeps
	// lines sold below it. Sales only. On stock sources they restrict to the
	// products that sold under the same rule in the current period.
	ExcludeMarkdowns bool
	IncludeMarkdowns bool
	// KeyIn keeps only the named keys, after grouping.
	KeyIn []string
	// Numeric filters apply to computed metrics, after grouping.
	Numeric []NumericFilter
}

Filters narrow the rows. Each filter states which sources it applies to in filters.go; a filter never applies silently to fewer sources than the caller expects, because Build reports what it skipped.

type Include

type Include struct {
	ThirdYear bool
	OnOrder   bool
	AdSpend   bool
	Snapshot  bool
}

Include switches optional sources on.

type Metric

type Metric string

Metric names every column of the report CTE that a caller may sort or filter on. It is the public vocabulary of the report and a closed set, so a sort field from a request is matched against it and never interpolated.

const (
	MetricKey           Metric = "key"
	MetricCurrentUnits  Metric = "current_units"
	MetricCurrentSales  Metric = "current_sales"
	MetricCurrentCost   Metric = "current_cost"
	MetricCurrentMargin Metric = "current_margin"
	MetricReturnedUnits Metric = "returned_units"
	MetricPreviousUnits Metric = "previous_units"
	MetricPreviousSales Metric = "previous_sales"
	MetricThirdUnits    Metric = "third_units"
	MetricThirdSales    Metric = "third_sales"
	MetricOnHand        Metric = "on_hand"
	MetricOnHandRetail  Metric = "on_hand_retail"
	MetricPrevOnHand    Metric = "previous_on_hand"
	MetricOnOrder       Metric = "on_order"
	MetricOnOrderRetail Metric = "on_order_retail"
	MetricAdCost        Metric = "ad_cost"
	MetricUnitsChange   Metric = "units_change"
	MetricUnitsPct      Metric = "units_pct"
	MetricSalesChange   Metric = "sales_change"
	MetricSalesPct      Metric = "sales_pct"
	MetricMarginPct     Metric = "margin_pct"
	MetricSellThrough   Metric = "sell_through"
	MetricReturnsPct    Metric = "returns_pct"
	MetricROAS          Metric = "roas"
)

Every metric the report computes. A sort or filter field from a request is matched against this set, which is what keeps request text out of the SQL.

type NumericFilter

type NumericFilter struct {
	Metric Metric
	Op     NumericOp
	Value  float64
}

NumericFilter compares a computed metric with a value, after aggregation.

type NumericOp

type NumericOp string

NumericOp is the closed set of comparisons a numeric filter may use.

const (
	OpGreater   NumericOp = "gt"
	OpGreaterEq NumericOp = "gte"
	OpLess      NumericOp = "lt"
	OpLessEq    NumericOp = "lte"
	OpEqual     NumericOp = "eq"
)

The comparisons a numeric filter may use.

type Page

type Page struct {
	Size   int
	Number int
}

Page is 1-based.

type Params

type Params struct {
	Dimension Dimension
	Current   DateRange
	Previous  DateRange
	Third     DateRange
	// FiscalYear is the year whose period labels decorate a fiscal-month
	// report. Required for ByFiscalMonth, ignored otherwise.
	FiscalYear int32
	Filters    Filters
	Include    Include
	Sort       Sort
	Page       Page
}

Params is one report request.

type Result

type Result struct {
	Query sqlb.Query
	// Row is the scan destination bound into Query. Copy it after each scan.
	Row *Row
	// DisabledSources names sources that cannot serve the dimension or were
	// not included; their measures are zeros in every row.
	DisabledSources []string
	// SkippedFilters names filters a source could not apply, as "source: filter".
	SkippedFilters []string
}

Result is the built statement plus what the build left out. A caller that shows the report can show the omissions beside it instead of showing zeros that look like data.

func Build

func Build(p Params) (Result, error)

Build renders the report for p. One statement returns the page and the total count.

type Row

type Row struct {
	Key      string
	Label    string
	ImageURL *string

	CurrentUnits  int64
	CurrentSales  float64
	CurrentCost   float64
	CurrentMargin float64
	ReturnedUnits int64
	PreviousUnits int64
	PreviousSales float64
	ThirdUnits    int64
	ThirdSales    float64
	OnHand        int64
	OnHandRetail  float64
	PrevOnHand    int64
	OnOrder       int64
	OnOrderRetail float64
	AdCost        float64

	UnitsChange float64
	UnitsPct    *float64
	SalesChange float64
	SalesPct    *float64
	MarginPct   *float64
	SellThrough *float64
	ReturnsPct  *float64
	ROAS        *float64

	// Total is the row count before pagination, from count(*) OVER ().
	Total int64
}

Row is the scan destination. Ratios are pointers because a zero denominator yields NULL, which is the honest value and not 0.

type Sort

type Sort struct {
	By  Metric
	Dir SortDir
}

Sort names a metric or the key, and a direction.

type SortDir

type SortDir string

SortDir is ASC or DESC.

const (
	Asc  SortDir = "asc"
	Desc SortDir = "desc"
)

The two sort directions.

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL