> For the complete documentation index, see [llms.txt](https://nightly.gitbook.io/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://nightly.gitbook.io/docs/database/clickhouse-problems/systeme-de-recommandation.md).

# Recommendation system

This tutorial shows you how to build a user behavior-based recommendation system, 100% in SQL in ClickHouse, with Big Data support via \`MATERIALIZED VIEW'. (<https://clickhouse.com/docs/materialized-view/incremental-materialized-view>)

### 📌 Objective

For each content viewed (‘content\_uuid’), the most frequently viewed content is recommended **next to** by other users in a **sliding window** (for example, the following 10 views).

### 📦 Step 1 – Create the source table

```sql
DROP TABLE IF EXISTS views;

CREATE TABLE views (
    id UInt64,
    user_uuid String,
    content_uuid String
) ENGINE = MergeTree()
ORDER BY (user_uuid, id);
```

This table stores user journeys, with each row representing a view.

* **id**: chronological order (auto-increment)
* **user\_uuid**: user ID
* **content\_uuid**: accessed content

### 🧪 Step 2 - Test data insertion

```sql
INSERT INTO views (id, user_uuid, content_uuid) VALUES
    (1, 'user1', 'A'),
    (2, 'user1', 'B'),
    (3, 'user1', 'C'),
    (4, 'user1', 'D'),
    (5, 'user1', 'E'),
    (6, 'user2', 'A'),
    (7, 'user2', 'C'),
    (8, 'user2', 'E'),
    (9, 'user2', 'F'),
    (10, 'user2', 'G'),
    (11, 'user3', 'B'),
    (12, 'user3', 'C'),
    (13, 'user3', 'D'),
    (14, 'user3', 'A'),
    (15, 'user3', 'E');
```

### ⚙️ Step 3 - Create MATERIALIZED VIEW

```sql
DROP TABLE IF EXISTS transitions_mv;

CREATE MATERIALIZED VIEW transitions_mv
ENGINE = SummingMergeTree()
ORDER BY (from_content, to_content)
POPULATE
AS
SELECT
    a.content_uuid AS from_content,
    b.content_uuid AS to_content,
    count() AS transition_count
FROM views AS a
JOIN views AS b
    ON a.user_uuid = b.user_uuid
   AND b.id > a.id
   AND b.id <= a.id + 10  -- 10 c'est une valeur brute, stv 20 faut update ça
GROUP BY from_content, to_content;
```

✅ The keyword *POPULATE* is used to fill the view with existing data.

* **from\_content**: initial content
* **to\_content**: content then seen in the window
* **transition\_count**: number of times this transition was observed

### 🔍 Step 4 - Query the view

Query to retrieve the top 20 contents to recommend after viewing 'A'

```sql
SELECT
    to_content,
    transition_count
FROM transitions_mv
WHERE from_content = 'A'
ORDER BY transition_count DESC
LIMIT 20;
```

#### Probability calculation (bonus)

If you want a normalized score (probability that **to\_content** follows **from\_content**):

```sql
SELECT
    from_content,
    to_content,
    transition_count,
    round(transition_count / sum(transition_count) OVER (PARTITION BY from_content), 3) AS probability
FROM transitions_mv
WHERE from_content = 'A'
ORDER BY probability DESC
LIMIT 20;
```