create-view.sql

-- Create project_instant_power_view materialized view
DROP MATERIALIZED VIEW IF EXISTS public.project_instant_power_view;

CREATE MATERIALIZED VIEW IF NOT EXISTS public.project_instant_power_view AS
SELECT
  innerview."projectId",
  ROUND(CAST(innerview."totalPower" as NUMERIC), 2) as "totalPower",
  rank() OVER (
    ORDER BY
      innerview."totalPower" DESC
  ) AS "powerRank"
FROM
  (
    SELECT
      project.id AS "projectId",
      CASE
        WHEN (project.verified = true OR project."isGivbackEligible" = true) AND project."statusId" = 5 THEN COALESCE(sum(pp."boostedPower"), 0 :: double precision)
        ELSE 0 :: double precision
      END AS "totalPower"
    FROM
      project
      LEFT JOIN (
        SELECT
          "powerBoosting"."projectId",
          sum("instantPowerBalance".balance * "powerBoosting".percentage :: double precision / 100 :: double precision) AS "boostedPower",
          now() AS "updateTime"
        FROM
          instant_power_balance "instantPowerBalance"
          JOIN power_boosting "powerBoosting" ON "powerBoosting"."userId" = "instantPowerBalance"."userId"
        GROUP BY
          "powerBoosting"."projectId"
      ) pp ON pp."projectId" = project.id
    GROUP BY
      project.id
  ) innerview
ORDER BY
  innerview."totalPower" DESC WITH DATA;

-- Create indexes
CREATE UNIQUE INDEX IF NOT EXISTS idx_project_instant_power_view_unique ON public.project_instant_power_view ("projectId");
CREATE INDEX IF NOT EXISTS project_instant_power_view_project_id ON public.project_instant_power_view USING hash ("projectId") TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS project_instant_power_view_total_power ON public.project_instant_power_view USING btree ("totalPower" DESC) TABLESPACE pg_default;