SQL queries for Provider Power by Quarter

create_tables.sql

CREATE TABLE synthetic_regions (
  provider VARCHAR(255),
  region VARCHAR(255),
  "numRegions" INTEGER,
  delegate VARCHAR(255));

indexes.sql

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);

month_by_month_matrix.sql

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;

monthly_sample_dates.sql

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;

quarter_by_quarter_matrix.sql

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.sql

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;

week_by_week_matrix.sql

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;

year_quarters.sql

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;