Mailing List Archive

Reports on contributors
Wondering if anyone's run reports in Postgres for say:

+ Contributors with stories published in the last year
+ Contributors sorted by number of associated stories

… and so on? Specifically, I'm trying to remember the path through the tables from 'story' to 'person'.

Any tips welcome (and will be added to the Github wiki of useful SQL)!

Phillip.


--
Phillip Smith
http://phillipadsmith.com
Re: Reports on contributors [ In reply to ]
On 2014-10-21, at 3:36 PM, Phillip Smith <ps@phillipadsmith.com> wrote:

>
> Wondering if anyone's run reports in Postgres for say:
>
> + Contributors with stories published in the last year
> + Contributors sorted by number of associated stories
>
> … and so on? Specifically, I'm trying to remember the path through the tables from 'story' to 'person'.
>
> Any tips welcome (and will be added to the Github wiki of useful SQL)!

FYI, here's one way to do it:

SELECT person.id as pid,
person.lname,
person.fname,
COUNT (person.id) as count
FROM person INNER JOIN person_member ON person.id = person_member.object_id
INNER JOIN story__contributor ON person_member.member__id = story__contributor.member__id
INNER JOIN story_instance ON story__contributor.story_instance__id = story_instance.id
INNER JOIN story ON story_instance.story__id = story.id
WHERE story_instance.cover_date > '2013-10-15 00:00:00' AND story.element_type__id = 1 AND story.active = 't'
GROUP BY pid, person.fname, person.lname
ORDER BY count DESC;


--
Phillip Smith
http://phillipadsmith.com
Re: Reports on contributors [ In reply to ]
On Oct 27, 2014, at 11:58 AM, Phillip Smith <ps@phillipadsmith.com> wrote:
>
> FYI, here's one way to do it:
>
> SELECT person.id as pid,
> person.lname,
> person.fname,
> COUNT (person.id) as count
> FROM person INNER JOIN person_member ON person.id = person_member.object_id
> INNER JOIN story__contributor ON person_member.member__id = story__contributor.member__id
> INNER JOIN story_instance ON story__contributor.story_instance__id = story_instance.id
> INNER JOIN story ON story_instance.story__id = story.id
> WHERE story_instance.cover_date > '2013-10-15 00:00:00' AND story.element_type__id = 1 AND story.active = 't'
> GROUP BY pid, person.fname, person.lname
> ORDER BY count DESC;

I think you might get inaccurate numbers for stories that have had more than one version. I suggest also joining story.published_version to story_instance.version. That will also nicely limit it only to stories that have been published.

Best,

David
Re: Reports on contributors [ In reply to ]
On 2014-10-27, at 4:15 PM, David E. Wheeler <david@justatheory.com> wrote:

> On Oct 27, 2014, at 11:58 AM, Phillip Smith <ps@phillipadsmith.com> wrote:
>>
>> FYI, here's one way to do it:
>>
>> SELECT person.id as pid,
>> person.lname,
>> person.fname,
>> COUNT (person.id) as count
>> FROM person INNER JOIN person_member ON person.id = person_member.object_id
>> INNER JOIN story__contributor ON person_member.member__id = story__contributor.member__id
>> INNER JOIN story_instance ON story__contributor.story_instance__id = story_instance.id
>> INNER JOIN story ON story_instance.story__id = story.id
>> WHERE story_instance.cover_date > '2013-10-15 00:00:00' AND story.element_type__id = 1 AND story.active = 't'
>> GROUP BY pid, person.fname, person.lname
>> ORDER BY count DESC;
>
> I think you might get inaccurate numbers for stories that have had more than one version. I suggest also joining story.published_version to story_instance.version. That will also nicely limit it only to stories that have been published.

There's a typo there, that should have been a COUNT on story.id, which is how I was thinking that I was only counting once per story vs. per story instance.

You're right about the published version. I was using 'active' as indicator that the story was not expired or deleted.

I'll try a few variations and post them back here and to the wiki.

Philip.
Re: Reports on contributors [ In reply to ]
On Oct 29, 2014, at 1:06 PM, Phillip Smith <ps@phillipadsmith.com> wrote:
>
> There's a typo there, that should have been a COUNT on story.id, which is how I was thinking that I was only counting once per story vs. per story instance.

That would have to be COUNT(DISTINCT story_id).

> You're right about the published version. I was using 'active' as indicator that the story was not expired or deleted.
>
> I'll try a few variations and post them back here and to the wiki.

Cool!

David
Re: Reports on contributors [ In reply to ]
On 2014-10-30, at 12:29 PM, David E. Wheeler <david@justatheory.com> wrote:

> On Oct 29, 2014, at 1:06 PM, Phillip Smith <ps@phillipadsmith.com> wrote:
>>
>> There's a typo there, that should have been a COUNT on story.id, which is how I was thinking that I was only counting once per story vs. per story instance.
>
> That would have to be COUNT(DISTINCT story_id).

Ah, yes, that looks more accurate.

>
>> You're right about the published version. I was using 'active' as indicator that the story was not expired or deleted.
>>
>> I'll try a few variations and post them back here and to the wiki.
>
> Cool!
>
> David
>