View Source

Please note that this page is a work in progress and may be non-functioning or inaccurate as a result of system upgrades and maintenance.

select 
	coalesce(sum(case when "outcome"='success' then 1 else 0 end),0) as "Total Successful Rescues",
	coalesce(sum(case when "outcome"='failure' then 1 else 0 end),0) as "Total Failed Rescues",
	ROUND(
			COUNT(CASE WHEN "outcome" = 'success' THEN 1.0 ELSE NULL END)	/ 
			CAST( COUNT(CASE WHEN "outcome" = 'success' OR "outcome" = 'failure' THEN 1.0 ELSE NULL END) AS NUMERIC) * 100, 1)
			AS "Success %"
from public."Rescues"
where "deletedAt" IS NULL and position(': false' in "data"::json#>>'{markedForDeletion}')>0 ;

select platform, COALESCE(sum(case when "outcome"='failure' then 1 else 0 end),0) as "Failed", COALESCE(sum(CASE WHEN "outcome"='success' then 1 else 0 end),0) as "Successful" from "Rescues" where "deletedAt" IS NULL and position(': false' in "data"::json#>>'{markedForDeletion}')>0 and "createdAt" between LOCALTIMESTAMP - INTERVAL '24 hours' and LOCALTIMESTAMP GROUP BY platform;

select platform, count(id) from "Rescues" where "deletedAt" IS NULL and position(': false' in "data"::json#>>'{markedForDeletion}')>0 and "createdAt" between LOCALTIMESTAMP - INTERVAL '24 hours' and LOCALTIMESTAMP and platform != 'unknown' GROUP BY platform;

Note: This chart includes active cases, but excludes unfiled paperwork, so the values may vary from the graph to the left.

select platform, COALESCE(sum(case when "outcome"='failure' then 1 else 0 end),0) as "Failed", COALESCE(sum(CASE WHEN "outcome"='success' then 1 else 0 end),0) as "Successful" from "Rescues" where "deletedAt" IS NULL and position(': false' in "data"::json#>>'{markedForDeletion}')>0 and "createdAt" between LOCALTIMESTAMP - INTERVAL '7 days' and LOCALTIMESTAMP GROUP BY platform;

select platform, count(id) from "Rescues" where "deletedAt" IS NULL and position(': false' in "data"::json#>>'{markedForDeletion}')>0 and "createdAt" between LOCALTIMESTAMP - INTERVAL '7 days' and LOCALTIMESTAMP GROUP BY platform;

Note: This chart includes active cases, but excludes unfiled paperwork, so the values may vary from the graph to the left.

SELECT
	to_char(date_trunc('week', "createdAt"), 'YYYY-MM-DD') AS "Week",
	round(COUNT(CASE WHEN "outcome" = 'success' THEN 1.0 ELSE NULL END) / CAST (COUNT(CASE WHEN "outcome" = 'success' OR "outcome" = 'failure' THEN 1.0 ELSE NULL END) AS NUMERIC) * 100, 2) AS "Success Rate"
FROM "Rescues"
GROUP BY "Week"
ORDER BY "Week" ASC

SELECT system as "System", COUNT(system) as "# of Rescues" FROM "Rescues" WHERE position(': false' in "data"::json#>>'{markedForDeletion}')>0 and "createdAt" BETWEEN LOCALTIMESTAMP - INTERVAL '7 days' AND LOCALTIMESTAMP GROUP BY system ORDER BY count(system) DESC LIMIT 10;

SELECT
	sq."Total Cases (Last 7 days)",
	sq."Success Rescues (Last 7 days)",
	sq."Success % (Last 7 days)",
	sq."Total Cases (Last 24 hours)",
	sq."Success Rescues (Last 24 hours)",
	sq."Success % (Last 24 hours)"
FROM
	(
		SELECT
			COALESCE(sum(CASE WHEN "createdAt" BETWEEN LOCALTIMESTAMP - INTERVAL '7 days' AND LOCALTIMESTAMP THEN 1 ELSE 0 END),0) as "Total Cases (Last 7 days)",
			COALESCE(sum(CASE WHEN "outcome"='success' and "createdAt" BETWEEN LOCALTIMESTAMP - INTERVAL '7 days' AND LOCALTIMESTAMP THEN 1 ELSE 0 END),0) as "Success Rescues (Last 7 days)",

			ROUND(
			COUNT(CASE WHEN "outcome" = 'success'
				AND "createdAt" BETWEEN LOCALTIMESTAMP - INTERVAL '7 days' AND LOCALTIMESTAMP
				THEN 1.0 ELSE NULL END)	/ 
			CAST( COUNT(CASE WHEN ("outcome" = 'success' OR "outcome" = 'failure')
				AND "createdAt" BETWEEN LOCALTIMESTAMP - INTERVAL '7 days' AND LOCALTIMESTAMP
				THEN 1.0 ELSE NULL END) AS NUMERIC) * 100, 2)
			AS "Success % (Last 7 days)",

COALESCE(sum(CASE WHEN "createdAt" BETWEEN LOCALTIMESTAMP - INTERVAL '24 hours' AND LOCALTIMESTAMP THEN 1 ELSE 0 END),0) as "Total Cases (Last 24 hours)",
			COALESCE(sum(CASE WHEN "outcome"='success' and "createdAt" BETWEEN LOCALTIMESTAMP - INTERVAL '24 hours' AND LOCALTIMESTAMP THEN 1 ELSE 0 END),0) as "Success Rescues (Last 24 hours)",


			ROUND(
			COUNT(CASE WHEN "outcome" = 'success'
				AND "createdAt" BETWEEN LOCALTIMESTAMP - INTERVAL '24 hours' AND LOCALTIMESTAMP
				THEN 1.0 ELSE NULL END)	/ 
			CAST( COUNT(CASE WHEN ("outcome" = 'success' OR "outcome" = 'failure')
				AND "createdAt" BETWEEN LOCALTIMESTAMP - INTERVAL '24 hours' AND LOCALTIMESTAMP
				THEN 1.0 ELSE NULL END) AS NUMERIC) * 100, 2)
			AS "Success % (Last 24 hours)"

		FROM
			public."Rescues" 
		WHERE
			"outcome" != 'invalid' AND "outcome" != 'other' AND "deletedAt" IS NULL and "platform" != 'unknown' and position(': false' in "data"::json#>>'{markedForDeletion}')>0 
	) sq;
 
select count(distinct("firstLimpetId")) as "Rats with a First Limpet (Last 7 Days)" from "Rescues" where "createdAt" between LOCALTIMESTAMP - INTERVAL '7 days' and LOCALTIMESTAMP;

select count(distinct("firstLimpetId")) as "Rats with a First Limpet (Last 24 hours)" from "Rescues" where "createdAt" between LOCALTIMESTAMP - INTERVAL '24 hours' and LOCALTIMESTAMP;

Total Rescues by Platform

select platform, COALESCE(sum(case when "deletedAt" IS NULL then 1 else 0 end),0) as "Cases" from "Rescues" where position(': false' in "data"::json#>>'{markedForDeletion}')>0 GROUP BY platform ORDER BY platform;

SELECT
	sq."Total CR Cases (Last 7 days)",
	sq."CR Success Rescues (Last 7 days)",
	sq."CR Success % (Last 7 days)",
	sq."Total CR Cases (Last 24 hours)",
	sq."CR Success Rescues (Last 24 hours)",
	sq."CR Success % (Last 24 hours)"
FROM
	(
		SELECT
			COALESCE(sum(CASE WHEN "createdAt" BETWEEN LOCALTIMESTAMP - INTERVAL '7 days' AND LOCALTIMESTAMP THEN 1 ELSE 0 END),0) as "Total CR Cases (Last 7 days)",
			COALESCE(sum(CASE WHEN "outcome"='success' and "createdAt" BETWEEN LOCALTIMESTAMP - INTERVAL '7 days' AND LOCALTIMESTAMP THEN 1 ELSE 0 END),0) as "CR Success Rescues (Last 7 days)",


			ROUND(
			COUNT(CASE WHEN "outcome" = 'success'
				AND "createdAt" BETWEEN LOCALTIMESTAMP - INTERVAL '7 days' AND LOCALTIMESTAMP
				THEN 1.0 ELSE NULL END)	/ 
			CAST( COUNT(CASE WHEN ("outcome" = 'success' OR "outcome" = 'failure')
				AND "createdAt" BETWEEN LOCALTIMESTAMP - INTERVAL '7 days' AND LOCALTIMESTAMP
				THEN 1.0 ELSE NULL END) AS NUMERIC) * 100, 2)
			AS "CR Success % (Last 7 days)",

			COALESCE(sum(CASE WHEN "createdAt" BETWEEN LOCALTIMESTAMP - INTERVAL '24 hours' AND LOCALTIMESTAMP THEN 1 ELSE 0 END),0) as "Total CR Cases (Last 24 hours)",
			COALESCE(sum(CASE WHEN "outcome"='success' and "createdAt" BETWEEN LOCALTIMESTAMP - INTERVAL '24 hours' AND LOCALTIMESTAMP THEN 1 ELSE 0 END),0) as "CR Success Rescues (Last 24 hours)",


			ROUND(
			COUNT(CASE WHEN "outcome" = 'success'
				AND "createdAt" BETWEEN LOCALTIMESTAMP - INTERVAL '24 hours' AND LOCALTIMESTAMP
				THEN 1.0 ELSE NULL END)	/ 
			CAST( COUNT(CASE WHEN ("outcome" = 'success' OR "outcome" = 'failure')
				AND "createdAt" BETWEEN LOCALTIMESTAMP - INTERVAL '24 hours' AND LOCALTIMESTAMP
				THEN 1.0 ELSE NULL END) AS NUMERIC) * 100, 2)
			AS "CR Success % (Last 24 hours)"

		FROM
			public."Rescues" 
		WHERE
			"outcome" != 'invalid' AND "outcome" != 'other' AND "codeRed" = TRUE and "deletedAt" IS NULL and "platform" != 'unknown' and position(': false' in "data"::json#>>'{markedForDeletion}')>0 
	) sq;

Registered Rats by Platform

SELECT platform, count(id) as Rats from "Rats" WHERE "deletedAt" IS NULL GROUP BY platform ORDER BY Rats DESC;

SELECT EXTRACT(hour from "createdAt") as hour, COALESCE(sum(CASE WHEN platform = 'pc' then 1 else 0 end),0) as PC, COALESCE(sum(CASE WHEN platform = 'xb' then 1 else 0 end),0) as XB, COALESCE(sum(CASE WHEN platform = 'ps' then 1 else 0 end),0) as PS FROM "Rescues"  WHERE EXTRACT(year from "createdAt")=2017 GROUP BY hour ORDER BY hour

select "data"::json#>>'{langID}' as "Language", count("data"::json#>>'{langID}') as "Cases" from "Rescues" where position(': false' in "data"::json#>>'{markedForDeletion}')>0 group by "data"::json#>>'{langID}' order by count("data"::json#>>'{langID}') desc;