Personalized Product Recommendations in Real Time Using SQL

Personalized Product Recommendations in Real Time Using SQL

Every e-commerce team wants to serve the right product to the right user at exactly the right moment. The catch: most recommendation systems run on batch pipelines that recompute scores overnight. By the time a recommendation reflects what a user just bought, twelve hours have passed and the moment is gone.

Streaming SQL changes this. With materialized views that update incrementally as events arrive, you can build a personalized product recommendations system where scores react to behavior within seconds, not hours. In this tutorial, you will use RisingWave to implement a collaborative filtering approach: track what users view, wish-list, and purchase; find items that appear together across users; and surface those items as personalized recommendations, all in standard SQL.

How Collaborative Filtering Works

Collaborative filtering is the recommendation technique behind "customers who bought X also bought Y." It relies on behavioral signals rather than product attributes. Two users who purchase the same items are implicitly similar, and the items one user engaged with are good candidates to recommend to the other.

The core data structures are:

  • User-item interaction matrix: A score for every (user, item) pair based on observed behavior. Purchases carry more weight than views.
  • Item co-occurrence matrix: How often two items appear in the same user's interaction history. High co-occurrence means the items attract a similar audience.
  • Recommendation query: For a given user, find items that co-occur frequently with items the user has already engaged with, excluding items the user has already seen.

In a batch system, you recompute the co-occurrence matrix on a schedule. In a streaming system, the matrix updates continuously as new events arrive.

Architecture Overview

graph LR
    A[Web / Mobile App] -->|Behavioral events| B[Kafka Topic]
    B -->|Ingestion| C[RisingWave]
    C -->|reco_mv_user_profiles| D[User interest scores]
    C -->|reco_mv_item_coviews| E[Item co-occurrence matrix]
    C -->|reco_mv_similar_items| F[Bidirectional item similarity]
    C -->|reco_mv_top_picks| G[Personalized top picks per user]
    G -->|PostgreSQL wire protocol| H[Recommendation API]
    G -->|Sink| I[Redis / Feature Store]

In production, behavioral events flow from your application through Apache Kafka into RisingWave. RisingWave maintains a chain of materialized views: user profiles feed item co-occurrence computation, which feeds the similarity index, which feeds personalized recommendations. Every insert into the events table triggers incremental updates across the entire chain.

For this tutorial, you will use a plain table as the event source so you can follow along without Kafka. The materialized view SQL is identical in both cases.

Setting Up the Data Model

All SQL in this article has been verified against RisingWave 2.8.0. You can connect to a local RisingWave instance with any PostgreSQL-compatible client.

Step 1: Create the Tables

Start with three tables: users, products, and behavioral events.

CREATE TABLE reco_users (
    user_id     VARCHAR,
    username    VARCHAR,
    age_group   VARCHAR,
    country     VARCHAR,
    signup_ts   TIMESTAMPTZ
);

CREATE TABLE reco_products (
    product_id   VARCHAR,
    name         VARCHAR,
    category     VARCHAR,
    subcategory  VARCHAR,
    price        DOUBLE PRECISION,
    avg_rating   DOUBLE PRECISION
);

CREATE TABLE reco_events (
    event_id    VARCHAR,
    user_id     VARCHAR,
    product_id  VARCHAR,
    event_type  VARCHAR,   -- 'view', 'wishlist', 'purchase'
    event_ts    TIMESTAMPTZ
);

The reco_events table is the source of truth. Every user action, whether a page view, a wishlist add, or a purchase, lands here. In production, this would be a RisingWave source connected to a Kafka topic.

Step 2: Insert Sample Data

Six users and ten products in a home office category. Users have distinct behavior patterns: some are electronics enthusiasts, some focus on furniture, and some are accessories buyers.

INSERT INTO reco_users VALUES
('u1', 'alice', '25-34', 'US', '2025-01-10 09:00:00+00'),
('u2', 'bob',   '35-44', 'UK', '2025-02-14 11:30:00+00'),
('u3', 'carol', '18-24', 'CA', '2025-03-01 08:15:00+00'),
('u4', 'dave',  '25-34', 'AU', '2025-03-20 14:00:00+00'),
('u5', 'eve',   '45-54', 'US', '2025-04-05 16:45:00+00'),
('u6', 'frank', '25-34', 'DE', '2025-04-15 10:00:00+00');

INSERT INTO reco_products VALUES
('p101', 'Wireless Noise-Cancelling Headphones', 'Electronics', 'Audio',       149.99, 4.7),
('p102', 'Mechanical Keyboard',                 'Electronics', 'Peripherals',   89.99, 4.5),
('p103', 'Ergonomic Office Chair',              'Furniture',   'Seating',       299.00, 4.6),
('p104', 'Standing Desk Converter',             'Furniture',   'Desks',         189.00, 4.3),
('p105', 'USB-C Docking Station',               'Electronics', 'Peripherals',   129.99, 4.4),
('p106', 'Blue Light Blocking Glasses',         'Accessories', 'Eyewear',        29.99, 4.2),
('p107', 'Laptop Stand',                        'Accessories', 'Mounts',         45.00, 4.5),
('p108', 'Wireless Mouse',                      'Electronics', 'Peripherals',    39.99, 4.6),
('p109', 'Webcam 4K',                           'Electronics', 'Video',          99.99, 4.3),
('p110', 'Desk Lamp with USB Charging',         'Accessories', 'Lighting',       34.99, 4.4);

Next, insert forty behavioral events that reflect realistic shopping patterns. Alice and dave have overlapping electronics interests; bob and eve share furniture preferences; carol focuses on accessories.

INSERT INTO reco_events VALUES
-- alice: buys keyboard, mouse; views docking station and laptop stand
('ev001','u1','p101','view',     '2026-03-01 10:00:00+00'),
('ev002','u1','p101','wishlist', '2026-03-01 10:02:00+00'),
('ev003','u1','p102','view',     '2026-03-01 10:05:00+00'),
('ev004','u1','p102','purchase', '2026-03-01 10:08:00+00'),
('ev005','u1','p108','view',     '2026-03-02 09:00:00+00'),
('ev006','u1','p108','purchase', '2026-03-02 09:10:00+00'),
('ev007','u1','p105','view',     '2026-03-03 14:00:00+00'),
('ev008','u1','p107','view',     '2026-03-04 11:00:00+00'),
-- bob: buys chair and keyboard; views mouse and standing desk
('ev009','u2','p103','view',     '2026-03-01 11:00:00+00'),
('ev010','u2','p103','purchase', '2026-03-01 11:30:00+00'),
('ev011','u2','p104','view',     '2026-03-02 10:00:00+00'),
('ev012','u2','p104','wishlist', '2026-03-02 10:05:00+00'),
('ev013','u2','p102','view',     '2026-03-03 12:00:00+00'),
('ev014','u2','p102','purchase', '2026-03-03 12:15:00+00'),
('ev015','u2','p108','view',     '2026-03-04 09:00:00+00'),
-- carol: buys glasses, laptop stand, desk lamp
('ev016','u3','p106','view',     '2026-03-01 15:00:00+00'),
('ev017','u3','p106','purchase', '2026-03-01 15:10:00+00'),
('ev018','u3','p107','view',     '2026-03-02 16:00:00+00'),
('ev019','u3','p107','purchase', '2026-03-02 16:20:00+00'),
('ev020','u3','p110','view',     '2026-03-03 17:00:00+00'),
('ev021','u3','p110','purchase', '2026-03-03 17:15:00+00'),
('ev022','u3','p108','view',     '2026-03-04 14:00:00+00'),
-- dave: buys headphones, docking station, webcam (overlaps with alice on electronics)
('ev023','u4','p101','view',     '2026-03-01 08:00:00+00'),
('ev024','u4','p101','purchase', '2026-03-01 08:30:00+00'),
('ev025','u4','p102','view',     '2026-03-02 09:00:00+00'),
('ev026','u4','p105','view',     '2026-03-03 10:00:00+00'),
('ev027','u4','p105','purchase', '2026-03-03 10:20:00+00'),
('ev028','u4','p109','view',     '2026-03-04 11:00:00+00'),
('ev029','u4','p109','purchase', '2026-03-04 11:30:00+00'),
-- eve: buys chair and standing desk (overlaps with bob on furniture)
('ev030','u5','p103','view',     '2026-03-01 13:00:00+00'),
('ev031','u5','p103','purchase', '2026-03-01 13:40:00+00'),
('ev032','u5','p104','view',     '2026-03-02 14:00:00+00'),
('ev033','u5','p104','purchase', '2026-03-02 14:30:00+00'),
('ev034','u5','p106','view',     '2026-03-03 15:00:00+00'),
('ev035','u5','p110','view',     '2026-03-04 16:00:00+00'),
-- frank: mostly browsing, one purchase
('ev036','u6','p101','view',     '2026-03-01 09:00:00+00'),
('ev037','u6','p102','view',     '2026-03-01 09:30:00+00'),
('ev038','u6','p103','view',     '2026-03-02 10:00:00+00'),
('ev039','u6','p108','view',     '2026-03-03 11:00:00+00'),
('ev040','u6','p107','purchase', '2026-03-04 12:00:00+00');

Building the Recommendation Engine with Materialized Views

With the event data in place, you now build a chain of four materialized views. Each view feeds the next. RisingWave maintains all of them incrementally: when a new event arrives, only the affected rows are recomputed.

Materialized View 1: User Interest Profiles

The first view computes a weighted interest score for every (user, product) pair. Purchases contribute 5 points, wishlist adds contribute 3 points, and views contribute 1 point. These weights are tunable, but this ratio reflects the standard assumption that purchase intent is the strongest signal.

CREATE MATERIALIZED VIEW reco_mv_user_profiles AS
SELECT
    user_id,
    product_id,
    SUM(
        CASE event_type
            WHEN 'purchase' THEN 5
            WHEN 'wishlist' THEN 3
            WHEN 'view'     THEN 1
            ELSE 0
        END
    ) AS interest_score,
    COUNT(*) AS interaction_count,
    MAX(event_ts) AS last_interacted_at
FROM reco_events
GROUP BY user_id, product_id;

Query the view to see alice's profile:

SELECT * FROM reco_mv_user_profiles
WHERE user_id = 'u1'
ORDER BY interest_score DESC;
 user_id | product_id | interest_score | interaction_count |    last_interacted_at
---------+------------+----------------+-------------------+---------------------------
 u1      | p108       |              6 |                 2 | 2026-03-02 09:10:00+00:00
 u1      | p102       |              6 |                 2 | 2026-03-01 10:08:00+00:00
 u1      | p101       |              4 |                 2 | 2026-03-01 10:02:00+00:00
 u1      | p107       |              1 |                 1 | 2026-03-04 11:00:00+00:00
 u1      | p105       |              1 |                 1 | 2026-03-03 14:00:00+00:00

Alice has strong signals on the wireless mouse (purchased) and mechanical keyboard (purchased), a wishlist signal on headphones, and weak view signals on the docking station and laptop stand. This is the profile the recommendation engine will use to find similar items.

Materialized View 2: Item Co-Occurrence Matrix

The second view computes how often two items appear together in any user's interaction history. Items bought by the same set of users are strong candidates for recommendations.

The join condition a.product_id < b.product_id ensures each pair is counted exactly once. The HAVING clause filters out pairs seen by only one user, which reduces noise.

CREATE MATERIALIZED VIEW reco_mv_item_coviews AS
SELECT
    a.product_id AS item_a,
    b.product_id AS item_b,
    COUNT(DISTINCT a.user_id) AS shared_users,
    SUM(
        CASE
            WHEN a.event_type = 'purchase' AND b.event_type = 'purchase' THEN 4
            WHEN a.event_type = 'purchase' OR  b.event_type = 'purchase' THEN 2
            ELSE 1
        END
    ) AS coview_weight
FROM reco_events a
JOIN reco_events b
    ON  a.user_id    = b.user_id
    AND a.product_id < b.product_id
GROUP BY a.product_id, b.product_id
HAVING COUNT(DISTINCT a.user_id) >= 2;
SELECT * FROM reco_mv_item_coviews
ORDER BY shared_users DESC, coview_weight DESC
LIMIT 8;
 item_a | item_b | shared_users | coview_weight
--------+--------+--------------+---------------
 p102   | p108   |            3 |            13
 p101   | p102   |            3 |            10
 p107   | p108   |            3 |             8
 p103   | p104   |            2 |            15
 p101   | p105   |            2 |            11
 p106   | p110   |            2 |            10
 p102   | p103   |            2 |            10
 p101   | p108   |            2 |             7

The strongest pair is mechanical keyboard + wireless mouse (3 shared users, weight 13). The chair + standing desk pair has fewer shared users but a higher raw weight because both were purchased, not just viewed. This makes intuitive sense for home office shoppers.

Materialized View 3: Bidirectional Item Similarity Index

The co-occurrence table stores each pair once with item_a < item_b. For the recommendation query, you need to look up all similar items for a given product, regardless of which side of the pair it sits on. This view makes the relationship bidirectional.

CREATE MATERIALIZED VIEW reco_mv_similar_items AS
SELECT item_a AS product_id, item_b AS similar_product_id, shared_users, coview_weight
FROM reco_mv_item_coviews
UNION ALL
SELECT item_b AS product_id, item_a AS similar_product_id, shared_users, coview_weight
FROM reco_mv_item_coviews;
SELECT * FROM reco_mv_similar_items
WHERE product_id = 'p102'
ORDER BY coview_weight DESC;
 product_id | similar_product_id | shared_users | coview_weight
------------+--------------------+--------------+---------------
 p102       | p108               |            3 |            13
 p102       | p101               |            3 |            10
 p102       | p103               |            2 |            10
 p102       | p105               |            2 |             6
 p102       | p107               |            2 |             5

The mechanical keyboard is most strongly associated with the wireless mouse, then headphones, then the ergonomic chair. These associations capture real shopping behavior: engineers setting up a desk tend to buy both keyboard and mouse, and they look at monitors, chairs, and audio gear at the same time.

Materialized View 4: Personalized Top Picks

The final view brings everything together. For each user, it joins their interest profile against the item similarity index to find candidate recommendations, then filters out items the user has already interacted with.

CREATE MATERIALIZED VIEW reco_mv_top_picks AS
SELECT
    up.user_id,
    si.similar_product_id                AS recommended_product_id,
    p.name                               AS product_name,
    p.category,
    SUM(up.interest_score * si.coview_weight) AS reco_score,
    COUNT(DISTINCT up.product_id)        AS sourced_from_n_items,
    MAX(up.last_interacted_at)           AS based_on_activity_at
FROM reco_mv_user_profiles up
JOIN reco_mv_similar_items si
    ON up.product_id = si.product_id
JOIN reco_products p
    ON p.product_id  = si.similar_product_id
WHERE NOT EXISTS (
    SELECT 1 FROM reco_mv_user_profiles already
    WHERE already.user_id    = up.user_id
      AND already.product_id = si.similar_product_id
)
GROUP BY up.user_id, si.similar_product_id, p.name, p.category;

The reco_score formula is SUM(interest_score * coview_weight). This product rewards recommendations that are both strongly aligned with items the user engaged with AND co-occur with many other users' behavior. The sourced_from_n_items column reveals how many of the user's interest signals contributed to the recommendation - a high number means the recommendation is robust.

Query the top picks across all users:

SELECT user_id, recommended_product_id, product_name, category, reco_score
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY reco_score DESC) AS rank
    FROM reco_mv_top_picks
) ranked
WHERE rank <= 3
ORDER BY user_id, rank;
 user_id | recommended_product_id |             product_name             |  category   | reco_score
---------+------------------------+--------------------------------------+-------------+------------
 u1      | p103                   | Ergonomic Office Chair               | Furniture   |         84
 u2      | p105                   | USB-C Docking Station                | Electronics |         72
 u2      | p101                   | Wireless Noise-Cancelling Headphones | Electronics |         67
 u2      | p107                   | Laptop Stand                         | Accessories |         38
 u3      | p102                   | Mechanical Keyboard                  | Electronics |         43
 u3      | p101                   | Wireless Noise-Cancelling Headphones | Electronics |         31
 u3      | p103                   | Ergonomic Office Chair               | Furniture   |          4
 u4      | p108                   | Wireless Mouse                       | Electronics |         55
 u4      | p107                   | Laptop Stand                         | Accessories |         29
 u4      | p103                   | Ergonomic Office Chair               | Furniture   |         10
 u5      | p102                   | Mechanical Keyboard                  | Electronics |         60
 u5      | p108                   | Wireless Mouse                       | Electronics |         24
 u6      | p105                   | USB-C Docking Station                | Electronics |         27
 u6      | p104                   | Standing Desk Converter              | Furniture   |         15

The results make sense. Alice (u1) gets the ergonomic chair because other users who bought her keyboard and mouse also bought that chair. Carol (u3) gets keyboard and headphones recommendations because those co-occur with her accessories purchases. Eve (u5), who bought furniture, gets keyboard and mouse recommendations based on bob's overlap behavior.

Real-Time Updates: What Happens When a New Event Arrives

This is the key advantage of a streaming database. When alice purchases the USB-C docking station, the entire recommendation chain updates automatically without any recomputation job.

Before the new event, alice's profile shows:

SELECT product_id, interest_score
FROM reco_mv_user_profiles
WHERE user_id = 'u1'
ORDER BY interest_score DESC;
 product_id | interest_score
------------+----------------
 p108       |              6
 p102       |              6
 p101       |              4
 p107       |              1
 p105       |              1

Now insert the new event:

INSERT INTO reco_events VALUES
('ev041', 'u1', 'p105', 'purchase', '2026-04-01 10:00:00+00');

Query the profile again immediately after the insert:

SELECT product_id, interest_score, last_interacted_at
FROM reco_mv_user_profiles
WHERE user_id = 'u1'
ORDER BY interest_score DESC;
 product_id | interest_score |    last_interacted_at
------------+----------------+---------------------------
 p108       |              6 | 2026-03-02 09:10:00+00:00
 p105       |              6 | 2026-04-01 10:00:00+00:00
 p102       |              6 | 2026-03-01 10:08:00+00:00
 p101       |              4 | 2026-03-01 10:02:00+00:00
 p107       |              1 | 2026-03-04 11:00:00+00:00

The docking station (p105) jumps from score 1 (a single view) to score 6 (view + purchase = 1 + 5). It is also removed from alice's recommendation candidates because she has now interacted with it. The reco_mv_top_picks view reflects this change automatically, without any scheduled job.

This is incremental materialized view maintenance: RisingWave identifies which rows in each downstream view are affected by the new event and recomputes only those rows. The cost of processing a new event is proportional to the change, not to the total data size.

Serving Recommendations to Your Application

The reco_mv_top_picks view is queryable via the PostgreSQL wire protocol, so any PostgreSQL-compatible client can fetch recommendations directly.

A minimal recommendation API endpoint looks like this:

import psycopg2

def get_recommendations(user_id: str, limit: int = 5) -> list[dict]:
    conn = psycopg2.connect(
        host="localhost", port=4566,
        user="root", dbname="dev"
    )
    cur = conn.cursor()
    cur.execute("""
        SELECT recommended_product_id, product_name, category, reco_score
        FROM reco_mv_top_picks
        WHERE user_id = %s
        ORDER BY reco_score DESC
        LIMIT %s;
    """, (user_id, limit))
    rows = cur.fetchall()
    return [
        {"product_id": r[0], "name": r[1], "category": r[2], "score": r[3]}
        for r in rows
    ]

Because the materialized view is precomputed, this query returns in milliseconds regardless of the number of events in reco_events. The heavy lifting happened at ingest time, not at query time.

For even lower latency, you can push recommendations to Redis using a RisingWave sink:

CREATE SINK reco_sink_redis
FROM reco_mv_top_picks
WITH (
    connector     = 'redis',
    primary_key   = 'user_id,recommended_product_id',
    redis.url     = 'redis://localhost:6379'
);

This keeps a Redis hash current with the latest recommendation scores. Your API reads from Redis instead of RisingWave, which is useful when you need sub-millisecond serving latency at very high request rates.

Connecting to a Kafka Source in Production

For a production deployment, replace the reco_events table with a Kafka source:

CREATE SOURCE reco_events_kafka (
    event_id    VARCHAR,
    user_id     VARCHAR,
    product_id  VARCHAR,
    event_type  VARCHAR,
    event_ts    TIMESTAMPTZ
) WITH (
    connector                    = 'kafka',
    topic                        = 'user-behavior-events',
    properties.bootstrap.server  = 'kafka-broker:9092',
    scan.startup.mode            = 'latest'
) FORMAT PLAIN ENCODE JSON;

All four materialized views built on top of this source work without modification. RisingWave consumes events from Kafka, maintains checkpoints for fault tolerance, and updates the views continuously. You get exactly-once processing semantics via RisingWave's checkpoint mechanism.

Collaborative filtering requires sufficient history to work well for new users or new products. A trending view fills the gap by surfacing globally popular items with no personalization required.

CREATE MATERIALIZED VIEW reco_mv_trending AS
SELECT
    e.product_id,
    p.name          AS product_name,
    p.category,
    COUNT(*)        AS total_interactions,
    COUNT(*) FILTER (WHERE e.event_type = 'purchase') AS purchases,
    COUNT(*) FILTER (WHERE e.event_type = 'wishlist') AS wishlists,
    COUNT(DISTINCT e.user_id) AS unique_users
FROM reco_events e
JOIN reco_products p ON p.product_id = e.product_id
GROUP BY e.product_id, p.name, p.category;
SELECT * FROM reco_mv_trending
ORDER BY total_interactions DESC
LIMIT 5;
 product_id |             product_name             |  category   | total_interactions | purchases | wishlists | unique_users
------------+--------------------------------------+-------------+--------------------+-----------+-----------+--------------
 p102       | Mechanical Keyboard                  | Electronics |                  6 |         2 |         0 |            4
 p108       | Wireless Mouse                       | Electronics |                  5 |         1 |         0 |            4
 p101       | Wireless Noise-Cancelling Headphones | Electronics |                  5 |         1 |         1 |            3
 p103       | Ergonomic Office Chair               | Furniture   |                  5 |         2 |         0 |            3
 p104       | Standing Desk Converter              | Furniture   |                  4 |         1 |         1 |            2

You can blend trending scores with personalized scores in your application layer: for new users, weight trending heavily; as the user's history grows, shift weight toward the personalized recommendations.

Why a Streaming Database Instead of a Batch Pipeline

Traditional recommendation batch pipelines follow a similar structure: nightly Spark job reads interaction logs, computes co-occurrence matrices, writes scores to a serving database. This works, but it introduces hours of lag and requires operating multiple systems.

AspectBatch Pipeline (Spark + Airflow)Streaming Database (RisingWave)
Recommendation freshnessHours (next batch run)Seconds (incremental update)
Infrastructure componentsS3 + Spark + Airflow + PostgreSQLRisingWave alone
Handling new usersMust wait for next batchProfile builds immediately
Cold-start mitigationRequires separate logicTrending view serves new users
Operational complexityHigh (4+ systems to monitor)Low (one system)
Query languageSQL + Python + DAG definitionsStandard SQL only

The key insight is that RisingWave acts as both the stream processor and the serving layer. You do not need a separate database between your stream processor and your API. The materialized views are live, queryable tables.

For teams already familiar with SQL, the learning curve is minimal. If you know how to write a GROUP BY query, you can build this recommendation engine. Compare this to implementing the same logic in Apache Flink, which requires Java or Python APIs, explicit state management, and a separate serving database.

FAQ

What is collaborative filtering in the context of streaming SQL?

Collaborative filtering is a recommendation technique that identifies similar users or items based on behavioral patterns, without requiring explicit product attributes. In streaming SQL, it means maintaining an item co-occurrence matrix as a materialized view that updates automatically when new behavioral events arrive, so recommendations reflect current user activity rather than yesterday's batch run.

How does RisingWave handle the cold-start problem for new users?

New users have no interaction history, so collaborative filtering cannot generate personalized recommendations yet. The standard approach is to serve globally trending items (from reco_mv_trending) to new users and blend in personalized scores as interaction history accumulates. RisingWave's incremental updates mean the personalized profile starts building from the very first event, and recommendations become available within a few interactions.

Can I add content-based signals like product category or price range?

Yes. You can add a content-based filtering layer by joining the user profile with product metadata. For example, if a user consistently buys Electronics products with prices above $100, you can boost the score of candidate recommendations in that category and price band. The join happens inside the materialized view definition, so the blended score updates incrementally like any other view.

How does this scale to millions of users and products?

RisingWave distributes materialized view computation across multiple nodes. The item co-occurrence matrix is the most expensive computation because it self-joins the events table. For large catalogs, you can control complexity by bounding the join window (only consider pairs within the same session or week), setting a higher minimum shared_users threshold in the HAVING clause, or pre-filtering events to high-weight interactions only. RisingWave Cloud handles horizontal scaling automatically.

Conclusion

Building personalized product recommendations in real time no longer requires a dedicated ML platform. With streaming SQL and materialized views, you can implement collaborative filtering as a chain of SQL views that update continuously as users interact with your catalog:

  • reco_mv_user_profiles tracks weighted interest scores per (user, item) pair
  • reco_mv_item_coviews computes item co-occurrence across users with purchase-weighted signals
  • reco_mv_similar_items makes the co-occurrence index bidirectional for efficient lookup
  • reco_mv_top_picks joins user profiles with item similarity to generate personalized recommendations, filtered to exclude already-seen items

The entire system is queryable via the PostgreSQL wire protocol, so your recommendation API is a single SQL query. New user behavior propagates through the view chain within seconds, without batch jobs or scheduled recomputation.


Ready to build your own recommendation engine? Try RisingWave Cloud free, no credit card required. Sign up here.

Join our Slack community to ask questions and connect with other stream processing developers.

Best-in-Class Event Streaming
for Agents, Apps, and Analytics
GitHubXLinkedInSlackYouTube
Sign up for our to stay updated.