8. Date-range filtering on Analytics
The Analytics page renders every snapshot you've ever taken. Sometimes that's what you want; sometimes you only care about the last six months. A ?range= query parameter lets the reader choose, the helpers take an optional cutoff date, and the template renders four filter buttons.
Adding date-range filtering
The Analytics page currently shows every snapshot the database carries. Users often want narrower windows: last 6 months, last 2 years, or all time. Adding date filtering lets them control the view without cluttering the interface.
Implement this with URL query parameters. The route reads ?range= from the request, converts it to a cutoff snapshot_date, and passes that cutoff into each helper. The template includes filter buttons that link to different range values.
Update your analytics() route and the three helpers to accept the cutoff:
# app.py -- updates to support ?range= filtering
# Add `timedelta` to your existing datetime import:
from datetime import datetime, timedelta
RANGE_MAP = {
'6m': 180,
'12m': 365,
'24m': 730,
'all': None,
}
def _cutoff_for(range_param):
"""Translate ?range= value to an ISO-format cutoff date or None."""
days = RANGE_MAP.get(range_param, 365)
if days is None:
return None
return (datetime.now() - timedelta(days=days)).strftime('%Y-%m-%d')
def get_catalogue_growth(cutoff=None):
conn = get_db_connection()
if not conn:
return {'labels': [], 'data': []}
try:
cursor = conn.cursor()
sql = """
SELECT strftime('%Y-%m', s.snapshot_date) AS month,
COUNT(*) AS discoveries
FROM snapshots s
WHERE s.time_range = 'short_term'
AND NOT EXISTS (
SELECT 1 FROM snapshots earlier
WHERE earlier.track_id = s.track_id
AND earlier.snapshot_date < s.snapshot_date
)
"""
params = []
if cutoff:
sql += " AND s.snapshot_date >= ?"
params.append(cutoff)
sql += " GROUP BY month ORDER BY month"
cursor.execute(sql, params)
rows = cursor.fetchall()
return {
'labels': [_format_month(r['month']) for r in rows],
'data': [r['discoveries'] for r in rows],
}
except sqlite3.Error as e:
print(f"Catalogue-growth query error: {e}")
return {'labels': [], 'data': []}
finally:
conn.close()
def get_artist_diversity(cutoff=None):
conn = get_db_connection()
if not conn:
return {'labels': [], 'data': []}
try:
cursor = conn.cursor()
sql = """
SELECT strftime('%Y-%m', s.snapshot_date) AS month,
COUNT(DISTINCT t.artist_name) AS artist_count
FROM snapshots s
JOIN tracks t ON t.track_id = s.track_id
WHERE s.time_range = 'short_term'
"""
params = []
if cutoff:
sql += " AND s.snapshot_date >= ?"
params.append(cutoff)
sql += " GROUP BY month ORDER BY month"
cursor.execute(sql, params)
rows = cursor.fetchall()
return {
'labels': [_format_month(r['month']) for r in rows],
'data': [r['artist_count'] for r in rows],
}
except sqlite3.Error as e:
print(f"Artist-diversity query error: {e}")
return {'labels': [], 'data': []}
finally:
conn.close()
def get_consistent_artists(cutoff=None, limit=10):
conn = get_db_connection()
if not conn:
return {'labels': [], 'data': []}
try:
cursor = conn.cursor()
sql = """
SELECT t.artist_name,
COUNT(DISTINCT s.snapshot_date) AS appearances
FROM tracks t
JOIN snapshots s ON s.track_id = t.track_id
WHERE s.time_range = 'short_term'
"""
params = []
if cutoff:
sql += " AND s.snapshot_date >= ?"
params.append(cutoff)
sql += """
GROUP BY t.artist_name
ORDER BY appearances DESC
LIMIT ?
"""
params.append(limit)
cursor.execute(sql, params)
rows = cursor.fetchall()
return {
'labels': [r['artist_name'] for r in rows],
'data': [r['appearances'] for r in rows],
}
except sqlite3.Error as e:
print(f"Consistent-artists query error: {e}")
return {'labels': [], 'data': []}
finally:
conn.close()
@app.route('/analytics')
@require_auth
def analytics():
range_param = request.args.get('range', '12m')
if range_param not in RANGE_MAP:
range_param = '12m' # invalid input falls back silently
cutoff = _cutoff_for(range_param)
chart_data = {
'growth': get_catalogue_growth(cutoff),
'diversity': get_artist_diversity(cutoff),
'artists': get_consistent_artists(cutoff),
}
return render_template(
'analytics.html',
chart_data=chart_data,
current_range=range_param,
)
The route now accepts a range query parameter with values 6m, 12m, 24m, or all. The route translates that to a cutoff snapshot_date via _cutoff_for(), hands the cutoff to each helper, and passes current_range to the template so the UI can highlight the active filter button. Invalid ?range= values fall back to 12m silently rather than crash.
One detail worth noticing: every helper appends the cutoff with sql += " AND s.snapshot_date >= ?" and adds the value to params. That keeps the filter parameterised, so even if someone hand-edits the URL, the date value is data, not executable SQL.
Now add a filter-bar block to analytics.html, immediately above the existing <div class="analytics-grid"> wrapper. The chart containers from the previous page stay exactly as they were:
<!-- Insert above the existing <div class="analytics-grid"> from the previous page -->
<!-- Date Range Filters -->
<div class="filter-bar">
<span class="filter-label">Time Range:</span>
<div class="filter-buttons">
<a href="{{ url_for('analytics', range='6m') }}"
class="filter-btn {% if current_range == '6m' %}active{% endif %}">
6 Months
</a>
<a href="{{ url_for('analytics', range='12m') }}"
class="filter-btn {% if current_range == '12m' or not current_range %}active{% endif %}">
12 Months
</a>
<a href="{{ url_for('analytics', range='24m') }}"
class="filter-btn {% if current_range == '24m' %}active{% endif %}">
2 Years
</a>
<a href="{{ url_for('analytics', range='all') }}"
class="filter-btn {% if current_range == 'all' %}active{% endif %}">
All Time
</a>
</div>
</div>
The filter bar uses Jinja2's url_for() to generate proper URLs with query parameters. The {% if current_range == '12m' or not current_range %} condition ensures the 12-month button is active by default. The active class highlights the selected filter visually.
Add these styles for the filter bar:
/* Date Range Filters */
.filter-bar {
display: flex;
align-items: center;
gap: 1rem;
margin-top: 1.5rem;
padding: 1rem;
background: #181818;
border-radius: 8px;
}
.filter-label {
color: #B3B3B3;
font-size: 0.875rem;
font-weight: 600;
}
.filter-buttons {
display: flex;
gap: 0.5rem;
}
.filter-btn {
padding: 0.5rem 1rem;
background: #282828;
color: #B3B3B3;
text-decoration: none;
border-radius: 20px;
font-size: 0.875rem;
transition: all 0.2s;
border: 2px solid transparent;
}
.filter-btn:hover {
background: #333;
color: #fff;
}
.filter-btn.active {
background: #1DB954;
color: #fff;
border-color: #1DB954;
}
@media (max-width: 768px) {
.filter-bar {
flex-direction: column;
align-items: flex-start;
}
.filter-buttons {
width: 100%;
flex-wrap: wrap;
}
.filter-btn {
flex: 1;
text-align: center;
min-width: calc(50% - 0.25rem);
}
}The filter buttons use rounded corners and a pill-style design common in modern web interfaces. The active state uses Spotify green with high contrast. On mobile, buttons wrap into two columns for easier tapping.
Why URL parameters instead of JavaScript filtering
You could filter client-side: send every snapshot to the browser and let JavaScript hide or show subsets based on the selected range. That works for small datasets but fails at scale. With ten thousand snapshot rows, shipping everything wastes bandwidth and slows the page.
URL parameters keep filtering in the database, which is far more efficient. The route queries and sends only what the reader asked for. The approach also makes filtered views shareable (readers can bookmark specific ranges) and works even with JavaScript disabled. Professional applications prioritise server-side filtering for performance and accessibility.
Checkpoint: Analytics page concepts
Test your understanding of the Analytics page implementation before moving forward.
Why does the Analytics route call three separate helper functions instead of one large query?
Answer: Each chart needs a different shape: monthly discovery counts for the line, monthly artist-diversity counts for the bar, ranked artists for the horizontal bar. Combining them into one query with JOINs and GROUP BYs would force post-processing in Python anyway. Three small helpers are clearer to read, easier to test in isolation, and let each helper handle its own database failure (one bad query doesn't sink the whole page). The performance cost of three separate connections is negligible at typical snapshot volumes (hundreds to low thousands of rows).
What happens if a reader visits /analytics?range=invalid with an unrecognised range parameter?
Answer: The route checks range_param not in RANGE_MAP before calling _cutoff_for; if the value is unknown, the route silently coerces it to '12m'. The reader sees the 12-month view with the 12-month filter button highlighted; no error is flashed. This shape is preferable to a 400-style rejection because the only way to reach the route with a bad value is hand-typing the URL, and a permissive fallback is friendlier than a stack trace.
Why do the single-series bar charts hide their legends?
Answer: A legend is useful when the reader needs to distinguish multiple datasets. These charts each show one dataset, and the title plus axis labels already explain what the bars mean. Showing a legend that repeats "Distinct artists" or "Snapshots" adds clutter without adding information.
What would break if you renamed the Python dictionary key from 'growth' to 'catalogueGrowth' in the route?
Answer: The JavaScript would fail because it reads chartData.growth.labels. The Python key becomes a JavaScript property name through the |tojson|safe filter; renaming one side without the other breaks the handoff silently (no console error -- the chart just doesn't render). You'd need to change both: Python's chart_data['catalogueGrowth'] and JavaScript's chartData.catalogueGrowth. Keeping the Python and JavaScript key names identical is what prevents this kind of silent chart breakage.