Bayesian Popularity
GLOBAL · SHRINKAGE · COLD STARTShrink item means toward the global mean according to evidence count.
target user = 1
EXECUTED SQL
Select Run recommender SQL.
—
SEVEN LIVE METHODS
One target user. Seven definitions of “relevant.”
User 1 has rated Space Odyssey = 5, Robot City = 4 and Quiet Letters = 1. Every method ranks the same three unseen candidates, so students can compare evidence rather than datasets.
Co-occurrence Graph
BASKET · EDGE · TRANSITIONModernizes the original KeyValue and probview flow into a positive-interaction graph.
target user = 1
EXECUTED SQL
Select Run recommender SQL.
—
Item-based Collaborative Filtering
ITEM × ITEM · ADJUSTED COSINELearns adjusted-cosine item similarity from co-rating users.
target user = 1
EXECUTED SQL
Select Run recommender SQL.
—
User-based Collaborative Filtering
USER × USER · NEIGHBOR DEVIATIONFinds similar users on overlapping items and aggregates neighbor deviations.
target user = 1
EXECUTED SQL
Select Run recommender SQL.
—
Content-based Profile
FEATURE VECTOR · COSINEBuilds a rating-weighted genre profile and scores candidates with cosine similarity.
target user = 1
EXECUTED SQL
Select Run recommender SQL.
—
Time-decayed Ranking
RECENCY · HALF-LIFE · DRIFTApplies exponential decay when taste or inventory changes over time.
target user = 1
EXECUTED SQL
Select Run recommender SQL.
—
Hybrid Rank
POPULARITY × CONTENT · WEIGHTED RANKCombines normalized popularity and content ranks with transparent weights.
target user = 1
EXECUTED SQL
Select Run recommender SQL.
—
SHARED TEACHING DATA
Small enough to audit every score. Rich enough to disagree.
The dataset is intentionally tiny. A student should be able to trace one recommendation back through every join and aggregate before scaling the pattern to retail events or MovieLens.
| User | Item | Rating |
|---|---|---|
| 1 | 1 | 5 |
| 1 | 2 | 4 |
| 1 | 3 | 1 |
| 2 | 1 | 4 |
| 2 | 2 | 5 |
| 2 | 4 | 1 |
| 3 | 3 | 5 |
| 3 | 4 | 4 |
| 3 | 5 | 4 |
| 4 | 1 | 2 |
| 4 | 3 | 4 |
| 4 | 5 | 5 |
| 5 | 2 | 4 |
| 5 | 4 | 5 |
| 5 | 6 | 4 |
| Item | Title | Content features |
|---|---|---|
| 1 | Space Odyssey | sci-fi, drama |
| 2 | Robot City | sci-fi, action |
| 3 | Quiet Letters | drama |
| 4 | Fast Track | action |
| 5 | Family Table | drama, comedy |
| 6 | Cosmic Joke | sci-fi, comedy |
KeyValue → graphview → probview → recommendation
The supplied retail SQL first reduces transactions to unique CustomerID–StockCode pairs. A self-join counts co-occurring products; item occurrence converts counts to conditional probabilities; the target customer’s owned products activate outgoing edges, and unseen destinations are ranked by summed probability.
What remains valuable
The graph is explainable: every recommendation has a path from something the customer already selected.
What the new page adds
Explicit positive-event policy, evidence count, modern CTEs, alternative models and ranking evaluation.
SYSTEM ARCHITECTURE
A model score is only one stage in a recommendation system.
A useful list also needs eligibility, deduplication, inventory or policy constraints, diversity, exploration and logging. Leaving these out creates a model demo—not a recommender system.
EVENTSview · rate · buy
CANDIDATESavailable · unseen
FEATURESuser · item · context
RANKERscore · blend · constrain
FEEDBACKimpression · action
EVALUATION & RESPONSIBILITY
A recommender changes what can be discovered.
Therefore evaluation must include ranking quality, system behavior and human consequences. High click-through can coexist with low diversity, fatigue or unfair exposure.
Offline ranking
TEMPORAL HOLDOUTPrecision@K · Recall@K · MAP · NDCG · MRR
Catalog behavior
BEYOND RELEVANCECoverage · diversity · novelty · serendipity · popularity concentration
Online outcomes
EXPERIMENTCTR · conversion · revenue · retention · hide/dismiss rate
Guardrails
RESPONSIBLE RANKINGExposure fairness · frequency caps · safety · inventory · latency
Three mistakes SQL cannot prevent for us
- Randomly splitting future interactions into training leaks information; use a temporal cutoff.
- Missing impressions make “not clicked” ambiguous: the user may never have seen the item.
- Optimizing clicks alone can reinforce popularity and narrow the catalog users encounter.
CONTINUE
Understand ranking in SQL, then decide where it should operate.
Connect this lesson to Pure SQL ML, Declarative ML and Data Systems Labs.