Business Queries - Solving Real-World Problems

Introduction

Now that you have data, let's write queries to answer business questions! These queries demonstrate JOIN, filtering, aggregation, and subquery techniques.

Query 1: My Tasks Due This Week

-- Find tasks assigned to a specific user, due within 7 days
SELECT
    t.id,
    t.title,
    t.status,
    t.priority,
    t.due_date,
    p.name AS project,
    w.name AS workspace
FROM tasks t
INNER JOIN task_assignments ta ON t.id = ta.task_id
INNER JOIN projects p ON t.id = p.id
INNER JOIN workspaces w ON p.workspace_id = w.id
WHERE ta.user_id = 2  -- Bob's ID
  AND t.status != 'done'
  AND t.due_date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '7 days'
ORDER BY t.due_date, t.priority DESC;

Query 2: Tasks by Status in a Project

-- Group tasks by status for project planning
SELECT
    status,
    COUNT(*) AS task_count,
    STRING_AGG(title, ', ' ORDER BY priority DESC) AS tasks
FROM tasks
WHERE project_id = 1
  AND deleted_at IS NULL
GROUP BY status
ORDER BY
    CASE status
        WHEN 'todo' THEN 1
        WHEN 'in_progress' THEN 2
        WHEN 'done' THEN 3
    END;

Query 3: Most Active Users

-- Find users with most activity in a workspace
SELECT
    u.first_name || ' ' || u.last_name AS user_name,
    COUNT(DISTINCT t.id) AS tasks_created,
    COUNT(DISTINCT c.id) AS comments_posted,
    COUNT(DISTINCT al.id) AS total_actions
FROM users u
LEFT JOIN tasks t ON u.id = t.created_by
LEFT JOIN comments c ON u.id = c.user_id
LEFT JOIN activity_log al ON u.id = al.user_id
INNER JOIN workspace_members wm ON u.id = wm.user_id
WHERE wm.workspace_id = 1
GROUP BY u.id, u.first_name, u.last_name
ORDER BY total_actions DESC
LIMIT 10;

Query 4: Project Completion Rate

-- Calculate what percentage of tasks are done per project
SELECT
    p.name AS project,
    COUNT(t.id) AS total_tasks,
    COUNT(CASE WHEN t.status = 'done' THEN 1 END) AS completed_tasks,
    ROUND(
        100.0 * COUNT(CASE WHEN t.status = 'done' THEN 1 END) / NULLIF(COUNT(t.id), 0),
        2
    ) AS completion_rate
FROM projects p
LEFT JOIN tasks t ON p.id = t.project_id AND t.deleted_at IS NULL
WHERE p.workspace_id = 1
  AND p.deleted_at IS NULL
GROUP BY p.id, p.name
ORDER BY completion_rate DESC;

Query 5: Task Activity Log

-- Show recent activity for tasks
SELECT
    al.created_at,
    u.first_name || ' ' || u.last_name AS user_name,
    al.action,
    al.entity_type,
    CASE
        WHEN al.entity_type = 'task' THEN (SELECT title FROM tasks WHERE id = al.entity_id)
        WHEN al.entity_type = 'project' THEN (SELECT name FROM projects WHERE id = al.entity_id)
    END AS entity_name
FROM activity_log al
LEFT JOIN users u ON al.user_id = u.id
WHERE al.workspace_id = 1
  AND al.created_at >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY al.created_at DESC
LIMIT 50;

Query 6: Overdue Tasks by Assignee

-- Find overdue tasks grouped by who they're assigned to
SELECT
    u.first_name || ' ' || u.last_name AS assignee,
    COUNT(t.id) AS overdue_count,
    STRING_AGG(t.title, ', ' ORDER BY t.due_date) AS overdue_tasks,
    MIN(t.due_date) AS earliest_overdue
FROM tasks t
INNER JOIN task_assignments ta ON t.id = ta.task_id
INNER JOIN users u ON ta.user_id = u.id
WHERE t.status NOT IN ('done', 'cancelled')
  AND t.due_date < CURRENT_DATE
  AND t.deleted_at IS NULL
GROUP BY u.id, u.first_name, u.last_name
ORDER BY overdue_count DESC;

Query 7: Tasks with Tags

-- Show tasks with their associated tags
SELECT
    t.title,
    t.status,
    t.priority,
    STRING_AGG(tg.name, ', ' ORDER BY tg.name) AS tags
FROM tasks t
LEFT JOIN task_tags tt ON t.id = tt.task_id
LEFT JOIN tags tg ON tt.tag_id = tg.id
WHERE t.project_id = 1
GROUP BY t.id, t.title, t.status, t.priority
ORDER BY t.created_at DESC;

Query 8: Workspaces I'm a Member Of

-- Show all workspaces for a user with their role
SELECT
    w.id,
    w.name,
    w.description,
    wm.role,
    COUNT(DISTINCT p.id) AS project_count,
    COUNT(DISTINCT t.id) AS total_tasks
FROM workspaces w
INNER 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 wm.user_id = 2  -- Bob's ID
  AND w.deleted_at IS NULL
GROUP BY w.id, w.name, w.description, wm.role
ORDER BY w.name;

Query 9: Tasks Without Assignees

-- Find unassigned tasks that need attention
SELECT
    t.id,
    t.title,
    t.priority,
    t.due_date,
    p.name AS project
FROM tasks t
INNER JOIN projects p ON t.project_id = p.id
LEFT JOIN task_assignments ta ON t.id = ta.task_id
WHERE ta.task_id IS NULL
  AND t.status = 'todo'
  AND t.deleted_at IS NULL
ORDER BY t.priority DESC, t.due_date NULLS LAST;

Query 10: Task Comments with User Info

-- Show task discussion thread
SELECT
    c.created_at,
    u.first_name || ' ' || u.last_name AS author,
    c.content,
    CASE
        WHEN c.updated_at > c.created_at THEN '(edited)'
        ELSE ''
    END AS edited
FROM comments c
INNER JOIN users u ON c.user_id = u.id
WHERE c.task_id = 1
  AND c.deleted_at IS NULL
ORDER BY c.created_at ASC;

Practice Exercises

Try writing these queries yourself:

1. Find all high-priority tasks due in the next month
2. List projects with no tasks
3. Show users who haven't been assigned any tasks
4. Calculate average number of tasks per project
5. Find tags that are used most frequently

Key Takeaways

• ✅ Complex JOINs across multiple tables
• ✅ Aggregation with GROUP BY and STRING_AGG
• ✅ Subqueries for dynamic data
• ✅ Filtering with WHERE and HAVING
• ✅ CASE statements for conditional logic
• ✅ Date calculations for due dates
• ✅ LEFT JOINs to find missing relationships

Next Steps

In Lesson 8.5, you'll create analytical reports and dashboards using advanced aggregation!