Re: Novice SQL question

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).

On Tue, May 21, 2019 at 1:03 PM Dr. Drexl Spivey <[email protected]>
wrote:

> 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??
>
>
>