-- Daily active users (last 30 days)
SELECT DATE(created_at) as day,
COUNT(DISTINCT user_id) as active_users
FROM analytics_events
WHERE created_at > NOW()
- INTERVAL '30 days'
GROUP BY DATE(created_at)
ORDER BY day;
-- Most popular filters this week
SELECT metadata->>'filter_name' as filter,
COUNT(*) as uses
FROM analytics_events
WHERE event_type = 'filter_apply'
AND created_at > NOW()
- INTERVAL '7 days'
GROUP BY filter
ORDER BY uses DESC
LIMIT 10;
-- Storage usage per user
SELECT u.name,
COUNT(e.id) as image_count,
ROUND(SUM(
(e.metadata->>'size_mb')::numeric
), 2) as total_mb
FROM users u
JOIN analytics_events e
ON u.id = e.user_id
WHERE e.event_type = 'upload'
GROUP BY u.name
ORDER BY total_mb DESC;
-- Migration: 001_add_avatar_to_users.sql
ALTER TABLE users
ADD COLUMN avatar_url TEXT;
ALTER TABLE users
ADD COLUMN last_login_at TIMESTAMP;
const { Pool } = require('pg');
const pool = new Pool({
connectionString: process.env.DATABASE_URL,
});
app.get('/api/analytics/daily-active-users',
authenticate, async (req, res) => {
const result = await pool.query(`
SELECT DATE(created_at) as day,
COUNT(DISTINCT user_id) as active_users
FROM analytics_events
WHERE created_at > NOW()
- INTERVAL '30 days'
GROUP BY DATE(created_at)
ORDER BY day
`);
res.json(result.rows);
});
Build an analytics dashboard page showing charts of usage data: daily active users line chart, filter popularity bar chart, storage usage per user table.
git switch -c feature/PIXELCRAFT-054-analytics-dashboard
git add server/ src/
git commit -m "Add analytics dashboard with complex SQL queries (PIXELCRAFT-054)"
git push origin feature/PIXELCRAFT-054-analytics-dashboard
# PR → Review → Merge → Close ticket ✅
Database normalization.
Every database design is a tradeoff between: read speed, write speed, data consistency, and storage efficiency.