Skip to content

Instantly share code, notes, and snippets.

@jimpick
Last active March 17, 2022 18:55
Show Gist options
  • Save jimpick/558225e73a3cfdd92b4fead07e644243 to your computer and use it in GitHub Desktop.
Save jimpick/558225e73a3cfdd92b4fead07e644243 to your computer and use it in GitHub Desktop.
SQL queries for Provider Power by Quarter
CREATE TABLE synthetic_regions (
provider VARCHAR(255),
region VARCHAR(255),
"numRegions" INTEGER,
delegate VARCHAR(255));
CREATE INDEX IF NOT EXISTS provider_power_daily_date_idx ON provider_power_daily (date);
CREATE INDEX IF NOT EXISTS synthetic_regions_provider_idx ON synthetic_regions (provider);
CREATE INDEX IF NOT EXISTS synthetic_regions_region_idx ON synthetic_regions (region);
CREATE INDEX IF NOT EXISTS synthetic_regions_delegate_idx ON synthetic_regions (delegate);
DROP MATERIALIZED VIEW IF EXISTS monthly_percentages;
DROP MATERIALIZED VIEW IF EXISTS monthly_growth;
DROP MATERIALIZED VIEW IF EXISTS month_over_month_matrix;
DROP MATERIALIZED VIEW IF EXISTS month_over_month;
DROP MATERIALIZED VIEW IF EXISTS filled_power_by_month;
DROP MATERIALIZED VIEW IF EXISTS power_by_month;
CREATE MATERIALIZED VIEW power_by_month AS
SELECT
provider,
CAST(SUBSTRING(provider FROM 2) AS INTEGER) AS "providerNum",
date,
"avg(rawBytePower)" / pow(1024, 5) AS "rawPiB",
"avg(qualityAdjPower)" / pow(1024, 5) AS "qualityPiB",
CASE
WHEN "avg(qualityAdjPower)" / pow(1024, 5) < 0.1 THEN 'a. < 0.1 PiB'
WHEN "avg(qualityAdjPower)" / pow(1024, 5) < 1 THEN 'b. 0.1 to 1 PiB'
WHEN "avg(qualityAdjPower)" / pow(1024, 5) < 10 THEN 'c. 1 to 10 PiB'
WHEN "avg(qualityAdjPower)" / pow(1024, 5) < 50 THEN 'd. 10 to 50 PiB'
ELSE 'e. > 50PiB'
END as "qualityBucket"
FROM public.provider_power_daily
WHERE EXTRACT(DAY FROM date) = 1
ORDER BY "providerNum", date;
CREATE MATERIALIZED VIEW filled_power_by_month AS
SELECT provider_dates.*,
COALESCE (power_by_month."rawPiB", 0) as "rawPiB",
COALESCE (power_by_month."qualityPiB", 0) as "qualityPiB",
COALESCE (power_by_month."qualityBucket", 'a. < 0.1 PiB') as "qualityBucket"
FROM
(
SELECT * FROM
(SELECT DISTINCT provider, "providerNum"
FROM power_by_month) AS providers,
(SELECT DISTINCT date
FROM power_by_month
UNION
SELECT * from (VALUES ('2020-07-01'::date)) as start_date (date)) AS dates
ORDER BY "providerNum", date
) as provider_dates
LEFT JOIN power_by_month
ON power_by_month.provider = provider_dates.provider
AND power_by_month.date = provider_dates.date
ORDER BY "providerNum", date;
CREATE MATERIALIZED VIEW month_over_month AS
SELECT *,
CASE
WHEN new = TRUE THEN 'New'
WHEN "prevQualityPiB" = 0 and "qualityPiB" = 0 THEN NULL
WHEN "qualityGrowthRate" > 10 THEN '1. >1000%'
WHEN "qualityGrowthRate" > 1 THEN '2. 100-1000%'
WHEN "qualityGrowthRate" > 0.5 THEN '3. 50-100%'
WHEN "qualityGrowthRate" > 0.25 THEN '4. 25-50%'
WHEN "qualityGrowthRate" > 0 THEN '5. 0-25%'
WHEN "qualityGrowthRate" > -0.01 THEN '6. ~0%'
WHEN "qualityGrowthRate" > -0.25 THEN '7. -0%-25%'
WHEN "qualityGrowthRate" > -0.50 THEN '8. -25%-50%'
WHEN "qualityGrowthRate" > -1 THEN '9. -50%-100%'
ELSE '91. <-100%'
END AS "growthRange"
FROM
(SELECT target.*,
(target.date - INTERVAL '1 month')::DATE AS "previousDate",
prev."rawPiB" as "prevRawPiB",
target."rawPiB" - prev."rawPiB" as "diffRawPiB",
prev."qualityPiB" as "prevQualityPiB",
target."qualityPiB" - prev."qualityPiB" as "diffQualityPiB",
(target."qualityPiB" - prev."qualityPiB") / NULLIF(prev."qualityPiB", 0) as "qualityGrowthRate",
CASE
WHEN prev."qualityPiB" = 0 AND target."qualityPiB" > 0 then TRUE
ELSE FALSE
END AS "new"
FROM filled_power_by_month target
INNER JOIN filled_power_by_month prev
ON (target.date - INTERVAL '1 month')::DATE = prev.date AND
target.provider = prev.provider
) AS data
ORDER BY "providerNum", date;
CREATE MATERIALIZED VIEW month_over_month_matrix AS
SELECT "date",
"previousDate",
"qualityBucket",
"growthRange",
COUNT(*),
SUM("diffQualityPiB") AS "qualityPowerDiffPiB"
FROM month_over_month
WHERE "growthRange" IS NOT NULL
GROUP BY "date", "previousDate", "qualityBucket", "growthRange"
ORDER BY "date", "qualityBucket", "growthRange";
CREATE MATERIALIZED VIEW monthly_growth AS
SELECT target."qualityBucket",
target."date",
target."previousDate",
SUM(target."qualityPowerDiffPiB") AS "qualityPowerGrowth",
COALESCE(SUM(previous.count), 0) AS "previousCount",
SUM(target.count) AS count,
SUM(target.count) - COALESCE(SUM(previous.count), 0) AS "countGrowth"
FROM month_over_month_matrix AS target
LEFT JOIN month_over_month_matrix AS previous
ON target."previousDate" = previous.date
AND target."qualityBucket" = previous."qualityBucket"
AND target."growthRange" = previous."growthRange"
GROUP BY target."date", target."previousDate", target."qualityBucket"
ORDER BY "qualityBucket", "date";
CREATE MATERIALIZED VIEW monthly_percentages AS
SELECT
month_over_month_matrix.date,
month_over_month_matrix."qualityBucket",
"growthRange",
count,
total,
count / total * 100 AS percentage
FROM month_over_month_matrix
INNER JOIN (
SELECT date, "qualityBucket", SUM(count) AS total FROM month_over_month_matrix
GROUP by date, "qualityBucket"
ORDER BY date, "qualityBucket"
) AS totals
ON month_over_month_matrix.date = totals.date
AND month_over_month_matrix."qualityBucket" = totals."qualityBucket";
SELECT * FROM monthly_percentages;
DROP TABLE IF EXISTS monthly_sample_dates;
CREATE TABLE monthly_sample_dates AS
SELECT DISTINCT date
FROM month_over_month_matrix
ORDER BY date;
SELECT * FROM monthly_sample_dates;
DROP MATERIALIZED VIEW IF EXISTS quarterly_percentages;
DROP MATERIALIZED VIEW IF EXISTS quarterly_growth;
DROP MATERIALIZED VIEW IF EXISTS quarter_over_quarter_matrix;
DROP MATERIALIZED VIEW IF EXISTS quarter_over_quarter;
DROP MATERIALIZED VIEW IF EXISTS filled_power_by_quarter;
DROP MATERIALIZED VIEW IF EXISTS power_by_quarter;
CREATE MATERIALIZED VIEW power_by_quarter AS
SELECT
provider,
CAST(SUBSTRING(provider FROM 2) AS INTEGER) AS "providerNum",
date,
"avg(rawBytePower)" / pow(1024, 5) AS "rawPiB",
"avg(qualityAdjPower)" / pow(1024, 5) AS "qualityPiB",
CASE
WHEN "avg(qualityAdjPower)" / pow(1024, 5) < 0.1 THEN 'a. < 0.1 PiB'
WHEN "avg(qualityAdjPower)" / pow(1024, 5) < 1 THEN 'b. 0.1 to 1 PiB'
WHEN "avg(qualityAdjPower)" / pow(1024, 5) < 10 THEN 'c. 1 to 10 PiB'
WHEN "avg(qualityAdjPower)" / pow(1024, 5) < 50 THEN 'd. 10 to 50 PiB'
ELSE 'e. > 50PiB'
END as "qualityBucket"
FROM public.provider_power_daily
WHERE EXTRACT(DAY FROM date) = 1 AND
EXTRACT(MONTH FROM date) = ANY(ARRAY[1, 4, 7, 10])
ORDER BY "providerNum", date;
CREATE MATERIALIZED VIEW filled_power_by_quarter AS
SELECT provider_dates.*,
COALESCE (power_by_quarter."rawPiB", 0) as "rawPiB",
COALESCE (power_by_quarter."qualityPiB", 0) as "qualityPiB",
COALESCE (power_by_quarter."qualityBucket", 'a. < 0.1 PiB') as "qualityBucket"
FROM
(
SELECT * FROM
(SELECT DISTINCT provider, "providerNum"
FROM power_by_quarter) AS providers,
(SELECT DISTINCT date
FROM power_by_quarter
UNION
SELECT * from (VALUES ('2020-07-01'::date)) as start_date (date)) AS dates
ORDER BY "providerNum", date
) as provider_dates
LEFT JOIN power_by_quarter
ON power_by_quarter.provider = provider_dates.provider
AND power_by_quarter.date = provider_dates.date;
CREATE MATERIALIZED VIEW quarter_over_quarter AS
SELECT *,
EXTRACT(YEAR FROM (date - INTERVAL '1 DAY')) || '_Q' || EXTRACT(QUARTER FROM (date - INTERVAL '1 DAY')) AS "yearQuarter",
CASE
WHEN new = TRUE THEN 'New'
WHEN "prevQualityPiB" = 0 and "qualityPiB" = 0 THEN NULL
WHEN "qualityGrowthRate" > 10 THEN '1. >1000%'
WHEN "qualityGrowthRate" > 1 THEN '2. 100-1000%'
WHEN "qualityGrowthRate" > 0.5 THEN '3. 50-100%'
WHEN "qualityGrowthRate" > 0.25 THEN '4. 25-50%'
WHEN "qualityGrowthRate" > 0 THEN '5. 0-25%'
WHEN "qualityGrowthRate" > -0.01 THEN '6. ~0%'
WHEN "qualityGrowthRate" > -0.25 THEN '7. -0%-25%'
WHEN "qualityGrowthRate" > -0.50 THEN '8. -25%-50%'
WHEN "qualityGrowthRate" > -1 THEN '9. -50%-100%'
ELSE '91. <-100%'
END AS "growthRange"
FROM
(SELECT target.*,
(target.date - INTERVAL '3 months')::DATE AS "previousDate",
prev."rawPiB" as "prevRawPiB",
target."rawPiB" - prev."rawPiB" as "diffRawPiB",
prev."qualityPiB" as "prevQualityPiB",
target."qualityPiB" - prev."qualityPiB" as "diffQualityPiB",
(target."qualityPiB" - prev."qualityPiB") / NULLIF(prev."qualityPiB", 0) as "qualityGrowthRate",
CASE
WHEN prev."qualityPiB" = 0 AND target."qualityPiB" > 0 then TRUE
ELSE FALSE
END AS "new"
FROM filled_power_by_quarter target
INNER JOIN filled_power_by_quarter prev
ON (target.date - INTERVAL '3 months')::DATE = prev.date AND
target.provider = prev.provider
) AS data
ORDER BY "providerNum", date;
CREATE MATERIALIZED VIEW quarter_over_quarter_matrix AS
SELECT "yearQuarter",
"date",
"previousDate",
"qualityBucket",
"growthRange",
COUNT(*),
SUM("diffQualityPiB") AS "qualityPowerDiffPiB"
FROM quarter_over_quarter
WHERE "growthRange" IS NOT NULL
GROUP BY "yearQuarter", "date", "previousDate", "qualityBucket", "growthRange"
ORDER BY "yearQuarter", "qualityBucket", "growthRange";
CREATE MATERIALIZED VIEW quarterly_growth AS
SELECT target."qualityBucket",
target."yearQuarter",
target."date",
target."previousDate",
SUM(target."qualityPowerDiffPiB") AS "qualityPowerGrowth",
COALESCE(SUM(previous.count), 0) AS "previousCount",
SUM(target.count) AS count,
SUM(target.count) - COALESCE(SUM(previous.count), 0) AS "countGrowth"
FROM quarter_over_quarter_matrix AS target
LEFT JOIN quarter_over_quarter_matrix AS previous
ON target."previousDate" = previous.date
AND target."qualityBucket" = previous."qualityBucket"
AND target."growthRange" = previous."growthRange"
GROUP BY target."yearQuarter", target."date", target."previousDate", target."qualityBucket"
ORDER BY "qualityBucket", "yearQuarter";
CREATE MATERIALIZED VIEW quarterly_percentages AS
SELECT
quarter_over_quarter_matrix.date,
quarter_over_quarter_matrix."yearQuarter",
quarter_over_quarter_matrix."qualityBucket",
"growthRange",
count,
total,
count / total * 100 AS percentage
FROM quarter_over_quarter_matrix
INNER JOIN (
SELECT date, "qualityBucket", SUM(count) AS total FROM quarter_over_quarter_matrix
GROUP by date, "qualityBucket"
ORDER BY date, "qualityBucket"
) AS totals
ON quarter_over_quarter_matrix.date = totals.date
AND quarter_over_quarter_matrix."qualityBucket" = totals."qualityBucket";
SELECT * FROM quarterly_percentages;
REFRESH MATERIALIZED VIEW power_by_week;
REFRESH MATERIALIZED VIEW filled_power_by_week;
REFRESH MATERIALIZED VIEW week_over_week;
REFRESH MATERIALIZED VIEW week_over_week_matrix;
REFRESH MATERIALIZED VIEW weekly_growth;
REFRESH MATERIALIZED VIEW weekly_percentages;
REFRESH MATERIALIZED VIEW power_by_month;
REFRESH MATERIALIZED VIEW filled_power_by_month;
REFRESH MATERIALIZED VIEW month_over_month;
REFRESH MATERIALIZED VIEW month_over_month_matrix;
REFRESH MATERIALIZED VIEW monthly_growth;
REFRESH MATERIALIZED VIEW monthly_percentages;
REFRESH MATERIALIZED VIEW power_by_quarter;
REFRESH MATERIALIZED VIEW filled_power_by_quarter;
REFRESH MATERIALIZED VIEW quarter_over_quarter;
REFRESH MATERIALIZED VIEW quarter_over_quarter_matrix;
REFRESH MATERIALIZED VIEW quarterly_growth;
REFRESH MATERIALIZED VIEW quarterly_percentages;
DROP MATERIALIZED VIEW IF EXISTS weekly_percentages;
DROP MATERIALIZED VIEW IF EXISTS weekly_growth;
DROP MATERIALIZED VIEW IF EXISTS week_over_week_matrix;
DROP MATERIALIZED VIEW IF EXISTS week_over_week;
DROP MATERIALIZED VIEW IF EXISTS filled_power_by_week;
DROP MATERIALIZED VIEW IF EXISTS power_by_week;
CREATE MATERIALIZED VIEW power_by_week AS
SELECT
provider,
CAST(SUBSTRING(provider FROM 2) AS INTEGER) AS "providerNum",
date,
"avg(rawBytePower)" / pow(1024, 5) AS "rawPiB",
"avg(qualityAdjPower)" / pow(1024, 5) AS "qualityPiB",
CASE
WHEN "avg(qualityAdjPower)" / pow(1024, 5) < 0.1 THEN 'a. < 0.1 PiB'
WHEN "avg(qualityAdjPower)" / pow(1024, 5) < 1 THEN 'b. 0.1 to 1 PiB'
WHEN "avg(qualityAdjPower)" / pow(1024, 5) < 10 THEN 'c. 1 to 10 PiB'
WHEN "avg(qualityAdjPower)" / pow(1024, 5) < 50 THEN 'd. 10 to 50 PiB'
ELSE 'e. > 50PiB'
END as "qualityBucket"
FROM public.provider_power_daily
WHERE EXTRACT(DOW FROM date) = 0
ORDER BY "providerNum", date;
CREATE MATERIALIZED VIEW filled_power_by_week AS
SELECT provider_dates.*,
COALESCE (power_by_week."rawPiB", 0) as "rawPiB",
COALESCE (power_by_week."qualityPiB", 0) as "qualityPiB",
COALESCE (power_by_week."qualityBucket", 'a. < 0.1 PiB') as "qualityBucket"
FROM
(
SELECT * FROM
(SELECT DISTINCT provider, "providerNum"
FROM power_by_week) AS providers,
(SELECT DISTINCT date
FROM power_by_week
UNION
SELECT * from (VALUES ('2020-08-23'::date)) as start_date (date)) AS dates
ORDER BY "providerNum", date
) as provider_dates
LEFT JOIN power_by_week
ON power_by_week.provider = provider_dates.provider
AND power_by_week.date = provider_dates.date
ORDER BY "providerNum", date;
CREATE MATERIALIZED VIEW week_over_week AS
SELECT *,
CASE
WHEN new = TRUE THEN 'New'
WHEN "prevQualityPiB" = 0 and "qualityPiB" = 0 THEN NULL
WHEN "qualityGrowthRate" > 10 THEN '1. >1000%'
WHEN "qualityGrowthRate" > 1 THEN '2. 100-1000%'
WHEN "qualityGrowthRate" > 0.5 THEN '3. 50-100%'
WHEN "qualityGrowthRate" > 0.25 THEN '4. 25-50%'
WHEN "qualityGrowthRate" > 0 THEN '5. 0-25%'
WHEN "qualityGrowthRate" > -0.01 THEN '6. ~0%'
WHEN "qualityGrowthRate" > -0.25 THEN '7. -0%-25%'
WHEN "qualityGrowthRate" > -0.50 THEN '8. -25%-50%'
WHEN "qualityGrowthRate" > -1 THEN '9. -50%-100%'
ELSE '91. <-100%'
END AS "growthRange"
FROM
(SELECT target.*,
(target.date - INTERVAL '1 week')::DATE AS "previousDate",
prev."rawPiB" as "prevRawPiB",
target."rawPiB" - prev."rawPiB" as "diffRawPiB",
prev."qualityPiB" as "prevQualityPiB",
target."qualityPiB" - prev."qualityPiB" as "diffQualityPiB",
(target."qualityPiB" - prev."qualityPiB") / NULLIF(prev."qualityPiB", 0) as "qualityGrowthRate",
CASE
WHEN prev."qualityPiB" = 0 AND target."qualityPiB" > 0 then TRUE
ELSE FALSE
END AS "new"
FROM filled_power_by_week target
INNER JOIN filled_power_by_week prev
ON (target.date - INTERVAL '1 week')::DATE = prev.date AND
target.provider = prev.provider
) AS data
ORDER BY "providerNum", date;
CREATE MATERIALIZED VIEW week_over_week_matrix AS
SELECT "date",
"previousDate",
"qualityBucket",
"growthRange",
COUNT(*),
SUM("diffQualityPiB") AS "qualityPowerDiffPiB"
FROM week_over_week
WHERE "growthRange" IS NOT NULL
GROUP BY "date", "previousDate", "qualityBucket", "growthRange"
ORDER BY "date", "qualityBucket", "growthRange";
CREATE MATERIALIZED VIEW weekly_growth AS
SELECT target."qualityBucket",
target."date",
target."previousDate",
SUM(target."qualityPowerDiffPiB") AS "qualityPowerGrowth",
COALESCE(SUM(previous.count), 0) AS "previousCount",
SUM(target.count) AS count,
SUM(target.count) - COALESCE(SUM(previous.count), 0) AS "countGrowth"
FROM week_over_week_matrix AS target
LEFT JOIN week_over_week_matrix AS previous
ON target."previousDate" = previous.date
AND target."qualityBucket" = previous."qualityBucket"
AND target."growthRange" = previous."growthRange"
GROUP BY target."date", target."previousDate", target."qualityBucket"
ORDER BY "qualityBucket", "date";
CREATE MATERIALIZED VIEW weekly_percentages AS
SELECT
week_over_week_matrix.date,
week_over_week_matrix."qualityBucket",
"growthRange",
count,
total,
count / total * 100 AS percentage
FROM week_over_week_matrix
INNER JOIN (
SELECT date, "qualityBucket", SUM(count) AS total FROM week_over_week_matrix
GROUP by date, "qualityBucket"
ORDER BY date, "qualityBucket"
) AS totals
ON week_over_week_matrix.date = totals.date
AND week_over_week_matrix."qualityBucket" = totals."qualityBucket";
SELECT * FROM weekly_percentages;
DROP TABLE IF EXISTS year_quarters;
CREATE TABLE year_quarters AS
SELECT DISTINCT "yearQuarter"
FROM quarter_over_quarter_matrix
ORDER BY "yearQuarter";
SELECT * FROM year_quarters;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment