Leaderboard — Rank Models by Score
Query all BenchmarkJobs, join with Model, sort by score descending, render as an HTML table. The competitive heart of the platform.
# The basic concept: sort a list by a key
results = [
{'model': 'random_forest', 'score': 0.92},
{'model': 'logistic', 'score': 0.81},
{'model': 'xgboost', 'score': 0.95},
{'model': 'knn', 'score': 0.74},
]
# Sort by score, descending
ranked = sorted(results, key=lambda r: r['score'], reverse=True)
for i, r in enumerate(ranked, 1):
print(f'{i}. {r["model"]:20s} {r["score"]:.3f}')
# Output:
# 1. xgboost 0.950
# 2. random_forest 0.920
# 3. logistic 0.810
# 4. knn 0.740
# In SQL: SELECT * FROM benchmark_jobs ORDER BY score DESC;
# Step 7: Add /leaderboard on top of steps 1-6
from sqlalchemy import desc
from database import Model, BenchmarkJob, User
@app.get('/leaderboard', response_class=HTMLResponse)
async def leaderboard(request: Request, db: Session = Depends(get_db)):
# Query: join BenchmarkJob + Model + User, order by score desc
rows = (db.query(BenchmarkJob, Model, User)
.join(Model, BenchmarkJob.model_id == Model.id)
.join(User, Model.owner_id == User.id)
.order_by(desc(BenchmarkJob.score))
.limit(100)
.all())
# Build a list of dicts for the template
entries = [{
'rank': i + 1,
'model_name': m.name,
'owner': u.username,
'score': round(b.score, 4),
'metric': b.metric,
} for i, (b, m, u) in enumerate(rows)]
return templates.TemplateResponse('leaderboard.html', {
'request': request, 'entries': entries,
})
{% extends 'base.html' %}
{% block title %}Leaderboard{% endblock %}
{% block content %}
<h1>Leaderboard</h1>
{% if entries %}
<table style='width:100%; border-collapse:collapse;'>
<thead>
<tr style='border-bottom:2px solid #a0c000; text-align:left;'>
<th>Rank</th>
<th>Model</th>
<th>Owner</th>
<th>Score</th>
<th>Metric</th>
</tr>
</thead>
<tbody>
{% for e in entries %}
<tr style='border-bottom:1px solid #eee;'>
<td>{{ e.rank }}</td>
<td>{{ e.model_name }}</td>
<td>{{ e.owner }}</td>
<td>{{ e.score }}</td>
<td>{{ e.metric }}</td>
</tr>
{% endfor %}
</tbody>
</table>
{% else %}
<p>No benchmarks yet. Upload a model and run a benchmark!</p>
{% endif %}
{% endblock %}
How it works
A leaderboard is just a sorted, joined query rendered as a table. The SQL equivalent of our query is: `SELECT * FROM benchmark_jobs JOIN models ON ... JOIN users ON ... ORDER BY score DESC LIMIT 100`. SQLAlchemy translates the .join() + .order_by() + .limit() chain into that SQL. The key insight: do the sorting in the database, not in Python. The DB is optimized for sorting (indexes, etc.); pulling all rows into Python and calling sorted() is slow for large tables. The template uses {% for e in entries %} to render one <tr> per entry. The rank is just the loop index + 1 (Jinja's loop.index starts at 1, so we could use that instead). The {% if entries %} / {% else %} block shows an empty state when there's no data yet — important for new users.
- Sorting in Python instead of SQL — `sorted(rows, key=...)` pulls every row into memory. Use `.order_by(desc(Score))` so the DB does it.
- N+1 queries — if you query BenchmarkJob then separately query Model for each one, you do 1 + N queries. Use `.join()` to do it in one query.
- Not handling ties — two models with the same score will get different ranks (1, 2) instead of (1, 1). Add a secondary sort key (e.g., earliest benchmark wins ties).
- Displaying raw floats — `0.9532146789` looks messy. Use `round(score, 4)` or Jinja's `{{ '%.3f' % e.score }}`.