Real-time recommendation features are the session, trending, affinity, co-occurrence, and engagement signals that tell a ranking model what a user wants right now, as opposed to what they wanted last week. This guide shows how to compute them as streaming SQL materialized views in RisingWave, a PostgreSQL-compatible streaming database, serve them at inference latency, and measure whether they move ranking metrics. It covers e-commerce product recommendations, publisher content recommendations, user behavior features for churn and personalization models, and a serving pattern that pushes engagement profiles to DynamoDB.
Long-Term Preference vs Short-Term Intent
Collaborative filtering is one of the most durable ideas in machine learning. Matrix factorization and two-tower models distill accumulated interaction history into dense embeddings that generalize across categories. They work because they encode stable long-term preferences.
Long-term preference is not current intent. A user who searched for camping gear three minutes ago, clicked two tent listings, and added a sleeping bag to their cart is telling you what they want this session. Their embedding, trained on data that is days or weeks old, still thinks they buy running shoes.
Every practical ranking model mixes the two signals. Short-term intent should dominate in-session surfaces: search results, related items, cart recommendations. Long-term preference should dominate return-visit personalization such as the home feed after two weeks away. Most models handle long-term preference well. The gap is computing short-term intent features reliably and freshly at serving time, and that is what a batch feature pipeline cannot do. A nightly job that writes "items clicked in the past 7 days" to a feature store cannot see a session that started ten minutes ago.
The Features That Move In-Session Ranking
Four families of real-time features carry most of the lift:
- Session click sequence. The ordered items and categories a user touched in the current session. It encodes what they are shopping for and which price and quality tier they are clicking in.
- Recent search queries. Explicit intent. A search for "waterproof hiking boots size 10" says more than any behavioral model infers.
- Trending in the user's context. Platform-wide trending is weak. Trending within the category, price band, or region the user is browsing is strong, and the conversion rate trend matters more than the raw view count. A product that went from 50 to 500 views in the last hour is a different item from one that has sat at 500 views for three days.
- Inventory pressure. Low stock on items the user has viewed is a genuine relevance signal, not only a merchandising trick, and it requires near-real-time inventory joined to session context.
Beyond ranking inputs, two more feature families matter for the surrounding systems: suppression (never recommend what the user just bought or dismissed) and engagement scores (how invested the user is in this session, for personalization and churn models).
Event Sources
Everything below reads from one interaction stream plus a catalog table. In production the stream is Kafka; for local testing the same shape works as a table.
CREATE SOURCE user_interactions (
event_id VARCHAR,
user_id VARCHAR,
session_id VARCHAR,
item_id VARCHAR,
event_type VARCHAR, -- 'view', 'click', 'add_to_cart', 'purchase', 'dismiss', 'search'
category VARCHAR,
price NUMERIC,
search_query VARCHAR,
scroll_depth_pct INT,
device_type VARCHAR,
event_time TIMESTAMPTZ
) WITH (
connector = 'kafka',
topic = 'user.interactions',
properties.bootstrap.server = 'kafka:9092',
scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;
CREATE TABLE item_catalog (
item_id VARCHAR PRIMARY KEY,
title VARCHAR,
category VARCHAR,
subcategory VARCHAR,
price NUMERIC,
created_at TIMESTAMPTZ
);
The catalog is typically fed by CDC from the product database. RisingWave is open source under Apache 2.0, written in Rust, and checkpoints state to S3, so the views below survive restarts and can be rebuilt by replaying Kafka.
One RisingWave detail shapes several views: NOW() is allowed in WHERE, HAVING, and ON clauses of streaming queries, where it acts as a temporal filter that adds new events and expires old ones automatically. Exponential decay inside SELECT is not available in a streaming view, so recency weighting is expressed as multiple time windows exposed as separate columns, which the model can weight as it likes.
Session Features: What the User Wants Right Now
The session intent view returns the recent category and item sequence for every active session. A 30-minute temporal filter keeps it focused on current intent:
CREATE MATERIALIZED VIEW user_session_intent AS
SELECT
user_id,
session_id,
ARRAY_AGG(category ORDER BY event_time DESC) AS recent_categories,
ARRAY_AGG(item_id ORDER BY event_time DESC) AS recent_items,
ARRAY_AGG(search_query ORDER BY event_time DESC)
FILTER (WHERE event_type = 'search') AS recent_queries,
COUNT(*) AS session_event_count,
MAX(event_time) AS last_activity
FROM user_interactions
WHERE event_type IN ('click', 'view', 'add_to_cart', 'search')
AND event_time >= NOW() - INTERVAL '30 MINUTES'
GROUP BY user_id, session_id;
The ranking service boosts candidates in the same category as recent_categories[1], the most recent category visited. A per-category breakdown with weighted engagement tells it whether the user is browsing broadly (exploration mode, where diversity matters) or deeply (purchase intent, where precision matters):
CREATE MATERIALIZED VIEW session_category_affinity AS
SELECT
session_id,
user_id,
category,
COUNT(*) AS interaction_count,
COUNT(*) FILTER (WHERE event_type = 'add_to_cart') AS cart_adds,
SUM(CASE event_type
WHEN 'view' THEN 1 WHEN 'click' THEN 3
WHEN 'add_to_cart' THEN 5 WHEN 'purchase' THEN 10 ELSE 0 END) AS engagement_score
FROM user_interactions
GROUP BY session_id, user_id, category;
Item Popularity and Trending
Raw popularity is a blunt instrument. Two windows, one hour and twenty-four hours, let you compute a velocity ratio at query time: weighted_score_1h divided by weighted_score_24h / 24 above 1.0 means the item is gaining momentum.
CREATE MATERIALIZED VIEW item_popularity_1h AS
SELECT
item_id,
category,
COUNT(*) AS interactions_1h,
COUNT(DISTINCT user_id) AS unique_users_1h,
COUNT(*) FILTER (WHERE event_type = 'purchase') AS purchases_1h,
COUNT(*) FILTER (WHERE event_type = 'purchase')::FLOAT / NULLIF(COUNT(*), 0) AS conversion_rate_1h,
SUM(CASE event_type WHEN 'view' THEN 1 WHEN 'click' THEN 2
WHEN 'add_to_cart' THEN 3 WHEN 'purchase' THEN 5 ELSE 0 END) AS weighted_score_1h
FROM user_interactions
WHERE event_time > NOW() - INTERVAL '1 HOUR'
GROUP BY item_id, category;
CREATE MATERIALIZED VIEW item_popularity_24h AS
SELECT
item_id,
category,
COUNT(*) AS interactions_24h,
COUNT(*) FILTER (WHERE event_type = 'purchase') AS purchases_24h,
SUM(CASE event_type WHEN 'view' THEN 1 WHEN 'click' THEN 2
WHEN 'add_to_cart' THEN 3 WHEN 'purchase' THEN 5 ELSE 0 END) AS weighted_score_24h
FROM user_interactions
WHERE event_time > NOW() - INTERVAL '24 HOURS'
GROUP BY item_id, category;
conversion_rate_1h is the real-time rate, not the historical average. An item that usually converts at 3 percent and is converting at 8 percent right now has changed: a price drop, a viral mention, or low inventory creating urgency. The same pattern grouped by category instead of item_id gives category-level trending for home and landing pages.
User-Item Affinity, Interaction History, and Suppression
For users with history, a soft affinity per category blends interaction strength with recency by exposing several windows:
CREATE MATERIALIZED VIEW user_category_affinity AS
SELECT
user_id,
category,
SUM(CASE event_type WHEN 'purchase' THEN 10 WHEN 'add_to_cart' THEN 5 WHEN 'click' THEN 1 ELSE 0 END)
FILTER (WHERE event_time >= NOW() - INTERVAL '1 DAY') AS affinity_1d,
SUM(CASE event_type WHEN 'purchase' THEN 10 WHEN 'add_to_cart' THEN 5 WHEN 'click' THEN 1 ELSE 0 END)
FILTER (WHERE event_time >= NOW() - INTERVAL '7 DAYS') AS affinity_7d,
SUM(CASE event_type WHEN 'purchase' THEN 10 WHEN 'add_to_cart' THEN 5 WHEN 'click' THEN 1 ELSE 0 END) AS affinity_30d,
MAX(event_time) AS last_interaction
FROM user_interactions
WHERE event_time >= NOW() - INTERVAL '30 DAYS'
GROUP BY user_id, category;
Pairwise history between a user and an item is directly useful for ranking. Three views and no purchase is a different signal from a first view:
CREATE MATERIALIZED VIEW user_item_interactions AS
SELECT
user_id,
item_id,
COUNT(*) AS total_interactions,
COUNT(*) FILTER (WHERE event_type = 'view') AS view_count,
COUNT(*) FILTER (WHERE event_type = 'add_to_cart') AS cart_count,
COUNT(*) FILTER (WHERE event_type = 'purchase') AS purchase_count,
COUNT(*) FILTER (WHERE event_type = 'dismiss') AS dismiss_count,
MIN(event_time) AS first_seen,
MAX(event_time) AS last_seen
FROM user_interactions
GROUP BY user_id, item_id;
CREATE MATERIALIZED VIEW items_to_suppress AS
SELECT DISTINCT user_id, item_id
FROM user_interactions
WHERE event_type IN ('purchase', 'dismiss');
The suppression view is the cheapest quality win in recommendations. It updates the instant a purchase or dismissal happens, so the serving layer can anti-join candidates against it and the user never sees a just-bought item recommended back.
Collaborative Signals: Co-Purchase, Co-View, and Similar Users
"Customers also bought" is a self-join on the interaction stream. Pairs are counted once with a.item_id < b.item_id, and a HAVING threshold removes noise:
CREATE MATERIALIZED VIEW item_cooccurrence AS
SELECT
a.item_id AS item_a,
b.item_id AS item_b,
COUNT(DISTINCT a.user_id) AS shared_users,
COUNT(*) FILTER (WHERE a.event_type = 'purchase' AND b.event_type = 'purchase') AS co_purchases,
COUNT(*) FILTER (WHERE a.event_type = 'view' AND b.event_type = 'view'
AND a.session_id = b.session_id) AS session_co_views
FROM user_interactions a
JOIN user_interactions b
ON a.user_id = b.user_id
AND a.item_id < b.item_id
AND a.event_time > NOW() - INTERVAL '7 DAYS'
AND b.event_time > NOW() - INTERVAL '7 DAYS'
GROUP BY a.item_id, b.item_id
HAVING COUNT(DISTINCT a.user_id) >= 3;
Per-item intent rates round out the product signals: the add-to-cart rate and cart-to-purchase rate per item are strong scoring inputs and flag items with a pricing or description problem.
CREATE MATERIALIZED VIEW product_intent_signals AS
SELECT
item_id,
COUNT(*) FILTER (WHERE event_type = 'view') AS view_count,
COUNT(*) FILTER (WHERE event_type = 'add_to_cart') AS cart_add_count,
COUNT(*) FILTER (WHERE event_type = 'purchase') AS purchase_count,
COUNT(*) FILTER (WHERE event_type = 'add_to_cart')::NUMERIC
/ NULLIF(COUNT(*) FILTER (WHERE event_type = 'view'), 0) AS cart_add_rate,
COUNT(*) FILTER (WHERE event_type = 'purchase')::NUMERIC
/ NULLIF(COUNT(*) FILTER (WHERE event_type = 'add_to_cart'), 0) AS purchase_conversion_rate
FROM user_interactions
GROUP BY item_id;
For "users like you" neighborhoods, user_category_affinity already gives each user a category distribution. The serving layer compares distributions, and a user who develops a new interest is matched with that neighborhood within seconds rather than after a nightly rebuild.
Content Recommendations for Publishers
Publishers and media apps use the same machinery with content metadata instead of a product catalog and dwell time as the dominant signal. A two-hour sliding window of interest per category, refreshed every five minutes, means a user who starts reading technology articles at 2 PM has a rising technology score by 2:05:
CREATE SOURCE content_events (
event_id VARCHAR,
user_id VARCHAR,
content_id VARCHAR,
event_type VARCHAR, -- 'view', 'scroll', 'share', 'like', 'comment', 'click'
dwell_seconds INT,
event_time TIMESTAMPTZ
) WITH (
connector = 'kafka',
topic = 'content.engagement',
properties.bootstrap.server = 'kafka:9092',
scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;
CREATE TABLE content_metadata (
content_id VARCHAR PRIMARY KEY,
title VARCHAR,
category VARCHAR,
tags VARCHAR,
published_at TIMESTAMPTZ
);
CREATE MATERIALIZED VIEW user_interest_profile AS
SELECT
e.user_id,
m.category,
window_start,
window_end,
SUM(CASE e.event_type WHEN 'view' THEN 1 WHEN 'scroll' THEN 2 WHEN 'like' THEN 5
WHEN 'share' THEN 8 WHEN 'comment' THEN 10 ELSE 1 END
+ LEAST(e.dwell_seconds / 30, 5)) AS interest_score,
COUNT(*) AS interaction_count
FROM HOP(content_events, event_time, INTERVAL '5 MINUTES', INTERVAL '2 HOURS') e
JOIN content_metadata m ON e.content_id = m.content_id
GROUP BY e.user_id, m.category, window_start, window_end;
CREATE MATERIALIZED VIEW trending_content AS
SELECT
content_id,
window_start,
window_end,
COUNT(*) FILTER (WHERE event_type IN ('view', 'click')) AS views,
COUNT(*) FILTER (WHERE event_type = 'share') AS shares,
AVG(dwell_seconds) AS avg_dwell,
COUNT(*) + COUNT(*) FILTER (WHERE event_type = 'share') * 3.0
+ COUNT(*) FILTER (WHERE event_type = 'like') * 2.0 AS trending_score
FROM TUMBLE(content_events, event_time, INTERVAL '30 MINUTES')
GROUP BY content_id, window_start, window_end;
The recommendation API joins the user's interest profile with trending content in the same category to surface articles that are both personally relevant and currently popular. A session window with a 15-minute gap adds primary_session_topic, so a user clearly in a finance reading session gets finance-adjacent suggestions even when their long-term profile skews elsewhere.
User Behavior Features and Engagement Scores
Churn and personalization models need per-session and per-user behavior features: depth, click velocity, scroll, duration, funnel progression. The session summary is the building block:
CREATE MATERIALIZED VIEW session_features AS
SELECT
session_id,
user_id,
MIN(event_time) AS session_start,
MAX(event_time) AS session_end,
EXTRACT(EPOCH FROM MAX(event_time) - MIN(event_time)) AS session_duration_sec,
COUNT(*) FILTER (WHERE event_type = 'view') AS page_views,
COUNT(*) FILTER (WHERE event_type = 'click') AS clicks,
COUNT(DISTINCT item_id) AS distinct_items,
COUNT(DISTINCT category) AS distinct_categories,
AVG(scroll_depth_pct) FILTER (WHERE scroll_depth_pct > 0) AS avg_scroll_depth,
BOOL_OR(event_type = 'add_to_cart') AS added_to_cart,
BOOL_OR(event_type = 'purchase') AS converted
FROM user_interactions
GROUP BY session_id, user_id;
A weighted engagement score collapses those signals into a 0 to 100 number with a tier label that downstream systems can act on without knowing the thresholds:
CREATE MATERIALIZED VIEW engagement_scores AS
SELECT
*,
CASE WHEN engagement_score >= 70 THEN 'high'
WHEN engagement_score >= 35 THEN 'medium'
ELSE 'low' END AS engagement_tier
FROM (
SELECT
session_id,
user_id,
session_duration_sec,
page_views,
clicks,
LEAST(100, ROUND(
LEAST(30, distinct_items * 5.0) -- depth, max 30
+ LEAST(25, clicks * 3.0) -- click activity, max 25
+ COALESCE(avg_scroll_depth, 0) * 0.2 -- scroll, max 20
+ LEAST(15, session_duration_sec / 40.0) -- duration, max 15
+ CASE WHEN added_to_cart THEN 10 ELSE 0 END)) -- cart intent
AS engagement_score
FROM session_features
) s;
Rolling per-user features layer on top, and because they read from session_features rather than the raw stream they add little cost:
CREATE MATERIALIZED VIEW user_rolling_features AS
SELECT
user_id,
COUNT(DISTINCT session_id) AS total_sessions,
AVG(session_duration_sec) AS avg_session_duration,
AVG(page_views) AS avg_pages_per_session,
SUM(CASE WHEN converted THEN 1 ELSE 0 END)::NUMERIC / NULLIF(COUNT(*), 0) AS historical_conversion_rate,
MAX(session_start) AS last_seen_at
FROM session_features
GROUP BY user_id;
CREATE MATERIALIZED VIEW user_funnel_features AS
SELECT
user_id,
COUNT(DISTINCT session_id) FILTER (WHERE event_type = 'view') AS sessions_with_views,
COUNT(DISTINCT session_id) FILTER (WHERE event_type = 'add_to_cart') AS sessions_with_cart,
COUNT(DISTINCT session_id) FILTER (WHERE event_type = 'purchase') AS sessions_with_purchase,
ROUND(COUNT(DISTINCT session_id) FILTER (WHERE event_type = 'purchase')::NUMERIC
/ NULLIF(COUNT(DISTINCT session_id) FILTER (WHERE event_type = 'add_to_cart'), 0), 4) AS cart_to_purchase_rate
FROM user_interactions
GROUP BY user_id;
A user with 45 sessions and zero purchases is a retargeting candidate; a user whose engagement tier has slid from high to low across their last five sessions is a churn risk. Both are a WHERE clause over these views.
Serving Features at Inference Time
Production ranking has four steps: offline training on point-in-time-correct features, candidate generation from approximate nearest neighbor search over collaborative filtering embeddings, feature assembly per candidate, and scoring. The streaming views feed step three. Because RisingWave speaks the PostgreSQL wire protocol, the ranking service reads them with any PostgreSQL client and no feature store SDK:
SELECT si.recent_categories, si.recent_items,
es.engagement_score, es.engagement_tier,
ur.historical_conversion_rate
FROM user_session_intent si
JOIN engagement_scores es USING (session_id)
JOIN user_rolling_features ur ON ur.user_id = si.user_id
WHERE si.user_id = $1 AND si.session_id = $2;
The query returns in single-digit milliseconds because nothing is aggregated at query time. Per-candidate features such as item_popularity_1h and product_intent_signals are fetched by item ID in the same way, and items_to_suppress is anti-joined against the candidate list.
For services that already read from a key-value store, sink the serving view there. A streaming video platform is a good example: the recommendation API has a few hundred milliseconds to assemble a viewer profile, and DynamoDB gives single-digit millisecond GetItem reads. RisingWave does the stateful computation; DynamoDB does the serving.
CREATE SOURCE view_events (
viewer_id BIGINT,
content_id VARCHAR,
genre VARCHAR,
watch_duration_sec INT,
completion_pct DECIMAL,
watched_at TIMESTAMP
) WITH (
connector = 'kafka',
topic = 'view_events',
properties.bootstrap.server = 'broker:9092',
scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;
-- Tier 1: daily tiles, stable once the day closes
CREATE MATERIALIZED VIEW viewer_watch_metrics_1d AS
SELECT
viewer_id,
DATE_TRUNC('day', watched_at) AS watch_date,
SUM(watch_duration_sec) / 60.0 AS watch_time_minutes,
COUNT(DISTINCT content_id) AS content_started,
COUNT(*) FILTER (WHERE completion_pct >= 0.9) AS completions,
COUNT(DISTINCT genre) AS genres_watched
FROM view_events
GROUP BY viewer_id, DATE_TRUNC('day', watched_at);
-- Tier 2: rolling profile, the serving contract
CREATE MATERIALIZED VIEW viewer_engagement_rolling AS
SELECT
viewer_id,
SUM(watch_time_minutes) FILTER (WHERE watch_date >= CURRENT_DATE - INTERVAL '7 DAYS') AS watch_minutes_7d,
SUM(watch_time_minutes) FILTER (WHERE watch_date >= CURRENT_DATE - INTERVAL '28 DAYS') AS watch_minutes_28d,
SUM(content_started) FILTER (WHERE watch_date >= CURRENT_DATE - INTERVAL '7 DAYS') AS titles_7d,
SUM(completions) FILTER (WHERE watch_date >= CURRENT_DATE - INTERVAL '28 DAYS') AS completions_28d
FROM viewer_watch_metrics_1d
GROUP BY viewer_id;
CREATE SINK viewer_engagement_sink FROM viewer_engagement_rolling
WITH (connector = 'dynamodb', table = 'viewer_profiles', region = 'us-east-1', primary_key = 'viewer_id');
Two rules make the DynamoDB pattern work. Sink from the rolling summary, not from the daily tile; the tile is an internal layer that makes rolling windows cheap, and the summary is the one-row-per-viewer contract the API reads. And model the table as one wide item per engagement domain (engagement#watch, engagement#interactions), which keeps items bounded under DynamoDB's 400 KB limit, lets one GetItem return a full domain, and maps domain ownership to the team that produces it. One item per metric forces dozens of reads per request, and one universal row grows without bound and invites cross-team overwrites.
The same views can be sunk to Kafka, PostgreSQL, or Redis for other serving stacks:
CREATE SINK popularity_to_kafka FROM item_popularity_1h
WITH (connector = 'kafka', topic = 'recommendation.item-popularity', properties.bootstrap.server = 'kafka:9092', primary_key = 'item_id')
FORMAT UPSERT ENCODE JSON;
Freshness SLAs by Surface
Not every surface needs the same freshness, and applying one SLA everywhere wastes effort:
| Surface | Acceptable staleness | Why |
|---|---|---|
| Search results | Under 1 second | The query is an explicit intent signal; features must reflect the current session |
| Cart recommendations | 10 to 30 seconds | Cart contents are the strongest signal |
| Related items on a product page | 30 to 60 seconds | Comparison mode; recency matters but not sub-second |
| Category browse | 1 to 2 minutes | The user is exploring; recent click sequence matters |
| Home feed on a return visit | 5 to 15 minutes | Intent has shifted since the last session; session features matter less |
| Email and push | Hours | The user is not active; historical features dominate |
Streaming materialized views handle this naturally. Freshness is set by event latency and processing lag, not by a refresh schedule, so a trending score over a one-hour window and a sub-second session feature come from the same pipeline.
Measuring Whether Real-Time Features Help
Adding real-time features is not automatically beneficial. Offline, compare normalized discounted cumulative gain with and without them using point-in-time-correct feature snapshots, meaning the feature values as they existed when each training example occurred. Break the result down by early-session queries (one or two interactions) and late-session queries (five or more); real-time features usually show larger lift late in the session where there is more context to exploit.
Online, measure click-through rate, conversion rate, and session depth, and split the A/B test on session ID rather than user ID to avoid contaminating a user's sessions with mixed treatment. Expect a possible drop in session length: if the features work, users find relevant items faster.
Batch Feature Pipelines vs Streaming SQL
| Dimension | Nightly batch to feature store | Kafka plus Flink plus feature store | Streaming SQL (RisingWave) |
|---|---|---|---|
| Session features | Not available | Custom job per feature | One materialized view |
| Trending and conversion rate | Yesterday's | Fresh, custom code | Fresh, SQL temporal filters |
| Suppression of just-bought items | Next day | Custom state | DISTINCT view, instant |
| Co-occurrence | Nightly rebuild | Heavy custom join | Incremental self-join |
| Serving | Key-value store | Key-value store | PostgreSQL protocol, or sinks to DynamoDB, Redis, Kafka |
| Orchestration | Airflow DAGs | Job scheduler plus Flink ops | None |
Frequently Asked Questions
Can RisingWave replace the feature store entirely? For online features, yes: the views are queryable over PostgreSQL and can be sunk to whatever store the serving stack already uses. For offline training you still want point-in-time-correct snapshots, which you can produce by sinking view changes to Iceberg or by replaying Kafka through the same views.
Why not compute exponential decay in the view?
Streaming views cannot evaluate NOW() in the SELECT list because the result would change without any input event. Expose several windows as separate columns instead, and let the model or the serving layer weight them.
How large can the co-occurrence join get?
It grows with the square of items per user inside the window. Keep the temporal filter tight, raise the HAVING threshold, and restrict the join to purchases or to same-session views if the full interaction stream is too wide.
How do I avoid recommending out-of-stock items?
Stream inventory through CDC into a table and anti-join candidates against items with zero sellable stock, the same way items_to_suppress is used. Low but non-zero stock can be exposed as a scarcity feature instead.
Does the DynamoDB pattern apply to Redis? Yes. The serving contract rule and the per-domain modeling rule are the same. RisingWave sinks the rolling summary, the serving layer reads one key per domain, and the daily tiles stay internal.
