Real-time insights from PostgreSQL analytics system
This is a production-grade PostgreSQL analytics system that identifies content strategy opportunities through engagement pattern analysis. Built for Jumper Media, this system analyzes thousands of posts and author behaviors to provide actionable insights.
Key Insight: The system identifies authors posting high volumes of low-engagement content, providing a quantifiable "opportunity score" that predicts potential engagement improvements through coaching.
Content platforms face critical challenges:
The system uses a normalized relational schema optimized for analytical queries:
authors ├── author_id (PK) ├── author_name ├── author_category └── created_at posts ├── post_id (PK) ├── author_id (FK → authors) ├── title ├── content ├── post_category ├── views ├── likes ├── comments └── posted_at engagements (detailed engagement events) ├── engagement_id (PK) ├── post_id (FK → posts) ├── engagement_type (view, like, comment) ├── engagement_value └── engagement_timestamp engagement_stats (materialized view) ├── post_id ├── total_views ├── total_likes ├── total_comments ├── total_engagements └── last_updated Indexes: • idx_posts_author_category (author_id, post_category) • idx_engagements_post_timestamp (post_id, engagement_timestamp) • idx_engagement_stats_post (post_id) on materialized view
Three production-ready analytical queries power the insights:
Identifies coaching opportunities by comparing author performance against category medians:
WITH author_performance AS (
SELECT
a.author_id,
a.author_name,
a.author_category,
COUNT(DISTINCT p.post_id) as total_posts,
AVG(es.total_engagements) as avg_engagement_per_post
FROM authors a
JOIN posts p ON a.author_id = p.author_id
JOIN engagement_stats es ON p.post_id = es.post_id
GROUP BY a.author_id
),
category_benchmarks AS (
SELECT
author_category,
PERCENTILE_CONT(0.5) WITHIN GROUP
(ORDER BY avg_engagement_per_post) as median_engagement
FROM author_performance
GROUP BY author_category
)
SELECT
ap.*,
cb.median_engagement as category_median,
(cb.median_engagement - ap.avg_engagement_per_post) * ap.total_posts
as opportunity_score
FROM author_performance ap
JOIN category_benchmarks cb ON ap.author_category = cb.author_category
WHERE ap.avg_engagement_per_post < cb.median_engagement
ORDER BY opportunity_score DESC;
Analyzes when users are most engaged using hour-of-day and day-of-week patterns:
SELECT
EXTRACT(DOW FROM e.engagement_timestamp) as day_of_week,
EXTRACT(HOUR FROM e.engagement_timestamp) as hour_of_day,
COUNT(*) as total_engagements,
COUNT(DISTINCT e.post_id) as unique_posts
FROM engagements e
GROUP BY day_of_week, hour_of_day
ORDER BY day_of_week, hour_of_day;
Benchmarks top performers for each content category:
WITH ranked_authors AS (
SELECT
a.author_name,
a.author_category,
COUNT(DISTINCT p.post_id) as total_posts,
SUM(es.total_engagements) as total_engagements,
AVG(es.total_engagements) as avg_engagement,
ROW_NUMBER() OVER (
PARTITION BY a.author_category
ORDER BY SUM(es.total_engagements) DESC
) as category_rank
FROM authors a
JOIN posts p ON a.author_id = p.author_id
JOIN engagement_stats es ON p.post_id = es.post_id
GROUP BY a.author_id, a.author_category
)
SELECT * FROM ranked_authors
WHERE category_rank <= 10
ORDER BY author_category, category_rank;
The FastAPI backend exposes real-time analytics through RESTful endpoints:
GET /engagement/{post_id} - Individual post engagement metricsGET /author/{author_id}/trends - Author performance trendsGET /categories/top - Top performing categoriesGET /analytics/engagement-patterns - Time-based patternsGET /analytics/opportunity-authors - Coaching opportunitiesGET /sample/posts - Sample posts with engagement dataGET /sample/authors - Author performance segmentationBased on the current dataset analysis: