p is the alias for the petitions table (in the FROM clause). p.slug is the slug field on the table p. (fwiw the same table can appear with multiple references. in such a case, the table is treated as distinct tables. That's not the case here, but that's why I said "on the tale p" not "on the table petitions".)
In MySQL p.slug could be the slug table in the p database if the name of a table is required in the syntax. If a field is required in the syntax, it is always the table.field. PostgreSQL is the same, except it's the p.slug is the slug table in the p schema (in the current database).
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??
