 WHERE
   btrim(c.c) <> ''::text;
 
+CREATE MATERIALIZED VIEW private.dependency_tree AS
+WITH RECURSIVE dependency_tree AS (
+  SELECT package_name, package_name as parent, 1 as deps FROM public.packages
+  UNION
+  SELECT t.package_name, d.dependency as parent, 1 as deps FROM private.dependencies d JOIN dependency_tree t ON d.dependent = t.parent
+),
+dependent_tree AS (
+  SELECT package_name, package_name as parent, 0 as deps FROM public.packages
+  UNION
+  SELECT t.package_name, d.dependent as parent, 1 as deps FROM private.dependencies d JOIN dependent_tree t ON d.dependency = t.parent
+),
+totals AS (
+  SELECT package_name, ty.sum as all_dependencies, tt.sum as all_dependents
+  FROM
+  (SELECT package_name, sum(deps) FROM dependency_tree GROUP BY package_name) ty
+  JOIN
+  (SELECT package_name, sum(deps) FROM dependent_tree GROUP BY package_name) tt
+  USING (package_name)
+)
+SELECT
+  p.package_name,
+  p.stars,
+  p.forks,
+  p.collaborators,
+  all_dependencies,
+  all_dependents,
+  all_dependents / all_dependencies::numeric as ratio
+FROM
+  totals JOIN public.packages p USING (package_name);
+
+
 -- API exposed through PostgREST
-CREATE SCHEMA api;
-
-CREATE OR REPLACE VIEW api.packages AS
+CREATE OR REPLACE VIEW public.packages AS
 SELECT
   p.package_name,
   p.version,

  updated_at timestamp NOT NULL default current_timestamp
);

CREATE TABLE public.repos (
  package_name text PRIMARY KEY REFERENCES packages,
  stars integer NOT NULL DEFAULT 0,
  forks integer NOT NULL DEFAULT 0,

  updated_at timestamp NOT NULL default current_timestamp
);

CREATE TABLE private.dependencies (
  dependent text REFERENCES private.packages (package_name),
  dependency text REFERENCES private.packages (package_name),
  version_range text,
  created_at timestamp NOT NULL default current_timestamp,
  updated_at timestamp NOT NULL default current_timestamp,
  PRIMARY KEY (dependent, dependency)
);

CREATE TABLE private.extensions (
  package_name text REFERENCES private.packages (package_name),
  extension text,
  created_at timestamp NOT NULL default current_timestamp,
  updated_at timestamp NOT NULL default current_timestamp,
