person_submission_download_stats2

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

last_name

varchar

45

√

null

first_name

varchar

45

√

null

email

varchar

100

√

null

person_num

int4

10

√

null

person.person_num

Implied ConstraintR

submission_id

int4

10

√

null

life_time_download_cnt

int8

19

√

null

Relationships

Close relationships within degrees of separation

View Definition

SELECT DISTINCT tt1.last_name,
    tt1.first_name,
    tt1.email,
    tt1.person_num,
    tt1.submission_id,
    t3.cnt AS life_time_download_cnt
   FROM (( SELECT DISTINCT t1.first_name,
            t1.last_name,
            t1.email,
            t1.person_num,
            t1.submission_id
           FROM (( SELECT p.first_name,
                    p.last_name,
                    p.email,
                    p.person_num,
                    a.submission_id
                   FROM ((author_list a
                     JOIN person p ON (((p.person_num = a.person_num) AND (a.author_order = 1))))
                     JOIN submissions s ON (((a.submission_id = s.submission_id) AND (s.release_date < now()) AND (s.status_id = 2) AND (a.person_num IS NOT NULL))))) t1
             FULL JOIN ( SELECT g.first_name,
                    g.last_name,
                    g.email,
                    s.submission_id
                   FROM (submissions s
                     JOIN grl_user g ON (((g.account_id = s.created_by) AND ((g.last_name)::text <> 'EarthChem'::text) AND (s.status_id = 2) AND (s.release_date <= now()))))) t2 ON ((((t2.first_name)::text = (t1.first_name)::text) AND ((t2.last_name)::text = (t1.last_name)::text) AND ((t2.email)::text = (t1.email)::text))))) tt1
     JOIN ( SELECT dss.submission_id,
            count(dss.submission_id) AS cnt
           FROM (( SELECT DISTINCT ds.submission_id,
                    ds.download_ip,
                    ds.download_date
                   FROM download_stats ds
                  WHERE ((ds.submission_id IS NOT NULL) AND ((ds.download_ip)::text <> ALL (ARRAY[('129.236.40.238'::character varying)::text, ('129.236.6.17'::character varying)::text, ('128.118.52.28'::character varying)::text, ('129.236.40.190'::character varying)::text, ('129.236.40.215'::character varying)::text, ('129.236.40.174'::character varying)::text, ('129.236.40.157'::character varying)::text, ('129.236.40.200'::character varying)::text, ('129.236.6.198'::character varying)::text, ('129.236.40.156'::character varying)::text])))) dss
             JOIN submissions s ON (((dss.submission_id = s.submission_id) AND (s.status_id = 2) AND (s.release_date < now()))))
          GROUP BY dss.submission_id) t3 ON ((t3.submission_id = tt1.submission_id)))
  WHERE (((tt1.email IS NOT NULL) OR ((tt1.email)::text <> ' '::text)) AND ((tt1.email)::text <> 'info@earthchem.org'::text))
  ORDER BY tt1.last_name, tt1.first_name, tt1.email, tt1.submission_id;

person_submission_download_stats2

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views