Explain a SQL Query

Reply staffs consultants onto client projects and tracks how much of each consultant's time is allocated where. Here's the schema and a query used by the staffing team's weekly report.

CREATE TABLE consultants (
    consultant_id  INT PRIMARY KEY,
    full_name      TEXT,
    seniority      TEXT
);

CREATE TABLE projects (
    project_id   INT PRIMARY KEY,
    client_name  TEXT,
    start_date   DATE,
    end_date     DATE   -- NULL means still active
);

CREATE TABLE assignments (
    assignment_id   INT PRIMARY KEY,
    consultant_id   INT REFERENCES consultants(consultant_id),
    project_id      INT REFERENCES projects(project_id),
    allocation_pct  INT,    -- percent of the consultant's time on this project
    assigned_at     TIMESTAMP
);

WITH consultant_load AS (
    SELECT
        a.consultant_id,
        p.project_id,
        p.client_name,
        a.allocation_pct,
        SUM(a.allocation_pct) OVER (PARTITION BY a.consultant_id) AS total_allocation,
        ROW_NUMBER() OVER (PARTITION BY a.consultant_id ORDER BY a.allocation_pct DESC) AS rank_within_consultant
    FROM assignments a
    JOIN projects p ON p.project_id = a.project_id
    WHERE p.end_date IS NULL OR p.end_date > CURRENT_DATE
)
SELECT consultant_id, project_id, client_name, allocation_pct, total_allocation
FROM consultant_load
WHERE rank_within_consultant = 1 AND total_allocation > 100;

Explain this query, showing your reasoning step by step: what does the CTE compute, what do the two window functions each do, and what does the final result actually represent for the staffing team?