gistfile1.txt

select
    max(x.previous_crawl_id) as previous_crawl_id,
    x.crawl_id,
    max(x.previous_crawl_date) as previous_crawl_date,
    x.crawl_date,
    max(a.body_words_total) as previous_body_words_total,
    max(b.body_words_total) as body_words_total,
    (max(b.body_words_total) - max(a.body_words_total)) as body_words_diff,
    max(a.article_words_total) as previous_article_words_total,
    max(b.article_words_total) as article_words_total,
    (max(b.article_words_total) - max(a.article_words_total)) as article_words_diff,
    max(a.page_or_article_words_total) as previous_page_or_article_words_total,
    max(b.page_or_article_words_total) as page_or_article_words_total,
    (max(b.page_or_article_words_total) - max(a.page_or_article_words_total)) as page_or_article_words_diff,
    max(a.p_words_total) as previous_p_words_total,
    max(b.p_words_total) as p_words_total,
    (max(b.p_words_total) - max(a.p_words_total)) as p_words_diff,
    max(a.p_count_total) as previous_p_count_total,
    max(b.p_count_total) as p_count_total,
    (max(b.p_count_total) - max(a.p_count_total)) as p_count_diff,
    max(a.img_count_total) as previous_img_count_total,
    max(b.img_count_total) as img_count_total,
    (max(b.img_count_total) - max(a.img_count_total)) as img_counts_diff

from (
    select
        (select crawl_id from changes where crawl_date < c.crawl_date order by crawl_date desc limit 1) previous_crawl_id,
        crawl_id,
        (select crawl_date from changes where crawl_date < c.crawl_date order by crawl_date desc limit 1) previous_crawl_date,
        crawl_date
    from changes c
    group by crawl_id, crawl_date
    order by max(crawl_date)
) x

inner join (
    select
        crawl_id,
        sum(body_words)            body_words_total,
        sum(article_words)         article_words_total,
        sum(page_or_article_words) page_or_article_words_total,
        sum(p_words)               p_words_total,
        sum(p_count)               p_count_total,
        sum(img_count)             img_count_total
    from changes
    group by crawl_id
) b on x.crawl_id = b.crawl_id

inner join (
    select
        crawl_id,
        sum(body_words)            body_words_total,
        sum(article_words)         article_words_total,
        sum(page_or_article_words) page_or_article_words_total,
        sum(p_words)               p_words_total,
        sum(p_count)               p_count_total,
        sum(img_count)             img_count_total
    from changes
    group by crawl_id
) a on x.previous_crawl_id = a.crawl_id

where x.previous_crawl_date is not null
group by x.crawl_id, x.crawl_date
order by x.crawl_date