SQL Function Reference¶

This page documents SQL functions and aggregate/window additions recently implemented in DecentDB.

For broader syntax coverage, see the SQL reference and feature matrix.

Compatibility catalog and introspection surfaces¶

DecentDB exposes a narrow read-only compatibility layer for SQLite and PostgreSQL-adjacent tooling. These are virtual SQL surfaces, not persistent catalog tables.

SQLite compatibility catalog views¶

SELECT * FROM sqlite_schema;
SELECT * FROM sqlite_master;
SELECT * FROM sqlite_temp_schema;
SELECT * FROM temp.sqlite_schema;

sqlite_schema and sqlite_master expose type, name, tbl_name, rootpage, and sql. rootpage is always 0 because DecentDB does not expose SQLite B-tree root pages. Temporary schema aliases expose session-scoped temp tables, views, and indexes.

Minimal information_schema¶

SELECT * FROM information_schema.schemata;
SELECT * FROM information_schema.tables;
SELECT * FROM information_schema.columns;

information_schema.schemata includes main, temp, and registered schemas. information_schema.tables and information_schema.columns expose visible persistent and temporary table/view metadata with DecentDB type names.

SQLite-compatible PRAGMA table functions¶

The following functions mirror the corresponding PRAGMA shapes and may be used in FROM clauses, joins, filters, and projections:

SELECT * FROM pragma_table_info('users');
SELECT * FROM pragma_table_xinfo('users');
SELECT * FROM pragma_table_list();
SELECT * FROM pragma_index_list('users');
SELECT * FROM pragma_index_info('users_name_idx');
SELECT * FROM pragma_index_xinfo('users_name_idx');
SELECT * FROM pragma_foreign_key_list('orders');
SELECT * FROM pragma_database_list();

main. and temp. prefixes are accepted for these functions. Unknown table or index names return an empty result set for the table-valued helpers.

generate_series¶

generate_series(start, stop [, step]) returns one visible column named value. It supports inclusive integer series with an optional integer step, timestamp series with an explicit INTERVAL, and date series with an explicit whole-day INTERVAL.

SELECT value FROM generate_series(1, 5);
SELECT value FROM generate_series(5, 1, -1);
SELECT value FROM generate_series(
  TIMESTAMP '2026-01-01 00:00:00',
  TIMESTAMP '2026-01-01 02:00:00',
  INTERVAL '1 hour'
);

Zero steps are rejected, temporal series require an explicit interval, and a series may not produce more than 1,000,000 rows.

Compatibility scalar helpers¶

• current_database() and current_schema() return main.
• database() and schema() are compatibility aliases returning main.
• current_audit_context(key) returns the current connection-local audit context value for key, or NULL.
• current_tenant() returns audit context tenant_id or tenant, or NULL.
• current_actor() returns audit context actor or user, or NULL.
• version() returns a DecentDB version string.
• sqlite_version() and pg_backend_pid() are rejected rather than returning misleading SQLite or PostgreSQL server values.

Audit context inspection¶

sys_audit_context exposes the current handle's audit context as key / value rows.

SET AUDIT CONTEXT tenant_id = 'tenant-a';
SET AUDIT CONTEXT actor = 'alice@example.com';

SELECT current_tenant(), current_actor();
SELECT key, value FROM sys_audit_context ORDER BY key;

Audit context is connection-local. It is used by row policies, column masks, and security audit event rows.

Full-Text Search Functions¶

Full-text search is exposed through ordinary SQL functions over USING fulltext indexes.

Example:

CREATE INDEX idx_docs_search
ON docs USING fulltext (title, body)
WITH (prefix = '2,3');

SELECT id, title, bm25('idx_docs_search') AS rank
FROM docs
WHERE fulltext_match('idx_docs_search', '"embedded database" OR search')
ORDER BY rank DESC
LIMIT 20;

The query string supports terms, quoted phrases, uppercase OR, exclusions with -term, and prefix terms such as dec* when the index has matching prefix lengths. Invalid full-text query syntax returns an FTS query error: SQL error. Calling bm25(...) without a compatible same-block fulltext_match(...) returns an FTS semantic error:.

Operational inspection views¶

DecentDB exposes operational inspection surfaces through stable, read-only sys.* views. These are not persistent catalog tables and do not accept bind parameters. Use the documented SELECT * forms; arbitrary projection, joins, LIMIT, and bind parameters are not part of this surface.

The canonical operational surfaces are:

SELECT * FROM sys.sync_status;
SELECT * FROM sys.wal_metrics;
SELECT * FROM sys.process_coordination;
SELECT * FROM sys.process_readers;
SELECT * FROM sys.process_lock_metrics;
SELECT * FROM sys.write_queue_metrics;
SELECT * FROM sys.storage_metrics;
SELECT * FROM sys.reactive_metrics;
SELECT * FROM sys.reactive_subscriptions;
SELECT * FROM sys.extensions;
SELECT * FROM sys.extension_functions;
SELECT * FROM sys.extension_collations;
SELECT * FROM sys.extension_dependencies;
SELECT * FROM sys.extension_validation;
SELECT * FROM sys.sessions;
SELECT * FROM sys.slow_queries;
SELECT * FROM sys.lock_waits;
SELECT * FROM sys.index_usage;
SELECT * FROM sys.doctor_findings;
SELECT * FROM sys.fix_plan;
SELECT * FROM sys.plan_cache;
SELECT * FROM sys.plan_cache_summary;

sys.plan_cache returns one row per cached entry with the cache key hash (SipHash-style, not portable across engine versions), the persistent and temp schema cookies, the policy/mask generation, hit count, last-used timestamp, and a closed-enum statement category. sys.plan_cache_summary returns one row with aggregate counters (total_entries, total_hits, total_misses, total_evictions, total_size_bytes, max_size_bytes, total_oversized_refusals, hit_rate). Neither view exposes the full SQL text of cached statements. Use PRAGMA flush_plan_cache to evict all entries and reset the counters. See design/_archive/WIN_QUERY_PLAN_CACHING_AND_STATEMENT_REUSE.md and ADR 0190-0194 for the full contract.

sys.doctor_findings includes current-connection plan-cache guidance when the cache is disabled, oversized entries are refused, eviction churn is observed, or sustained lookup history shows a low hit rate.

Legacy sys_sync_* names remain for sync inspection compatibility:

• sys_sync_status
• sys_sync_journal
• sys_sync_retention
• sys_sync_peer_lag
• sys_sync_peers
• sys_sync_scopes
• sys_sync_scope_tables
• sys_sync_peer_scopes
• sys_sync_sessions
• sys_sync_conflict_policy
• sys_sync_conflicts
• sys_sync_doctor

sys.sync_status¶

One row describing the local sync state. It has the same shape and values as legacy sys_sync_status.

Example:

SELECT * FROM sys.sync_status;
SELECT * FROM sys_sync_status; -- legacy compatibility

sys.write_queue_metrics¶

One row describing the current engine-owned write queue snapshot. All columns are non-null. Calling this view may initialize the lazy queue object, but it does not route direct writes through queued execution.

Example:

SELECT * FROM sys.write_queue_metrics;

sys.wal_metrics¶

One row describing the current WAL runtime state. All columns are non-null.

Example:

SELECT * FROM sys.wal_metrics;

sys.process_coordination¶

One row describing this handle's cross-process coordination mode and observed sidecar state.

sys.process_readers¶

One row per active cross-process reader slot. Empty when coordination is disabled or no external reader slots are active.

sys.process_lock_metrics¶

One row describing cross-process lock wait counters and current lock owners.

sys.storage_metrics¶

One row describing the current database file and storage snapshot. All columns are non-null.

Example:

SELECT * FROM sys.storage_metrics;

sys.reactive_metrics¶

One row describing in-process reactive subscription state.

Example:

SELECT * FROM sys.reactive_metrics;

sys.reactive_subscriptions¶

One row per active in-process watch.

Example:

SELECT * FROM sys.reactive_subscriptions ORDER BY watch_id;

sys.sessions¶

One row per active session for the current database handle.

Example:

SELECT * FROM sys.sessions;

sys.slow_queries¶

One row per captured slow query statement. Empty when tracing is disabled or no queries have exceeded the threshold.

Example:

SELECT * FROM sys.slow_queries;

sys.lock_waits¶

One row per captured lock-wait event. Empty when lock-wait tracing is disabled or no waits have exceeded the threshold.

Example:

SELECT * FROM sys.lock_waits;

sys.index_usage¶

One row per index with observed read or write traffic. Empty when index-usage tracing is disabled or no indexes have been accessed.

Example:

SELECT * FROM sys.index_usage;

sys.doctor_findings¶

Merged findings from the static operational Doctor report and the runtime Advisor engine. Empty when no findings are present.

Example:

SELECT * FROM sys.doctor_findings;

sys.fix_plan¶

One row per safe-to-apply fix plan emitted by the Advisor engine. Empty when no plans are available.

Example:

SELECT * FROM sys.fix_plan;

Lifecycle and compatibility notes¶

• sys.write_queue_metrics is a one-row snapshot of Db::write_queue_metrics and the C ABI ddb_db_write_queue_metrics values. Counter values are accumulated for the current database handle's lazy queue lifetime and reset when the database is reopened through a new handle.
• sys.reactive_metrics and sys.reactive_subscriptions describe only in-process watch handles. They are not durable changefeed state and reset when the process exits.
• sys.storage_metrics is a one-row snapshot equivalent to Db::storage_info for stable fields, and includes both database and WAL paths.
• sys.wal_metrics is a one-row snapshot of internal WAL runtime counters such as active readers, warning state, payload versions, and checkpoint state.
• sys.process_coordination, sys.process_readers, and sys.process_lock_metrics describe native cross-process coordination for the current local database handle. They return disabled/empty snapshots on unsupported VFSes or single_process_unsafe opens.
• sys.sync_status is the canonical name for the sync status row. The sys_sync_status compatibility name remains supported.
• Runtime tracing views (sys.slow_queries, sys.lock_waits, sys.index_usage, sys.doctor_findings, sys.fix_plan) are in-memory snapshots. They do not write telemetry rows or create catalog objects, and they reset when the database handle is closed.

sys_sync_status¶

Columns:

• enabled
• replica_id
• next_sequence
• journal_path
• journal_size_bytes

Example:

SELECT * FROM sys_sync_status;

sys_sync_journal¶

Columns:

• sequence
• replica_id
• transaction_lsn
• table_name
• operation
• primary_key_json
• after_json
• schema_cookie
• committed_at_micros

Notes:

• SELECT * FROM sys_sync_journal returns the full journal.
• SELECT * FROM sys_sync_journal WHERE sequence > 42 is also recognized by the engine for incremental inspection.
• Ordered variants on sequence are accepted.

Example:

SELECT * FROM sys_sync_journal WHERE sequence > 100 ORDER BY sequence;

sys_sync_peers¶

Columns:

• name
• endpoint
• token_env
• created_at_micros
• updated_at_micros

sys_sync_retention¶

Columns:

• journal_records
• first_sequence
• last_sequence
• safe_prune_through
• prunable_records
• blocked_by_json
• journal_size_bytes

SELECT * FROM sys.sync_retention is the canonical dotted alias. Shape client checkpoints are retention blockers and appear as shape:<shape>:client:<id> in blocked_by_json.

sys_sync_peer_lag¶

Columns:

• peer_name
• remote_replica_id
• in_watermark
• out_watermark
• local_high_watermark
• in_lag
• out_lag

SELECT * FROM sys.sync_peer_lag is the canonical dotted alias.

sys.sync_relay_status¶

Columns:

• relay_id
• protocol_version
• database_replica_id
• production_mode
• secure_transport_required
• insecure_override_enabled
• active_sessions
• active_streams
• started_at_micros

sys.sync_relay_sessions¶

Columns:

• session_id
• tenant_id
• subject_id
• subject_kind
• request_id
• operation
• scope_name
• shape_id
• started_at_micros
• ended_at_micros
• status
• error
• rows_seen
• bytes_seen

sys.sync_shapes¶

Columns:

• shape_id
• name
• scope_name
• tenant_id
• allowed_roles_json
• allowed_subjects_json
• created_at_micros
• updated_at_micros
• retention_ttl_micros
• max_records
• ack_deadline_micros
• heartbeat_micros

sys.sync_shape_clients¶

Columns:

• shape_id
• tenant_id
• client_replica_id
• subject_id
• session_id
• last_ack_sequence
• last_ack_watermark
• last_changeset_id
• last_seen_at_micros
• retention_blocking
• status

sys.sync_changeset_history¶

Columns:

• changeset_id
• source_replica_id
• source_kind
• scope_name
• shape_id
• record_count
• bytes
• created_at_micros
• applied_at_micros
• outcome
• integrity_hash

sys_sync_doctor¶

Columns:

• enabled
• replica_id
• highest_severity
• journal_records
• journal_size_bytes
• unresolved_conflicts
• guidance_json

sys_sync_scopes¶

Columns:

• name
• include_tables_json
• row_filter
• filter_columns_json
• created_at_micros
• updated_at_micros

sys_sync_scope_tables¶

Columns:

• scope_name
• table_name

sys_sync_peer_scopes¶

Columns:

• peer_name
• scope_name
• created_at_micros
• updated_at_micros

sys_sync_sessions¶

Columns:

• session_id
• peer_name
• direction
• remote_replica_id
• started_at_micros
• ended_at_micros
• status
• error
• pushed_batch_id
• pulled_batch_id
• pushed_seen
• pushed_applied
• pushed_skipped
• pushed_conflicted
• pulled_seen
• pulled_applied
• pulled_skipped
• pulled_conflicted
• retry_count

sys_sync_conflict_policy¶

Columns:

• default_policy
• origin_priority_json

sys_sync_conflicts¶

Columns:

• conflict_id
• batch_id
• remote_replica_id
• remote_sequence
• table_name
• operation
• conflict_type
• message
• primary_key_json
• remote_record_json
• local_row_json
• created_at_micros
• resolved
• resolution
• resolved_at_micros
• resolved_by
• resolution_note
• policy_name
• local_record_json

Example:

SELECT * FROM sys_sync_conflicts ORDER BY conflict_id;
SELECT * FROM sys_sync_conflict_policy;

Subquery operators¶

Supported:

• EXISTS (subquery) / NOT EXISTS (subquery)
• expr op ANY (subquery) and expr op SOME (subquery) (SOME is a synonym)
• expr op ALL (subquery)

Behavior notes:

• Subquery comparison operators support =, <>/!=, <, <=, >, >=.
• ANY returns TRUE if at least one comparison is true; ALL returns TRUE only if all comparisons are true.
• Empty subquery semantics follow SQL quantifier rules: ANY yields FALSE, ALL yields TRUE.
• NULL comparison propagation follows SQL three-valued logic.

Examples:

SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

SELECT * FROM employees
WHERE salary > ANY (SELECT salary FROM peers);

SELECT * FROM employees
WHERE salary >= ALL (SELECT salary FROM peers);

Regex comparison operators¶

Supported:

• left ~ pattern (case-sensitive match)
• left ~* pattern (case-insensitive match)
• left !~ pattern (case-sensitive non-match)
• left !~* pattern (case-insensitive non-match)

Behavior notes:

• Both operands must be TEXT; otherwise an SQL type error is raised.
• NULL operands yield NULL.
• Invalid regex patterns return an SQL error.

Examples:

SELECT name FROM users WHERE name ~ '^A';
SELECT name FROM users WHERE name ~* '^admin';
SELECT name FROM users WHERE name !~ 'bot$';
SELECT name FROM users WHERE name !~* '^test_';

Math functions¶

Trigonometric¶

Supported:

• SIN(x)
• COS(x)
• TAN(x)
• ASIN(x)
• ACOS(x)
• ATAN(x)
• ATAN2(y, x)
• PI()
• DEGREES(x)
• RADIANS(x)
• COT(x)

Behavior notes:

• Numeric inputs are accepted (INT64, FLOAT64, DECIMAL); outputs are FLOAT64.
• ASIN and ACOS return NULL for out-of-domain values outside [-1, 1].
• TAN returns NULL near undefined points (odd multiples of π/2).
• COT returns NULL when tan(x) is approximately zero.

Examples:

SELECT SIN(PI() / 2), COS(0), TAN(PI() / 4);
SELECT ASIN(1), ACOS(0), ATAN2(1, 1);
SELECT DEGREES(PI()), RADIANS(180), COT(PI() / 4);

Conditional functions¶

Supported:

• GREATEST(value1, value2, ...)
• LEAST(value1, value2, ...)
• IIF(condition, then_value, else_value)

Behavior notes:

• GREATEST/LEAST return NULL if any argument is NULL.
• IIF follows CASE-like behavior and uses DecentDB truthiness semantics for the condition.

Examples:

SELECT GREATEST(10, 20, 15), LEAST(10, 20, 15);
SELECT IIF(score >= 60, 'pass', 'fail') FROM exams;

Date/time functions¶

Supported:

• DATE_TRUNC(precision, timestamp)
• DATE_PART(field, timestamp)
• DATE_DIFF(part, start, end)
• LAST_DAY(timestamp)
• NEXT_DAY(timestamp, weekday)
• MAKE_DATE(year, month, day)
• MAKE_TIMESTAMP(year, month, day, hour, minute, second)
• TO_TIMESTAMP(epoch_or_text [, format])
• AGE(timestamp [, timestamp])
• INTERVAL '...' (for timestamp arithmetic)

Behavior notes:

• DATE_TRUNC supports: microsecond, millisecond, second, minute, hour, day, week, month, quarter, year, decade, century, millennium.
• TO_TIMESTAMP(text, format) currently supports formats: YYYY-MM-DD HH24:MI:SS, YYYY-MM-DD, and DD/MM/YYYY.
• AGE returns a textual interval (for example, "1 days 00:00:00").
• INTERVAL literal parsing supports integer year/month/week/day/hour/minute/second units in amount-unit pairs.
• Timestamp interval arithmetic supports timestamp +/- INTERVAL '...' and date/timestamp text on the left side.

Examples:

SELECT DATE_TRUNC('month', '2024-03-15 14:30:45');
SELECT DATE_PART('doy', '2024-03-15');
SELECT DATE_DIFF('day', '2024-03-10', '2024-03-15');
SELECT LAST_DAY('2024-02-11'), NEXT_DAY('2024-03-15', 'Monday');
SELECT MAKE_DATE(2024, 3, 15), MAKE_TIMESTAMP(2024, 3, 15, 14, 30, 0);
SELECT TO_TIMESTAMP(1710505800), TO_TIMESTAMP('15/03/2024', 'DD/MM/YYYY');
SELECT AGE('2024-03-15', '2024-03-14');
SELECT '2024-03-15 14:30:00'::timestamp + INTERVAL '1 day';

String functions¶

Supported:

• CONCAT(expr, ...)
• CONCAT_WS(separator, expr, ...)
• POSITION(substring IN string)
• INITCAP(string)
• ASCII(string)
• REGEXP_REPLACE(string, pattern, replacement [, flags])
• SPLIT_PART(string, delimiter, index)
• STRING_TO_ARRAY(string, delimiter)
• QUOTE_IDENT(string)
• QUOTE_LITERAL(string)
• MD5(string)
• SHA256(string)

Behavior notes:

• CONCAT treats NULL arguments as empty strings.
• CONCAT_WS skips NULL value arguments; NULL separator returns NULL.
• POSITION returns 1-based positions, and 0 if no match exists.
• REGEXP_REPLACE supports g (global) and i (case-insensitive) flags.
• STRING_TO_ARRAY returns a JSON text array.

Examples:

SELECT CONCAT('hello', ' ', 'world');
SELECT CONCAT_WS(', ', 'Alice', NULL, 'Bob');
SELECT POSITION('world' IN 'hello world');
SELECT INITCAP('hello world from decentdb');
SELECT ASCII('A');
SELECT REGEXP_REPLACE('abc123def', '\d', '', 'g');
SELECT SPLIT_PART('a,b,c', ',', 2);
SELECT STRING_TO_ARRAY('a,b,c', ',');
SELECT QUOTE_IDENT('table name'), QUOTE_LITERAL('O''Brien');
SELECT MD5('hello'), SHA256('hello');

Spatial functions¶

Spatial functions operate on native GEOMETRY and GEOGRAPHY values. Spatial values are stored as normalized EWKB; GEOGRAPHY uses SRID 4326 and lon/lat coordinates.

Supported:

• Constructors: ST_Point, ST_MakePoint, ST_PointZ, ST_PointM, ST_PointZM
• Geography point constructors: ST_GeogPoint, ST_GeogPointZ, ST_GeogPointM, ST_GeogPointZM
• Import/export: ST_GeomFromText, ST_GeogFromText, ST_GeomFromWKB, ST_GeogFromWKB, ST_GeomFromGeoJSON, ST_GeogFromGeoJSON, ST_AsText, ST_AsBinary, ST_AsGeoJSON
• Accessors: ST_SRID, ST_SetSRID, ST_GeometryType, ST_X, ST_Y, ST_Z, ST_M, ST_IsValid
• Predicates: ST_DWithin, ST_Intersects, ST_Contains, ST_Within, ST_Equals
• Measurements: ST_Distance, ST_Length, ST_Area
• Distance ordering: <->

Behavior notes:

• ST_Distance returns meters for GEOGRAPHY point-to-point distance and planar units for GEOMETRY.
• ST_DWithin uses the same units as ST_Distance.
• ST_Length and ST_Area are planar for GEOMETRY; GEOGRAPHY uses spherical approximations.
• Spatial indexes (CREATE INDEX ... USING spatial) are single-column indexes for GEOMETRY and GEOGRAPHY.

Examples:

CREATE TABLE places (id INT PRIMARY KEY, geog GEOGRAPHY(POINT,4326));
CREATE INDEX idx_places_geog ON places USING spatial(geog);

INSERT INTO places VALUES (1, ST_GeogPoint(-97.7431, 30.2672));

SELECT id
FROM places
WHERE ST_DWithin(geog, ST_GeogPoint(-97.7431, 30.2672), 5000);

SELECT ST_AsText(ST_GeomFromText('POINT(1 2)'));
SELECT ST_Area(ST_GeomFromText('POLYGON((0 0,10 0,10 10,0 10,0 0))'));
SELECT id FROM places ORDER BY geog <-> ST_GeogPoint(-97.7431, 30.2672) LIMIT 10;

Aggregate functions¶

Statistical aggregates¶

Supported:

• STDDEV(expr) (alias of STDDEV_SAMP)
• STDDEV_SAMP(expr)
• STDDEV_POP(expr)
• VARIANCE(expr) (alias of VAR_SAMP)
• VAR_SAMP(expr)
• VAR_POP(expr)

Behavior notes:

• Implemented using a numerically stable online (Welford-style) accumulation strategy.
• *_SAMP forms return NULL when fewer than 2 non-NULL values exist.
• Population forms return NULL for empty input sets.
• DISTINCT is supported.

Boolean aggregates¶

Supported:

• BOOL_AND(expr)
• BOOL_OR(expr)

Behavior notes:

• NULL inputs are ignored.
• If all values are NULL, result is NULL.
• Non-boolean non-NULL inputs are rejected.

Collection and ordered-set aggregates¶

Supported:

• ARRAY_AGG(expr [ORDER BY ...])
• MEDIAN(expr)
• PERCENTILE_CONT(fraction) WITHIN GROUP (ORDER BY expr)
• PERCENTILE_DISC(fraction) WITHIN GROUP (ORDER BY expr)

Behavior notes:

• ARRAY_AGG returns JSON text arrays (for example, "[1,null,2]").
• ARRAY_AGG(DISTINCT ...) is supported.
• MEDIAN returns FLOAT64 and ignores NULL inputs.
• Percentile fraction must be between 0 and 1 inclusive.
• PERCENTILE_CONT interpolates and returns FLOAT64.
• PERCENTILE_DISC returns a value from the ordered input domain.

Examples:

SELECT STDDEV(amount), VARIANCE(amount), BOOL_AND(amount > 0), BOOL_OR(amount > 100) FROM orders;

SELECT ARRAY_AGG(amount ORDER BY created_at) FROM orders;
SELECT MEDIAN(amount) FROM orders;

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) FROM orders;
SELECT PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY amount) FROM orders;

Window functions¶

Additional supported window features include:

• NTILE(n)
• PERCENT_RANK()
• CUME_DIST()
• Aggregate window functions such as SUM(...) OVER (...), COUNT(...) OVER (...), MIN/MAX/AVG/... OVER (...)
• ROWS frame clauses
• RANGE frames for UNBOUNDED/CURRENT ROW style bounds (offset-based RANGE bounds are not yet supported)

Examples:

SELECT id, NTILE(4) OVER (ORDER BY salary DESC) AS quartile FROM employees;

SELECT id,
       PERCENT_RANK() OVER (ORDER BY score) AS pct_rank,
       CUME_DIST() OVER (ORDER BY score) AS cume_dist
FROM results;

SELECT created_at, amount,
       SUM(amount) OVER (
         ORDER BY created_at
         ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
       ) AS rolling_sum
FROM orders;