SELECT
sq."Total Cases (Last 7 days)",
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 THEN 1 ELSE 0 END),0) as "Total 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 "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 "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
sq."Total PC Cases (Last 7 days)",
sq."Total PC Cases (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 PC Cases (Last 7 days)",
COALESCE(sum(CASE WHEN "createdAt" BETWEEN LOCALTIMESTAMP - INTERVAL '24 hours' AND LOCALTIMESTAMP THEN 1 ELSE 0 END),0) as "Total PC Cases (Last 24 hours)"
FROM
public."Rescues"
WHERE
"platform" = 'pc' and "deletedAt" IS NULL and "platform" != 'unknown' and position(': false' in "data"::json#>>'{markedForDeletion}')>0
) sq;
SELECT
sq."Total XB Cases (Last 7 days)",
sq."Total XB Cases (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 XB Cases (Last 7 days)",
COALESCE(sum(CASE WHEN "createdAt" BETWEEN LOCALTIMESTAMP - INTERVAL '24 hours' AND LOCALTIMESTAMP THEN 1 ELSE 0 END),0) as "Total XB Cases (Last 24 hours)"
FROM
public."Rescues"
WHERE
"platform" = 'xb' 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; |