D-Net

-- CREATE TABLE "shadow".numbers_funder AS SELECT funder, count(distinct r.id) AS pubstotal, count(distinct CASE WHEN r.bestlicense='Open Access' THEN r.id END) AS oapubs, count(distinct CASE WHEN r.bestlicense='Restricted' THEN r.id END) AS respubs, count(distinct CASE WHEN r.bestlicense='Embargo' THEN r.id END) AS embpubs, count(distinct p.id) AS projpubs, (SELECT count(distinct p2.id) FROM "shadow".project p2 WHERE p2.funder=p.funder GROUP BY p2.funder) AS projtotal FROM "shadow".project p, "shadow".project_results pr, "shadow".result r WHERE p.id=pr.id AND pr.result=r.id AND r.type='publication' GROUP BY funder ORDER BY funder;

-- CREATE TABLE "shadow".chart_funder_year AS SELECT p.funder, r.year, count(distinct r.id) FROM "shadow".result r, "shadow".project_results pr, "shadow".project p WHERE r.id=pr.result AND pr.id=p.id AND r.type='publication' AND r.year >= 2007 AND r.year <= extract(YEAR from now()) GROUP BY p.funder, r.year ORDER BY p.funder, r.year;

-- CREATE TABLE "shadow".chart_funder_license AS SELECT p.funder, r.bestlicense, count(distinct r.id) from "shadow".result r, "shadow".project_results pr, "shadow".project p WHERE r.id=pr.result AND pr.id=p.id AND r.type='publication' GROUP BY p.funder, r.bestlicense ORDER BY p.funder, r.bestlicense;

-- CREATE TABLE "shadow".chart_funder_fp7 AS SELECT p.funder, r.year, count(distinct r.id) from "shadow".result r, "shadow".project_results pr, "shadow".project p, "shadow".project_results pr2, "shadow".project p2 WHERE r.id=pr.result AND pr.id=p.id AND pr2.result=r.id AND pr2.id=p2.id AND p2.funding_lvl0='FP7' AND r.year >= 2007 AND r.year <= extract(YEAR from now()) GROUP BY p.funder, r.year ORDER BY p.funder, r.year;

-- funder nums

-- CREATE INDEX numbers_funder_funder ON "shadow".numbers_funder USING btree(funder);

-- funder charts

-- CREATE INDEX chart_funder_year_funder ON "shadow".chart_funder_year USING btree(funder);

-- CREATE INDEX chart_funder_license_funder ON "shadow".chart_funder_license USING btree(funder);