RonSQL for On-demand Transformations in a Feature Store

A feature store serves the features that machine learning models use for online inference, such as the number of transactions of a credit card in the last hour, or the categories a user clicked on in the current session. Many such features are aggregates over recent events. Precomputing them in a batch or streaming pipeline means that they lag behind the newest events, and that every variant of a feature (every time window, every N) must be computed and stored in advance.

With RonSQL, such features can instead be computed on demand, at request time, directly from the events stored in RonDB. This is called an on-demand transformation. Most of the filtering, joins and aggregation run inside the data nodes, in parallel over the table partitions, so the result is computed from the freshest data with low latency, and only the computed feature values are returned to the model.

This chapter shows typical on-demand transformations for fraud detection, personalized recommendations and predictive maintenance. The examples combine the RonSQL features described in the previous chapters: LIMIT in the main query and in CTE bodies, a single CTE and several CTEs, CTEs that produce a single row and CTEs that produce many rows. They are sent to the /ronsql endpoint of the REST API server like any RonSQL query; see the RonSQL overview.

Example tables

The examples use the following feature group tables. As in the Hopsworks online feature store, each table name carries the feature group version, and the event tables have the entity key and the event time as their primary key. This makes every per-entity time window an index range, and the primary key delivers the events of an entity in time order.

Fraud detection:

CREATE TABLE transactions_1 (
  cc_num       BIGINT NOT NULL,
  event_time   TIMESTAMP NOT NULL,
  amount       DOUBLE NOT NULL,
  merchant_id  INT NOT NULL,
  country      CHAR(2) NOT NULL,
  PRIMARY KEY (cc_num, event_time)
) ENGINE=NDB;

CREATE TABLE cards_1 (
  cc_num        BIGINT NOT NULL,
  credit_limit  DOUBLE NOT NULL,
  home_country  CHAR(2) NOT NULL,
  PRIMARY KEY (cc_num)
) ENGINE=NDB;

CREATE TABLE merchants_1 (
  merchant_id        INT NOT NULL,
  mcc                INT NOT NULL,
  fraud_reports_90d  INT NOT NULL,
  PRIMARY KEY (merchant_id)
) ENGINE=NDB;

Personalized recommendations:

CREATE TABLE clicks_1 (
  user_id      BIGINT NOT NULL,
  event_time   TIMESTAMP NOT NULL,
  item_id      BIGINT NOT NULL,
  category_id  INT NOT NULL,
  dwell_ms     INT NOT NULL,
  PRIMARY KEY (user_id, event_time)
) ENGINE=NDB;

CREATE TABLE items_1 (
  item_id      BIGINT NOT NULL,
  category_id  INT NOT NULL,
  price        DOUBLE NOT NULL,
  popularity   INT NOT NULL,
  PRIMARY KEY (item_id),
  INDEX idx_category (category_id)
) ENGINE=NDB;

CREATE TABLE item_views_1 (
  item_id     BIGINT NOT NULL,
  event_time  TIMESTAMP NOT NULL,
  user_id     BIGINT NOT NULL,
  PRIMARY KEY (item_id, event_time, user_id)
) ENGINE=NDB;

CREATE TABLE purchases_1 (
  item_id     BIGINT NOT NULL,
  event_time  TIMESTAMP NOT NULL,
  user_id     BIGINT NOT NULL,
  PRIMARY KEY (item_id, event_time, user_id)
) ENGINE=NDB;

CREATE TABLE user_profile_1 (
  user_id    BIGINT NOT NULL,
  max_price  DOUBLE NOT NULL,
  PRIMARY KEY (user_id)
) ENGINE=NDB;

Predictive maintenance:

CREATE TABLE sensor_readings_1 (
  sensor_id    INT NOT NULL,
  event_time   TIMESTAMP NOT NULL,
  temperature  DOUBLE NOT NULL,
  vibration    DOUBLE NOT NULL,
  PRIMARY KEY (sensor_id, event_time)
) ENGINE=NDB;

RonSQL has no NOW() function, so the application computes the start of each time window and passes it as a TIMESTAMP literal in UTC. The examples assume that the request is made at 2026-09-29 12:00:00 UTC.

The following table gives an overview of the examples:

Example	LIMIT	CTEs
1. Average of the last 10 transactions	CTE body	One, many rows
2. The last 5 transactions as a sequence	CTE body	One, many rows
3. Transaction velocity in several windows	None	Three, one row each
4. Spending above the 90-day maximum	None	One, one row
5. Transactions abroad	None	One, single-row lookup
6. Distinct merchants in the last 50 transactions	CTE body	Two, chained
7. Exposure to risky merchants	None	One, one row per merchant
8. Merchant categories of the last 20 transactions	CTE body	One, many rows
9. Favourite categories of a user	Main query	None
10. Candidates from the favourite categories	CTE body	One, top-N rows
11. Real-time popularity of candidate items	None	Two, one row per item
12. Candidates within the user’s price range	Main query	One, single-row lookup
13. Category profile of the current session	CTE body	One, many rows
14. Sensor statistics for a machine	None	None

Fraud detection

A fraud detection model scores a card transaction while the payment is being authorized, typically within a few milliseconds. Its most important features describe how the new transaction relates to the recent history of the card.

Example 1: Average of the last 10 transactions

A count-based window: the average and the largest amount of the card’s last 10 transactions, regardless of how long ago they happened.

WITH last10 AS (
  SELECT cc_num, event_time, amount
  FROM transactions_1
  WHERE cc_num = 4242
  ORDER BY event_time DESC
  LIMIT 10)
SELECT COUNT(*) AS n_tx, AVG(amount) AS avg_amount,
       MAX(amount) AS max_amount
FROM last10;

The CTE body selects the last N rows of the card with ORDER BY and LIMIT, and the main query only aggregates them. RonSQL executes the body as an ordered scan of the primary key, which stops after 10 rows, and computes the aggregates over the returned rows. A card with fewer than 10 transactions gives the aggregates over the transactions it has, and a card without transactions gives n_tx 0 and NULL for the other features.

Example 2: The last 5 transactions as a sequence

Sequence models, and rules comparing the new transaction with the previous ones, need the most recent transactions themselves rather than an aggregate:

WITH last5 AS (
  SELECT cc_num, event_time, amount, merchant_id, country
  FROM transactions_1
  WHERE cc_num = 4242
  ORDER BY event_time DESC
  LIMIT 5)
SELECT event_time, amount, merchant_id, country
FROM last5;

The main query passes the rows of the CTE through, so RonSQL executes the statement as the single-table query in the CTE body, and returns the rows in the order of its ORDER BY, newest first. The statement without the CTE, SELECT ... FROM transactions_1 WHERE cc_num = 4242 ORDER BY event_time DESC LIMIT 5, gives the same result.

Example 3: Transaction velocity in several time windows

A burst of transactions is a strong fraud signal. This example computes the number and the amount of the card’s transactions in the last hour, the last 24 hours and the last 7 days, in one request:

WITH w1h AS (
  SELECT COUNT(*) AS n_1h, SUM(amount) AS amt_1h
  FROM transactions_1
  WHERE cc_num = 4242 AND event_time >= '2026-09-29 11:00:00'),
     w24h AS (
  SELECT COUNT(*) AS n_24h, SUM(amount) AS amt_24h
  FROM transactions_1
  WHERE cc_num = 4242 AND event_time >= '2026-09-28 12:00:00'),
     w7d AS (
  SELECT COUNT(*) AS n_7d, MAX(amount) AS max_amount_7d
  FROM transactions_1
  WHERE cc_num = 4242 AND event_time >= '2026-09-22 12:00:00')
SELECT w1h.n_1h, w1h.amt_1h, w24h.n_24h, w24h.amt_24h,
       w7d.n_7d, w7d.max_amount_7d
FROM w1h, w24h, w7d;

Each CTE is a scalar aggregate, which always produces exactly one row, so the comma join of the three CTEs produces one row with all the features. The three CTEs are computed in parallel inside the data nodes, each as an index range scan on the primary key.

Example 4: Spending above the 90-day maximum

A transaction far above anything the card has spent before is suspicious. This example counts today’s transactions that are larger than the largest transaction of the previous 90 days:

WITH hist AS (
  SELECT MAX(amount) AS max_amount_90d
  FROM transactions_1
  WHERE cc_num = 4242
    AND event_time >= '2026-07-01 00:00:00'
    AND event_time < '2026-09-29 00:00:00')
SELECT COUNT(*) AS n_above_max, SUM(t.amount) AS amt_above_max
FROM transactions_1 AS t, hist
WHERE t.cc_num = 4242
  AND t.event_time >= '2026-09-29 00:00:00'
  AND t.amount > hist.max_amount_90d;

The scalar CTE computes a threshold, and the condition t.amount > hist.max_amount_90d compares every row of the main query with it inside the data nodes. If the card has no history, the threshold is NULL and no transaction counts as above it, as in MySQL. Comparisons with a CTE output need operands of integer, FLOAT, DOUBLE or DATE types, or strings of the same type; this is one reason the examples store amounts as DOUBLE rather than DECIMAL.

Example 5: Transactions abroad

Static attributes of an entity, such as the home country of the card holder, are read with a single-row CTE: a CTE body that looks up one row by its full primary key.

WITH card AS (
  SELECT home_country
  FROM cards_1
  WHERE cc_num = 4242)
SELECT COUNT(*) AS n_abroad_24h
FROM transactions_1 AS t, card
WHERE t.cc_num = 4242
  AND t.event_time >= '2026-09-28 12:00:00'
  AND t.country <> card.home_country;

The lookup is done once, and its columns can be used throughout the query. Both country columns are CHAR(2) with the same character set, as required when comparing strings with a CTE output. Note that if the card does not exist in cards_1, the CTE is empty and so is the join, so the count is 0.

Example 6: Distinct merchants in the last 50 transactions

A stolen card is often used at many different merchants in a short time. This example counts the distinct merchants among the card’s last 50 transactions, and the largest number of transactions at a single merchant:

WITH last50 AS (
  SELECT cc_num, event_time, merchant_id
  FROM transactions_1
  WHERE cc_num = 4242
  ORDER BY event_time DESC
  LIMIT 50),
     per_merchant AS (
  SELECT merchant_id, COUNT(*) AS n
  FROM last50
  GROUP BY merchant_id)
SELECT COUNT(*) AS n_merchants, MAX(n) AS max_tx_one_merchant
FROM per_merchant;

The CTEs are chained: the second CTE groups the rows selected by the first, and the main query aggregates the groups. Since RonSQL has no COUNT(DISTINCT ...), counting the groups of a CTE is the way to count distinct values. Because the last-N CTE is grouped by a later CTE, its body must select the whole primary key of the table (here cc_num and event_time), so that each transaction is kept as a separate row.

Example 7: Exposure to risky merchants

Merchants that are often involved in fraud are a risk signal for the cards that use them. This example aggregates the card’s last 30 days per merchant, and joins each merchant to its fraud statistics:

WITH m30 AS (
  SELECT merchant_id, COUNT(*) AS n_tx, SUM(amount) AS amt
  FROM transactions_1
  WHERE cc_num = 4242 AND event_time >= '2026-08-30 12:00:00'
  GROUP BY merchant_id)
SELECT COUNT(*) AS n_merchants_30d,
       SUM(m30.amt) AS amt_30d,
       MAX(m.fraud_reports_90d) AS max_merchant_fraud_reports
FROM m30
JOIN merchants_1 AS m ON m.merchant_id = m30.merchant_id;

The CTE produces one row per merchant. It is the first table of the main query, and merchants_1 is joined to it by its primary key, so each merchant row is a primary key lookup. The GROUP BY column of the CTE keeps the type of its source column (INT), as required for the join key. A stored table joined onto a CTE in this way must be joined by its primary key or a unique key.

Example 8: Merchant categories of the last 20 transactions

This example breaks the card’s last 20 transactions down by merchant category code (MCC), which shows if the card suddenly is used for an unusual kind of purchase:

WITH last20 AS (
  SELECT cc_num, event_time, amount, merchant_id
  FROM transactions_1
  WHERE cc_num = 4242
  ORDER BY event_time DESC
  LIMIT 20)
SELECT m.mcc, COUNT(*) AS n_tx, SUM(last20.amount) AS amt
FROM last20
JOIN merchants_1 AS m ON m.merchant_id = last20.merchant_id
GROUP BY m.mcc;

The main query joins and groups the last 20 rows. As in Example 6, the body therefore selects the whole primary key, and RonSQL selects the 20 rows inside the data nodes before the join.

Personalized recommendations

A recommendation system typically first retrieves a few hundred candidate items for a user, and then ranks them with a model that uses features of the user, of the items and of the interaction between the two. Many of these features depend on what the user did in the last minutes.

Example 9: Favourite categories of a user

The categories the user clicked on most in the last 7 days:

SELECT category_id, COUNT(*) AS n_clicks,
       SUM(dwell_ms) AS total_dwell_ms
FROM clicks_1
WHERE user_id = 17 AND event_time >= '2026-09-22 12:00:00'
GROUP BY category_id
ORDER BY n_clicks DESC
LIMIT 5;

Here LIMIT is applied to the main query: the data nodes compute the click count of every category, and RonSQL sorts the groups and returns the top 5. Groups with the same count are returned in an arbitrary order; add a second sort column, such as category_id, to make the result deterministic.

Example 10: Candidates from the favourite categories

To keep only the candidates in the user’s three favourite categories, the top-N selection moves into a CTE, and the candidate items are joined to it:

WITH top_categories AS (
  SELECT category_id, COUNT(*) AS n_clicks
  FROM clicks_1
  WHERE user_id = 17 AND event_time >= '2026-09-22 12:00:00'
  GROUP BY category_id
  ORDER BY n_clicks DESC, category_id
  LIMIT 3)
SELECT i.item_id, i.category_id, top_categories.n_clicks
FROM items_1 AS i
JOIN top_categories ON top_categories.category_id = i.category_id
WHERE i.item_id IN (101, 102, 103, 104, 105, 106, 107, 108);

The ORDER BY and LIMIT in the CTE body select which groups the CTE keeps; category_id breaks ties so that the kept set is deterministic. Candidates in other categories find no row in the CTE and are dropped by the inner join; a LEFT JOIN would keep them with NULL for n_clicks. The candidates are read with a scan of one primary key range per value in the IN list, and each candidate looks up its category in the CTE.

Example 11: Real-time popularity of candidate items

Trending items are often good recommendations. This example computes, for each candidate item, the number of views in the last hour and the number of purchases in the last 24 hours:

WITH views_1h AS (
  SELECT item_id, COUNT(*) AS n_views
  FROM item_views_1
  WHERE item_id IN (101, 102, 103, 104, 105)
    AND event_time >= '2026-09-29 11:00:00'
  GROUP BY item_id),
     purchases_24h AS (
  SELECT item_id, COUNT(*) AS n_purchases
  FROM purchases_1
  WHERE item_id IN (101, 102, 103, 104, 105)
    AND event_time >= '2026-09-28 12:00:00'
  GROUP BY item_id)
SELECT i.item_id, i.price, i.popularity,
       views_1h.n_views, purchases_24h.n_purchases
FROM items_1 AS i
LEFT JOIN views_1h ON views_1h.item_id = i.item_id
LEFT JOIN purchases_24h ON purchases_24h.item_id = i.item_id
WHERE i.item_id IN (101, 102, 103, 104, 105);

Each CTE produces one row per item with events in its window. The two CTEs are independent and are computed in parallel, and the main query returns one row per candidate item. Items without views or purchases in the window have no row in the CTE, so the LEFT JOINs give NULL for them, which the application treats as 0.

Example 12: Candidates within the user’s price range

A user profile can restrict the recommendations. This example reads the user’s maximum price with a single-row CTE, and returns the 10 most popular items of a category that are within it:

WITH user_pref AS (
  SELECT max_price
  FROM user_profile_1
  WHERE user_id = 17)
SELECT i.item_id, i.price, i.popularity
FROM items_1 AS i, user_pref
WHERE i.category_id = 5 AND i.price <= user_pref.max_price
ORDER BY i.popularity DESC
LIMIT 10;

The items of the category are read with an index scan on idx_category, filtered against the profile inside the data nodes, and sorted by RonSQL, which then returns the first 10. If the user has no profile row, the result is empty.

Example 13: Category profile of the current session

The intent of a user in the current session is best described by the last few clicks. This example aggregates the user’s last 20 clicks per category:

WITH recent_clicks AS (
  SELECT user_id, event_time, category_id, dwell_ms
  FROM clicks_1
  WHERE user_id = 17
  ORDER BY event_time DESC
  LIMIT 20)
SELECT category_id, COUNT(*) AS n_clicks,
       SUM(dwell_ms) AS total_dwell_ms
FROM recent_clicks
GROUP BY category_id;

Since the main query groups the rows of the last-N CTE, the body selects the whole primary key (user_id and event_time).

Predictive maintenance

Example 14: Sensor statistics for a machine

A predictive maintenance model looks at the recent readings of all sensors of a machine. This example computes, for each of the four sensors of a machine, the temperature range and the average vibration of the last hour:

SELECT sensor_id,
       MIN(temperature) AS min_temperature,
       MAX(temperature) AS max_temperature,
       AVG(vibration) AS avg_vibration
FROM sensor_readings_1
WHERE sensor_id IN (11, 12, 13, 14)
  AND event_time >= '2026-09-29 11:00:00'
GROUP BY sensor_id;

An IN list on the leading primary key column computes features for several entities in one request: RonSQL scans one index range per sensor and returns one row per sensor that has readings in the window. The same technique serves batch requests for many entities; up to 4095 values in an IN list are read as separate index ranges.

Guidelines

• Make the entity key the first primary key column and the event time the second. Per-entity time windows are then index ranges, and the last N rows of an entity are read in primary key order without sorting.

• Compute window boundaries in the application and pass them as TIMESTAMP literals in UTC, with a time part.

• Give every output an alias with AS. The aliases become the feature names in the JSON response.

• Select the whole primary key in a last-N CTE body when the main query joins or groups its rows (Examples 6, 8 and 13). When the main query only aggregates the rows of one last-N CTE (Example 1) or passes them through (Example 2), this is not needed, and the scan stops after N rows.

• Use a scalar CTE for a threshold or a statistic that the rows of the main query are compared with (Example 4), and a single-row CTE for attributes of an entity (Examples 5 and 12).

• Use several CTEs in one statement to compute many features in one round trip (Examples 3 and 11); independent CTEs are computed in parallel.

• Join stored tables onto a CTE that is the first table only by their primary key or a unique key (Examples 7 and 8). To combine a CTE with rows read by an index scan, make the stored table the first table and join the CTE onto it (Example 10).

• Store values that are compared with CTE outputs as integer, FLOAT, DOUBLE or DATE columns, or as strings of the same type and character set as the column they are compared with.

• Check new statements with EXPLAIN (see the RonSQL overview), which shows how each table and CTE is read.