'Recursive query which aggregates child items in sqlite3

I have a table of parent-child relationships:

CREATE TABLE item_parent(item_hash, parent_hash);
CREATE UNIQUE INDEX item_parent_unique ON item_parent(item_hash, parent_hash);
CREATE VIEW item_timestamp AS SELECT item_hash, item_timestamp FROM [...]

I would like to create a view which aggregates these into the following columns:

item_hash (from the table)
child_count (recursively summarized)
newest_timestamp (joined from item_timestamp, most recent timestamp of all the items which are in the tree)

I understand I'm supposed to do something with WITH RECURSIVE, but having trouble figuring out the syntax. I've looked around SO already.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source