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!