SELECT
  timestamp '2019-09-01 00:00:00.000' AS period_start,
  timestamp '2019-09-30 00:00:00.000' AS period_end,
  namespace,
  sum(pod_request_memory_byte_seconds / memory_units.resource_base_units / time_units.resource_base_units) as pod_request_memory,
  concat(memory_units.resource_unit, ' ', time_units.resource_unit, 's') AS pod_request_memory_units,
  sum(pod_request_memory_byte_seconds / price_sheet_memory_unit.resource_base_units / price_sheet_time_unit.resource_base_units * price_sheet.price) as cost,
  (price_sheet.price / (price_sheet_memory_unit.resource_base_units / memory_units.resource_base_units) / (price_sheet_time_unit.resource_base_units / time_units.resource_base_units)) AS price,
  upper(price_sheet.currency) as currency
FROM hive.metering.datasource_metering_chancez2_pod_memory_request_raw

-- specify how prices are defined in the price sheet
JOIN my_custom_price_sheet3 price_sheet
ON price_sheet.resource = 'memory'
AND price_sheet.resource_unit = 'gigabyte'
AND price_sheet.time_unit = 'hour'

-- desired memory units for conversions
JOIN unit_conversions memory_units
ON memory_units.resource = 'memory'
AND memory_units.resource_unit = 'gigabyte'

-- get the unit conversion from the price sheet unit to convert price book
-- memory units to desired units
JOIN unit_conversions price_sheet_memory_unit
ON price_sheet_memory_unit.resource = 'memory'
AND price_sheet_memory_unit.resource_unit = price_sheet.resource_unit

-- desired time units for conversions
JOIN unit_conversions time_units
ON time_units.resource = 'time'
AND time_units.resource_unit = 'hour'

-- get the unit conversion from the price sheet unit to convert price book time
-- units to desired units
JOIN unit_conversions price_sheet_time_unit
ON price_sheet_time_unit.resource = 'time'
AND price_sheet_time_unit.resource_unit = price_sheet.time_unit

WHERE "timestamp" >= timestamp '2019-09-01 00:00:00.000'
AND "timestamp" < timestamp '2019-09-30 00:00:00.000'
AND dt >= '2019-09-01'
AND dt <= '2019-09-30'
GROUP BY
    namespace,
    concat(memory_units.resource_unit, ' ', time_units.resource_unit, 's'),
    (price_sheet.price / (price_sheet_memory_unit.resource_base_units / memory_units.resource_base_units) / (price_sheet_time_unit.resource_base_units / time_units.resource_base_units)),
    price_sheet.currency
ORDER BY pod_request_memory DESC