with DATE_BUCKET as (
select cal_date
FROM UNNEST(GENERATE_DATE_ARRAY(CAST(parse_date('%Y%m%d','20240601') as date), CAST(parse_date('%Y%m%d','20241111') as date), INTERVAL 1 DAY)) as cal_date
)
, RESERVATION_CHANGES as (
select *
from (
SELECT datetime(change_timestamp, ('PST8PDT')) change_timestamp_org,
date(date_trunc(datetime(change_timestamp, ('PST8PDT')), DAY)) as change_date,
datetime(IFNULL(LAG(a.change_timestamp) OVER (partition by a.reservation_name ORDER BY a.change_timestamp DESC),CURRENT_TIMESTAMP()), ('PST8PDT')) AS end_change_timestamp_org,
date(datetime(IFNULL(LAG(a.change_timestamp) OVER (partition by a.reservation_name ORDER BY a.change_timestamp DESC),CURRENT_TIMESTAMP()), ('PST8PDT'))) AS end_change_date,
project_id,
reservation_name,
ignore_idle_slots,
action,
slot_capacity,
autoscale.current_slots,
autoscale.max_slots,
user_email,
edition
FROM `region-US`.INFORMATION_SCHEMA.RESERVATION_CHANGES a
)
where 1=1
and action NOT IN ('DELETE')
)
, DATE_BUCKET_RESERVATION_CHANGES as (
select cal_date
, case when cal_date > change_date then CAST(parse_timestamp('%Y-%m-%d %H:%M:%S', cal_date || ' 00:00:00') AS datetime)
else change_timestamp_org
end as change_timestamp_new
, case when cal_date < end_change_date then CAST(parse_timestamp('%Y-%m-%d %H:%M:%S', date_add(cal_date, interval 1 day) || ' 00:00:00') AS datetime)
else end_change_timestamp_org
end as end_change_timestamp_new
, R.project_id
, R.reservation_name
, R.ignore_idle_slots
, R.action
, R.slot_capacity
, R.current_slots
, R.max_slots
, R.user_email
, R.edition
from DATE_BUCKET T
join RESERVATION_CHANGES R
on R.change_date <> R.end_change_date
and cal_date >= R.change_date
and cal_date <= R.end_change_date
union all
select cal_date
, change_timestamp_org as change_timestamp_new
, end_change_timestamp_org as end_change_timestamp_new
, R.project_id
, R.reservation_name
, R.ignore_idle_slots
, R.action
, R.slot_capacity
, R.current_slots
, R.max_slots
, R.user_email
, R.edition
from DATE_BUCKET T
join RESERVATION_CHANGES R
on R.change_date = R.end_change_date
and cal_date = R.change_date
)
, tb_main as (
select change_timestamp_new
, end_change_timestamp_new
, TIMESTAMP_DIFF(end_change_timestamp_new,change_timestamp_new,SECOND) as diff
, ceil(
(UNIX_MICROS(cast(end_change_timestamp_new as timestamp)) - UNIX_MICROS(cast(change_timestamp_new as timestamp))) / 1000 / 1000
) as diff_mill
-- , TIMESTAMP_DIFF(end_change_timestamp_new,change_timestamp_new,millisecond) as diff_mill
-- , TIMESTAMP_DIFF(end_change_timestamp_new,change_timestamp_new,MICROSECOND) as diff_micro
, project_id
, reservation_name
, ignore_idle_slots
, action
, slot_capacity
, current_slots
, max_slots
, user_email
, edition
, CASE WHEN edition='STANDARD' THEN 0.04/60/60
WHEN edition='ENTERPRISE' THEN 0.06/60/60 END PAYG_PRICING
from DATE_BUCKET_RESERVATION_CHANGES
where 1=1
-- and edition = 'STANDARD'
order by cal_date desc, change_timestamp_new desc
)
SELECT project_id
, edition
, reservation_name
, date(change_timestamp_new) as change_date
-- , min(change_timestamp_new) change_timestamp_new
-- , max(end_change_timestamp_new) end_change_timestamp_new
, sum(slot_capacity*diff)*MAX(PAYG_PRICING) BASE_PRICING
, sum(current_slots*diff)*MAX(PAYG_PRICING) AUTOSCALING_PRICING
, sum(current_slots*diff)
, sum(current_slots*diff_mill)
FROM tb_main
GROUP BY project_id, edition, reservation_name, change_date
