WITH first_release (vid, released)
AS (
	SELECT v.id, MIN(r.released)
	FROM vn v, releases r, releases_vn rv
	WHERE r.id = rv.id
		AND v.id = rv.vid
		AND r.released > 0
		AND r.released < 99999999
	GROUP BY v.id
	ORDER BY MIN(r.released)
	)
SELECT v.id
FROM first_release fr, releases r, vn v, releases_vn rv, releases_lang rl
WHERE r.released = fr.released
	AND v.id = fr.vid
	AND r.id = rv.id
	AND rl.lang = 'ja'
	AND rv.vid = fr.vid
GROUP BY v.id

WITH RECURSIVE descendant_tags (gid)
AS (
	SELECT tp.tag
	FROM tags_parents tp
	WHERE tp.parent = '2'
	GROUP BY tp.tag
	
	UNION ALL
	
	SELECT tp.tag
	FROM tags_parents tp, descendant_tags dt
	WHERE tp.parent = dt.gid
	GROUP BY tp.tag
	)
SELECT *
FROM descendant_tags

SELECT title
  FROM vn v
 WHERE v.id IN(
      SELECT rv.vid
        FROM releases_lang rl
        JOIN releases_vn rv ON rv.id = rl.id
        JOIN releases r ON r.id = rl.id
       WHERE lang = 'ja' -- The original language you want
         AND NOT EXISTS(
            SELECT 1 FROM releases_vn irv JOIN releases ir ON ir.id = irv.id
             WHERE irv.vid = rv.vid AND ir.released < r.released))

CREATE OR REPLACE FUNCTION get_descendant_tags (tag_id INTEGER)
RETURNS TABLE (gid INTEGER) AS $$
WITH RECURSIVE descendant_tags(gid) AS (
		SELECT tp.tag
		FROM tags_parents tp
		WHERE tp.parent = tag_id
		GROUP BY tp.tag
		
		UNION ALL
		
		SELECT tp.tag
		FROM tags_parents tp, descendant_tags dt
		WHERE tp.parent = dt.gid
		GROUP BY tp.tag
		)

SELECT gid
FROM descendant_tags $$ 
LANGUAGE sql;

SELECT first_release.year, AVG(vote_avg) AS avg_rating, AVG(vote_cnt) AS avg_votes, COUNT(v.id) AS novels FROM 
vn v INNER JOIN
(
  SELECT rv.vid, MIN(r.released) as released, MIN(r.released) / 10000 as year
	FROM releases_vn rv
	INNER JOIN releases r on r.id = rv.id
	GROUP BY rv.vid
) first_release ON v.id = first_release.vid
INNER JOIN 
(
  SELECT vo.vid, avg(vo.vote) AS vote_avg, count(vo.vote) AS vote_cnt
    FROM votes vo
    GROUP BY vo.vid
    HAVING count(vo.vote) > 150
) votes
ON votes.vid = v.id
GROUP BY first_release.year
ORDER BY first_release.year

CREATE OR REPLACE FUNCTION get_child_traits(trait_id INTEGER) RETURNS TABLE(trait INTEGER) AS $$
WITH RECURSIVE child_traits(trait) AS (
  SELECT tp.trait FROM traits_parents tp WHERE tp.parent = trait_id
  UNION ALL
  SELECT tp.trait FROM traits_parents tp, child_traits ct WHERE tp.parent = ct.trait GROUP BY tp.trait
) SELECT trait FROM child_traits
$$ LANGUAGE sql;