Combination aggregate/greatest-n-per-group SQL query on simplified data set

Question

I've been reading the top 'greatest-n-per-group' answers on StackOverflow but haven't yet found any answers that fit my specific case.

The following code works, but it seems overly-complicated for what I want to achieve. The nested/self-joined derived tables seem wrong but I don't know how to simplify things without resorting to a correlated sub-query.

Goal: For each article, I want to select the url (as determined by the page with the lowest page_number), page count and word count.

SQL Fiddle

Schema:

-- tables
CREATE TABLE article (
  id int PRIMARY KEY
);
CREATE TABLE page (
  id int PRIMARY KEY,
  url text,
  page_number int,
  word_count int,
  article_id int REFERENCES article
);

-- data
INSERT INTO article (id) VALUES (1), (2), (3);
INSERT INTO page (id, url, page_number, word_count, article_id) VALUES
  (1, '/1/1', 1, 250, 1), (2, '/1/2', 2, 230, 1), (3, '/1/3', 3, 55, 1),
  (4, '/2/1', 1, 300, 2),
  (5, '/3/1', 1, 1000, 3), (6, '/3/2', 2, 1000, 3);

Query:

SELECT
  first_page.url,
  count(*) AS page_count,
  sum(page.word_count) AS word_count,
  page.article_id
FROM
  page
  JOIN (
    SELECT
      page.url,
      page.article_id
    FROM
      page
      JOIN (
        SELECT
          min(page_number) AS page_number,
          article_id
        FROM page
        GROUP BY article_id
      ) AS first_page ON
        first_page.article_id = page.article_id AND
        first_page.page_number = page.page_number
  ) AS first_page ON first_page.article_id = page.article_id
GROUP BY
  page.article_id,
  first_page.url;

Answer 1

A common solution is based on Window Functions, as all three are based on the same window they're calculated in a single step:

SELECT url, page_count, word_count, article_id
FROM
 (
   SELECT
      url,
      article_id,
      -- both aggregations rewritten as Group Aggregates
      count(*)         over (partition by article_id) as page_count,
      sum(word_count)  over (partition by article_id) AS word_count,
      -- find the minimum page number
      page_number = 
      min(page_number) over (partition by article_id) as is_min_page
   FROM page
 ) as dt
WHERE is_min_page; -- return minimum page only

See fiddle