Novice SQL question

Hello,

I don't know if this is the place for such inquiries, but I figured I
would try. Worse case, someone could point me in the right direction.

I got an assignment to determine the results of the following query.  I
can figure out most of it, but one or two questions remain.

WITH user_signatures AS (
    SELECT
        petition_id,
        email,
        RANK() OVER (PARTITION BY email ORDER BY created_at) AS
        petition_rank,
        COUNT(*) OVER (PARTITION BY email) AS petition_count
        FROM signatures
), first_user_signatures AS (
    SELECT *
    FROM user_signatures
    WHERE petition_rank = 1
)
SELECT
    p.id,
    p.slug,
    SUM(first_user_signatures.petition_count) AS activity_points
FROM petitions p INNER JOIN first_user_signatures
ON first_user_signatures.petition_id = p.id
GROUP BY p.id, p.slug
ORDER BY activity_points DESC
LIMIT 10;

Though I never used the "WITH" clause, I've learned it's used to create
CTE (temporary tables). Most of the clauses and results are self
explanatory, but I am unclear on the last subquery:

what exactly is the p.id, p.slug, and later the inter-join between the
petitions p.

My understanding, and I guess it's possible, "p" would be a database,
and "id, and slug" are tables within database p. But, then, my
understanding of the FROM clause, "petitions p" doesn't jive??

My only other guess is that the "p" is just some reference to the
"petitions" database??