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

Thanks! I just hadn't seen that syntax before, but it makes 100% sense,
and matches the databases and tables.

-----Original Message-----
From: James Keener <jim@jimkeener.com>
To: drexl@little-beak.com
Cc: pgsql-novice@lists.postgresql.org
Subject: Re: Novice SQL question
Date: Tue, 21 May 2019 13:10:17 -0400

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 <drexl@little-beak.com
> 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??
> 
> 
>