select count(id) as SELECT
sq."Total Cases", COALESCE(sum(CASELast WHEN7 "successful" THEN 1 ELSE 0 END),0) as "Successful Rescues", ROUND(CAST(COALESCE(sum(CASE WHEN "successful" THEN 1 ELSE 0 END),0) as decimal)/count(id) *100,2) as "Success Rate (%)" from "Rescues" where "platform" != 'unknown' and position(': false' in "data"::json#>>'{markedForDeletion}')>0 ANDdays)",
sq."Rescues (Last 7 days)",
sq."Success % (Last 7 days)",
sq."Total Cases (Last 24 hours)",
sq."Rescues (Last 24 hours)",
sq."Success % (Last 24 hours)"
FROM
(
SELECT
COALESCE(sum(CASE WHEN "createdAt" BETWEEN LOCALTIMESTAMP - INTERVAL '7 days' AND LOCALTIMESTAMP;
select count(idTHEN 1 ELSE 0 END),0) as "Total Cases (Last CR7 Casesdays)",
COALESCE(sum(CASE WHEN "successful" and "createdAt" BETWEEN LOCALTIMESTAMP - INTERVAL '7 days' AND LOCALTIMESTAMP THEN 1 ELSE 0 END),0) as "Successful CR Rescues", Rescues (Last 7 days)",
ROUND(CAST(COALESCE(sum(CASE WHEN "successful" and "createdAt" BETWEEN LOCALTIMESTAMP - INTERVAL '7 days' AND LOCALTIMESTAMP THEN 1 ELSE 0 END),0) as decimal)/count(id) *100,2) as "CR Success Rate (%)" from "Rescues" where "codeRed" = TRUE AND "platform" != 'unknown' and position(': false' in "data"::json#>>'{markedForDeletion}')>0 AND "createdAt" BETWEENCOALESCE(sum(CASE WHEN "createdAt" BETWEEN LOCALTIMESTAMP - INTERVAL '7 days' AND LOCALTIMESTAMP THEN 1 ELSE 0 END),0) *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 "successful" and "createdAt" BETWEEN LOCALTIMESTAMP - INTERVAL '24 hours' AND LOCALTIMESTAMP THEN 1 ELSE 0 END),0) as "Rescues (Last 24 hours)",
ROUND(CAST(COALESCE(sum(CASE WHEN "successful" and "createdAt" BETWEEN LOCALTIMESTAMP - INTERVAL '24 hours' AND LOCALTIMESTAMP THEN 1 ELSE 0 END),0) as decimal)/COALESCE(sum(CASE WHEN "createdAt" BETWEEN LOCALTIMESTAMP - INTERVAL '24 hours' AND LOCALTIMESTAMP THEN 1 ELSE 0 END),0) *100,2) as "Success % (Last 24 hours)"
FROM
public."Rescues"
WHERE
"deletedAt" IS NULL and "platform" != 'unknown' and position(': false' in "data"::json#>>'{markedForDeletion}')>0
) sq;
SELECT
sq."Total CR Cases (Last 7 days)",
sq."CR Rescues (Last 7 days)",
sq."CR Success % (Last 7 days)",
sq."Total CR Cases (Last 24 hours)",
sq."CR 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 "successful" and "createdAt" BETWEEN LOCALTIMESTAMP - INTERVAL '7 days' AND LOCALTIMESTAMP THEN 1 ELSE 0 END),0) as "CR Rescues (Last 7 days)",
ROUND(CAST(COALESCE(sum(CASE WHEN "successful" and "createdAt" BETWEEN LOCALTIMESTAMP - INTERVAL '7 days' AND LOCALTIMESTAMP THEN 1 ELSE 0 END),0) as decimal)/COALESCE(sum(CASE WHEN "createdAt" BETWEEN LOCALTIMESTAMP - INTERVAL '7 days' AND LOCALTIMESTAMP THEN 1 ELSE 0 END),0) *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 "successful" and "createdAt" BETWEEN LOCALTIMESTAMP - INTERVAL '24 hours' AND LOCALTIMESTAMP THEN 1 ELSE 0 END),0) as "CR Rescues (Last 24 hours)",
ROUND(CAST(COALESCE(sum(CASE WHEN "successful" and "createdAt" BETWEEN LOCALTIMESTAMP - INTERVAL '24 hours' AND LOCALTIMESTAMP THEN 1 ELSE 0 END),0) as decimal)/COALESCE(sum(CASE WHEN "createdAt" BETWEEN LOCALTIMESTAMP - INTERVAL '24 hours' AND LOCALTIMESTAMP THEN 1 ELSE 0 END),0) *100,2) as "CR Success % (Last 24 hours)"
FROM
public."Rescues"
WHERE
"codeRed" = TRUE 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' ANDand LOCALTIMESTAMP;
select count(distinct("firstLimpetId")) as "Rats with a First Limpet (Last 24 hours)" from "Rescues" where "createdAt" between LOCALTIMESTAMP - INTERVAL '724 dayshours' and LOCALTIMESTAMP; |