public.stg_tepco_demand

CREATE VIEW stg_tepco_demand AS (
 WITH deduplicated AS (
         SELECT raw_tepco_demand.id,
            raw_tepco_demand.date_str,
            raw_tepco_demand.time_str,
            raw_tepco_demand.demand_mw_str,
            raw_tepco_demand.supply_capacity_mw_str,
            raw_tepco_demand.usage_pct_str,
            raw_tepco_demand.loaded_at,
            raw_tepco_demand.forecast_mw_str,
            row_number() OVER (PARTITION BY raw_tepco_demand.date_str, raw_tepco_demand.time_str ORDER BY raw_tepco_demand.loaded_at DESC) AS row_num
           FROM raw_tepco_demand
          WHERE (raw_tepco_demand.demand_mw_str IS NOT NULL)
        )
 SELECT deduplicated.id,
    to_date(deduplicated.date_str, 'YYYY/MM/DD'::text) AS demand_date,
    (deduplicated.time_str)::time without time zone AS demand_time,
    ((deduplicated.demand_mw_str)::numeric * (10)::numeric) AS demand_mw,
        CASE
            WHEN ((deduplicated.forecast_mw_str IS NOT NULL) AND (deduplicated.forecast_mw_str <> ''::text)) THEN ((deduplicated.forecast_mw_str)::numeric * (10)::numeric)
            ELSE NULL::numeric
        END AS forecast_mw,
    ((deduplicated.supply_capacity_mw_str)::numeric * (10)::numeric) AS supply_capacity_mw,
    (deduplicated.usage_pct_str)::numeric AS usage_pct,
    deduplicated.loaded_at
   FROM deduplicated
  WHERE (deduplicated.row_num = 1)
)