Objective

Write a SQL query to create a histogram of employee query activity for Q3 2023.

Table Schema:

• employees

• queries

Task Requirements:

• Filter queries executed in Q3 2023 (July, August, September)
• Count unique queries per employee
• Include employees who executed zero queries
• Count how many employees executed each number of unique queries
• Sort results by query count in ascending order
  
  

Output columns: unique_queries, employee_count

SELECT 
    COALESCE(unique_queries, 0) AS unique_queries,
    COUNT(*) AS employee_count
FROM (
    SELECT 
        e.employee_id,
        COUNT(DISTINCT q.query_id) AS unique_queries
    FROM employees e
    LEFT JOIN queries q ON e.employee_id = q.employee_id
        AND EXTRACT(MONTH FROM q.execution_date) IN (7, 8, 9)
        AND EXTRACT(YEAR FROM q.execution_date) = 2023
    GROUP BY e.employee_id
) AS employee_query_counts
GROUP BY unique_queries
ORDER BY unique_queries ASC;

Explanation

Step 1: Understanding the Histogram with Zero Values

This problem requires including employees who executed zero queries during Q3 2023, necessitating a LEFT JOIN to preserve all employees even without matching query records.

Step 2: Filtering Q3 2023 Queries in the JOIN

LEFT JOIN queries q ON e.employee_id = q.employee_id
    AND EXTRACT(MONTH FROM q.execution_date) IN (7, 8, 9)
    AND EXTRACT(YEAR FROM q.execution_date) = 2023

Date filters are placed in the JOIN condition (not WHERE) to preserve all employees from the LEFT JOIN while only matching Q3 2023 queries.

Step 3: Inner Query - Counting Unique Queries Per Employee

COUNT(DISTINCT q.query_id) AS unique_queries

Counts distinct query_ids per employee. For employees with no matching queries, this returns 0.

Step 4: Outer Query - Creating the Histogram

COALESCE(unique_queries, 0) AS unique_queries,
COUNT(*) AS employee_count

Groups employees by their query count and tallies how many employees fall into each bucket.

Step 5: Ordering the Results

ORDER BY unique_queries ASC

Sorts by query count ascending to create a proper histogram from least to most active.