athena_query_final.sql

INSERT INTO smart_hub_data_catalog.etl_tmp_output_parquet
WITH t1 AS (SELECT d.loc_id, d.ts, d.data.s_10 AS kwh, l.state, l.tz
            FROM smart_hub_data_catalog.smart_hub_data_parquet d
                     LEFT OUTER JOIN smart_hub_data_catalog.smart_hub_locations_parquet l ON d.loc_id = l.hash
            WHERE d.loc_id = 'b6a8d42425fde548'
              AND d.dt BETWEEN cast('2019-12-21' AS date) AND cast('2019-12-22' AS date)),
     t2 AS (SELECT at_timezone(from_unixtime(t1.ts, 'UTC'), t1.tz)                    AS ts,
                   date_format(at_timezone(from_unixtime(t1.ts, 'UTC'), t1.tz), '%H') AS rate_period,
                   m.description                                                      AS device,
                   m.location,
                   t1.loc_id,
                   t1.state,
                   t1.tz,
                   t1.kwh
            FROM t1
                     LEFT OUTER JOIN smart_hub_data_catalog.sensor_mappings_parquet m ON t1.loc_id = m.loc_id
            WHERE t1.loc_id = 'b6a8d42425fde548'
              AND m.state = t1.state
              AND m.description = (SELECT m2.description
                                   FROM smart_hub_data_catalog.sensor_mappings_parquet m2
                                   WHERE m2.loc_id = 'b6a8d42425fde548'
                                     AND m2.id = 's_10')),
     t3 AS (SELECT substr(r.to, 1, 2) AS rate_period, r.type, r.rate, r.year, r.month, r.state
            FROM smart_hub_data_catalog.electricity_rates_parquet r
            WHERE r.year BETWEEN cast(date_format(cast('2019-12-21' AS date), '%Y') AS integer)
                      AND cast(date_format(cast('2019-12-22' AS date), '%Y') AS integer))
SELECT replace(cast(t2.ts AS VARCHAR), concat(' ', t2.tz), '') AS ts,
       t2.device,
       t2.location,
       t3.type,
       t2.kwh,
       t3.rate                                                 AS cents_per_kwh,
       round(t2.kwh * t3.rate, 4)                              AS cost,
       t2.state,
       t2.loc_id
FROM t2
         LEFT OUTER JOIN t3 ON t2.rate_period = t3.rate_period
WHERE t3.state = t2.state
ORDER BY t2.ts, t2.device;