Skip to main content

Feeds

This page covers feed queries for returning ranked lists of recommended items. For query fundamentals, see Query Basics.

A feed query returns a ranked list of recommended items. Feeds can be chronological, trending, or personalized to a specific user. Common applications include homepages, "For You" carousels, and discovery experiences.

Personalized feeds use a rank pipeline that:

  1. Retrieves candidate items
  2. Optionally filters out unwanted items (e.g., already seen)
  3. Scores candidates using a model
  4. Reorders results with diversity and exploration
  5. Returns ranked results

Chronological feeds​

Chronological feeds return items ordered by creation time, showing the most recent items first. This is useful for news feeds, social media timelines, or any feed where recency is important.

Prerequisites​

  1. An engine with item data configured
  2. A timestamp column (e.g., created_at) on items

Query example​

SELECT *
FROM column_order(columns='created_at DESC', limit=100)
LIMIT 20

You can also use the derived chronological rank column if you have an interaction table:

SELECT *
FROM column_order(columns='_derived_chronological_rank ASC', limit=100)
LIMIT 20

Trending feeds return items ordered by popularity metrics, showing items that are currently popular or gaining traction. This is useful for discovery pages or "trending now" sections.

Prerequisites​

  1. An engine with item data configured
  2. Popularity metrics (e.g., views, likes, engagement scores)

Query example​

SELECT *
FROM column_order(columns='views DESC, likes DESC, created_at DESC', limit=100)
LIMIT 20

You can also use the derived popular rank column if you have an interaction table:

SELECT *
FROM column_order(columns='_derived_popular_rank ASC', limit=100)
LIMIT 20

Use a time decay formula to surface items that are both popular and recent, giving more weight to recent engagement:

SELECT *
FROM column_order(columns='_derived_popular_rank ASC', limit=1000)
ORDER BY (item.score - 1) / ((((now_seconds() - item.published_at) / 3600) + 2) ** 1.8)
LIMIT 20

Personalized feed with multiple retrievers​

A comprehensive personalized feed combines multiple retrieval strategies, scoring models, diversity reordering, and exploration. A good baseline includes:

  • A content-based retriever (finds items similar to user's past interactions)
  • A collaborative retriever (finds items liked by similar users)
  • A trending list (ensures fresh, popular content)
  • A lightgbm scoring model (ranks candidates by predicted engagement)

Prerequisites​

  1. An engine with item data configured
  2. A trained collaborative embedding (e.g., ALS)
  3. A trained content embedding (e.g., text embedding)
  4. A trained scoring model (e.g., lightgbm)
  5. A user_id to personalize for

Query example​

SELECT *
FROM similarity(embedding_ref='item_content_embedding',
               encoder='interaction_round_robin',
               input_user_id='$user_id', limit=50),
     similarity(embedding_ref='als_embedding',
               encoder='precomputed_user',
               input_user_id='$user_id', limit=50),
     column_order(columns='_derived_popular_rank ASC', limit=50)
WHERE item_id NOT IN (SELECT item_id FROM interactions 
                      WHERE user_id = '$user_id')
ORDER BY lightgbm
REORDER BY diversity(0.3), exploration(0.2)
LIMIT 20

This query:

  1. Retrieves candidates from three sources:
    • Content-based similarity (items similar to what the user has interacted with)
    • Collaborative similarity (items liked by similar users)
    • Trending items (popular items for freshness)
  2. Filters out items the user has already interacted with
  3. Scores candidates using a lightgbm model to predict engagement
  4. Reorders with diversity (30%) to ensure variety and exploration (20%) to surface new items
  5. Returns the top 20 personalized items

Combining multiple models in personalized feeds​

Use an ensemble of models to balance different signals when ranking feed items:

SELECT *
FROM similarity(embedding_ref='item_content_embedding',
               encoder='interaction_round_robin',
               input_user_id='$user_id', limit=50),
     similarity(embedding_ref='als_embedding',
               encoder='precomputed_user',
               input_user_id='$user_id', limit=50),
     column_order(columns='_derived_popular_rank ASC', limit=50)
WHERE item_id NOT IN (SELECT item_id FROM interactions 
                      WHERE user_id = '$user_id')
ORDER BY 0.6 * lightgbm + 0.4 * bert4rec
REORDER BY diversity(0.3), exploration(0.2)
LIMIT 20

Personalized trendy feed​

Combine popularity, personalization, and time decay to create a feed that surfaces items that are both trending and relevant to the user:

SELECT *
FROM column_order(columns='_derived_popular_rank ASC', limit=1000)
WHERE item_id NOT IN (SELECT item_id FROM interactions 
                      WHERE user_id = '$user_id')
ORDER BY ((item.score / 1000) + 
          cosine_similarity(
            text_encoding(item, embedding_ref='text_embedding'), 
            pooled_text_encoding(user.recent_interactions, 
                                embedding_ref='text_embedding'))) / 
         ((((now_seconds() - item.published_at) / 3600) + 2) ** 1.8)
LIMIT 20

Using user attributes in feed scoring​

Incorporate user attributes (age, location, membership tier) into feed scoring to personalize results based on user profile:

SELECT *
FROM similarity(embedding_ref='als_embedding',
               encoder='precomputed_user',
               input_user_id='$user_id', limit=50),
     column_order(columns='_derived_popular_rank ASC', limit=50)
WHERE item_id NOT IN (SELECT item_id FROM interactions 
                      WHERE user_id = '$user_id')
ORDER BY lightgbm + 0.1 * user.age - 0.05 * item.price + 
         0.2 * item.rating + 0.15 * user.membership_tier
LIMIT 20

Anonymous feed​

For users without stored history, you can return popular items. This is useful for new users or anonymous visitors:

SELECT *
FROM column_order(columns='_derived_popular_rank ASC', limit=100)
LIMIT 20