Reports and Analytics - Data Insights
Introduction
Turn your data into actionable insights! This lesson shows you how to create reports and dashboards using aggregation, window functions, and advanced queries.
Report 1: Workspace Overview Dashboard
-- Executive summary of workspace activity
WITH workspace_stats AS (
SELECT
w.id,
w.name,
COUNT(DISTINCT wm.user_id) AS member_count,
COUNT(DISTINCT p.id) AS project_count,
COUNT(DISTINCT t.id) AS total_tasks,
COUNT(DISTINCT CASE WHEN t.status = 'done' THEN t.id END) AS completed_tasks,
COUNT(DISTINCT CASE WHEN t.due_date < CURRENT_DATE AND t.status != 'done' THEN t.id END) AS overdue_tasks
FROM workspaces w
LEFT JOIN workspace_members wm ON w.id = wm.workspace_id
LEFT JOIN projects p ON w.id = p.workspace_id AND p.deleted_at IS NULL
LEFT JOIN tasks t ON p.id = t.project_id AND t.deleted_at IS NULL
WHERE w.deleted_at IS NULL
GROUP BY w.id, w.name
)
SELECT
name AS workspace,
member_count,
project_count,
total_tasks,
completed_tasks,
ROUND(100.0 * completed_tasks / NULLIF(total_tasks, 0), 2) AS completion_rate,
overdue_tasks
FROM workspace_stats
ORDER BY name;
Report 2: Task Priority Distribution
-- Pie chart data: tasks by priority
SELECT
priority,
COUNT(*) AS task_count,
ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS percentage
FROM tasks
WHERE deleted_at IS NULL
AND status NOT IN ('done', 'cancelled')
GROUP BY priority
ORDER BY
CASE priority
WHEN 'high' THEN 1
WHEN 'medium' THEN 2
WHEN 'low' THEN 3
END;
Report 3: Weekly Task Completion Trend
-- Line chart data: tasks completed per week
SELECT
DATE_TRUNC('week', completed_at)::DATE AS week_start,
COUNT(*) AS tasks_completed
FROM tasks
WHERE completed_at IS NOT NULL
AND completed_at >= CURRENT_DATE - INTERVAL '12 weeks'
GROUP BY DATE_TRUNC('week', completed_at)
ORDER BY week_start;
Report 4: User Performance Leaderboard
-- Rank users by tasks completed
SELECT
u.first_name || ' ' || u.last_name AS user_name,
COUNT(DISTINCT t.id) FILTER (WHERE t.status = 'done') AS completed_tasks,
COUNT(DISTINCT c.id) AS comments_posted,
COUNT(DISTINCT t.id) FILTER (WHERE t.status = 'in_progress') AS in_progress_tasks,
MAX(t.completed_at) AS last_completion,
RANK() OVER (ORDER BY COUNT(DISTINCT t.id) FILTER (WHERE t.status = 'done') DESC) AS rank
FROM users u
LEFT JOIN task_assignments ta ON u.id = ta.user_id
LEFT JOIN tasks t ON ta.task_id = t.id
LEFT JOIN comments c ON u.id = c.user_id
GROUP BY u.id, u.first_name, u.last_name
HAVING COUNT(DISTINCT t.id) FILTER (WHERE t.status = 'done') > 0
ORDER BY rank;
Report 5: Project Health Report
-- Traffic light report: red/yellow/green projects
WITH project_metrics AS (
SELECT
p.id,
p.name,
COUNT(t.id) AS total_tasks,
COUNT(CASE WHEN t.status = 'done' THEN 1 END) AS completed,
COUNT(CASE WHEN t.due_date < CURRENT_DATE AND t.status != 'done' THEN 1 END) AS overdue,
MAX(t.updated_at) AS last_activity
FROM projects p
LEFT JOIN tasks t ON p.id = t.project_id AND t.deleted_at IS NULL
WHERE p.deleted_at IS NULL
GROUP BY p.id, p.name
)
SELECT
name AS project,
total_tasks,
completed,
overdue,
ROUND(100.0 * completed / NULLIF(total_tasks, 0), 0) AS completion_pct,
CASE
WHEN overdue > 3 OR (completion_pct < 30 AND total_tasks > 5) THEN '🔴 At Risk'
WHEN overdue > 0 OR (completion_pct < 60 AND total_tasks > 3) THEN '🟡 Needs Attention'
ELSE '🟢 On Track'
END AS health_status,
CURRENT_DATE - last_activity::DATE AS days_since_activity
FROM project_metrics
ORDER BY
CASE health_status
WHEN '🔴 At Risk' THEN 1
WHEN '🟡 Needs Attention' THEN 2
ELSE 3
END,
name;
Report 6: Tag Usage Analysis
-- Most popular tags
SELECT
tg.name,
tg.color,
COUNT(tt.task_id) AS times_used,
COUNT(DISTINCT t.project_id) AS projects_used_in,
STRING_AGG(DISTINCT p.name, ', ' ORDER BY p.name) AS projects
FROM tags tg
LEFT JOIN task_tags tt ON tg.id = tt.tag_id
LEFT JOIN tasks t ON tt.task_id = t.id
LEFT JOIN projects p ON t.project_id = p.id
GROUP BY tg.id, tg.name, tg.color
HAVING COUNT(tt.task_id) > 0
ORDER BY times_used DESC
LIMIT 10;
Report 7: Monthly Activity Heatmap
-- Calendar heatmap data: task completions by day
SELECT
DATE(completed_at) AS date,
COUNT(*) AS completions
FROM tasks
WHERE completed_at >= CURRENT_DATE - INTERVAL '90 days'
AND completed_at IS NOT NULL
GROUP BY DATE(completed_at)
ORDER BY date;
Report 8: Workspace Member Activity
-- When are team members most active?
SELECT
u.first_name || ' ' || u.last_name AS member,
wm.role,
COUNT(DISTINCT DATE(al.created_at)) AS active_days,
COUNT(al.id) AS total_actions,
MAX(al.created_at) AS last_seen
FROM workspace_members wm
INNER JOIN users u ON wm.user_id = u.id
LEFT JOIN activity_log al ON u.id = al.user_id AND al.workspace_id = wm.workspace_id
WHERE wm.workspace_id = 1
GROUP BY u.id, u.first_name, u.last_name, wm.role
ORDER BY total_actions DESC;
Report 9: Task Aging Report
-- How long have tasks been in each status?
SELECT
status,
COUNT(*) AS task_count,
ROUND(AVG(CURRENT_DATE - created_at::DATE), 1) AS avg_age_days,
MIN(CURRENT_DATE - created_at::DATE) AS min_age_days,
MAX(CURRENT_DATE - created_at::DATE) AS max_age_days
FROM tasks
WHERE deleted_at IS NULL
AND status != 'cancelled'
GROUP BY status
ORDER BY
CASE status
WHEN 'todo' THEN 1
WHEN 'in_progress' THEN 2
WHEN 'done' THEN 3
END;
Report 10: Overdue Task Alert
-- Critical: tasks overdue by more than 7 days
SELECT
t.title,
t.priority,
t.due_date,
CURRENT_DATE - t.due_date AS days_overdue,
u.first_name || ' ' || u.last_name AS assigned_to,
p.name AS project
FROM tasks t
LEFT JOIN task_assignments ta ON t.id = ta.task_id
LEFT JOIN users u ON ta.user_id = u.id
INNER JOIN projects p ON t.project_id = p.id
WHERE t.status NOT IN ('done', 'cancelled')
AND t.due_date < CURRENT_DATE - INTERVAL '7 days'
AND t.deleted_at IS NULL
ORDER BY days_overdue DESC;
Key Takeaways
- ✅ Aggregate data for summaries
- ✅ Use FILTER for conditional aggregation
- ✅ Window functions for rankings and percentages
- ✅ CTEs for complex multi-step reports
- ✅ CASE for categorization (red/yellow/green)
- ✅ Date functions for trends
- ✅ STRING_AGG for readable lists
Next Steps
In Lesson 8.6, you'll optimize these queries with indexes and analyze performance!
اختبار قصير
السؤال 1 من 520% مكتمل
0 من 5 أسئلة تمت الإجابة عنها
التالي · الدرس 43 من 44
Performance Optimization Basics
قراءة 1 دقيقة
أنهيت 42 من 44 درسًا

اسأل Byte عن هذا الدرس
محادثة خاصة حول هذا الدرس. قرأ Byte الدرس كاملًا، ويجيب خلال ثوانٍ، ويُظهر القسم الذي تأتي منه كل إجابة. يمكنك السؤال بأي لغة.
الأسئلة والأجوبة
أسئلة عامة حول هذا الدرس. يجيب AI Tutor فورًا بلغتك، ويراجع فريق FreeAcademy الإجابات، ويمكن لكل المتعلمين قراءتها.

