-- startDate and endDate represents period for which potential savings are being calculated
SET startDate = (SELECT current_date - 7);
SET endDate = (SELECT current_date);

WITH warehouse_credits_map AS (
    SELECT * FROM (
        VALUES
        ('X-Small', 'STANDARD', 1),
        ('Small', 'STANDARD', 2),
        ('Medium', 'STANDARD', 4),
        ('Large', 'STANDARD', 8),
        ('X-Large', 'STANDARD', 16),
        ('2X-Large', 'STANDARD', 32),
        ('3X-Large', 'STANDARD', 64),
        ('4X-Large', 'STANDARD', 128),
        ('5X-Large', 'STANDARD', 256),
        ('6X-Large', 'STANDARD', 512),
        ('Medium', 'SNOWPARK-OPTIMIZED', 6),
        ('Large', 'SNOWPARK-OPTIMIZED', 12),
        ('X-Large', 'SNOWPARK-OPTIMIZED', 24),
        ('2X-Large', 'SNOWPARK-OPTIMIZED', 48),
        ('3X-Large', 'SNOWPARK-OPTIMIZED', 96),
        ('4X-Large', 'SNOWPARK-OPTIMIZED', 192),
        ('5X-Large', 'SNOWPARK-OPTIMIZED', 384),
        ('6X-Large', 'SNOWPARK-OPTIMIZED', 768)
    ) AS t (warehouse_size, warehouse_type, credits_per_hour)
),

stop_threshold as (
    SELECT MAX(timestamp) AS timestamp
    FROM snowflake.account_usage.warehouse_events_history
),

warehouse_cluster_status_started AS (
    SELECT
        warehouse_id,
        warehouse_name,
        cluster_number,
        timestamp AS start_timestamp,
        event_name,
        event_reason,
        user_name,
        role_name,
        query_id 
    from snowflake.account_usage.warehouse_events_history
    WHERE 
        event_name IN ('RESUME_CLUSTER', 'SUSPEND_CLUSTER') 
        AND event_state = 'STARTED'
        AND timestamp::date BETWEEN $startDate AND $endDate
),
warehouse_cluster_status_consistent AS (
    SELECT 
        warehouse_id,
        event_name,
        timestamp AS complete_timestamp
    from snowflake.account_usage.warehouse_events_history
    WHERE 
        event_name = 'WAREHOUSE_CONSISTENT' 
        AND event_state = 'COMPLETED'
        AND timestamp::date BETWEEN $startDate AND $endDate
),
warehouse_cluster_status_completed AS (
    SELECT 
        wcss.*,
        wcsc.complete_timestamp
    FROM 
        warehouse_cluster_status_started wcss
    JOIN 
        warehouse_cluster_status_consistent wcsc
    ON
        wcss.warehouse_id = wcsc.warehouse_id
        AND wcsc.complete_timestamp >= wcss.start_timestamp
    QUALIFY 
        ROW_NUMBER() OVER (PARTITION BY wcss.start_timestamp ORDER BY wcsc.complete_timestamp) = 1
),
warehouse_cluster_status_base as (
    select
        warehouse_id,
        warehouse_name,
        cluster_number,
        timestamp as valid_from,
        lead(timestamp) over (partition by warehouse_id, cluster_number order by timestamp asc) as valid_to,
        event_name = 'RESUME_CLUSTER' as is_active,
        event_reason,
        user_name,
        role_name,
        query_id
    from snowflake.account_usage.warehouse_events_history
    where
        event_name in ('RESUME_CLUSTER', 'SUSPEND_CLUSTER')
        and event_state = 'COMPLETED'
        AND timestamp::date BETWEEN $startDate AND $endDate

    UNION ALL

    select 
        warehouse_id,
        warehouse_name,
        cluster_number,
        complete_timestamp as valid_from,
        lead(complete_timestamp) over (partition by warehouse_id, cluster_number order by complete_timestamp asc) as valid_to,
        event_name = 'RESUME_CLUSTER' as is_active,
        event_reason,
        user_name,
        role_name,
        query_id
    from warehouse_cluster_status_completed
),

warehouse_cluster_status as (
    select
        warehouse_cluster_status_base.warehouse_id,
        warehouse_cluster_status_base.warehouse_name,
        warehouse_cluster_status_base.cluster_number,
        warehouse_cluster_status_base.is_active,
        warehouse_cluster_status_base.valid_from,
        coalesce(warehouse_cluster_status_base.valid_to, stop_threshold.timestamp) as valid_to,
        warehouse_cluster_status_base.event_reason,
        warehouse_cluster_status_base.user_name,
        warehouse_cluster_status_base.role_name,
        warehouse_cluster_status_base.query_id
    from warehouse_cluster_status_base
    cross join stop_threshold
),
warehouse_cluster_status_enriched AS (
    SELECT *, 
    LAG(valid_from, 1) OVER (PARTITION BY warehouse_id, cluster_number ORDER BY valid_from) AS previous_valid_from
    FROM warehouse_cluster_status
    WHERE valid_from::date BETWEEN $startDate AND $endDate
),
query_history_enriched AS (
    SELECT
        query_id, 
        TIMEADD('milliseconds', queued_overload_time + compilation_time + queued_provisioning_time + queued_repair_time + list_external_files_time, start_time) AS act_start_time,
        execution_time,
        TIMEADD('milliseconds', execution_time, act_start_time) AS act_end_time,
        warehouse_id,
        warehouse_name,
        cluster_number,
        warehouse_type,
        warehouse_size
    FROM snowflake.account_usage.query_history
    WHERE
        warehouse_size IS NOT NULL AND warehouse_name IS NOT NULL
        AND 
        act_end_time::date BETWEEN $startDate AND $endDate
),
ranked_queries AS (
    SELECT
        query_id,
        act_start_time,
        act_end_time,
        warehouse_id,
        cluster_number,
        warehouse_size,
        warehouse_type,
        MIN(act_start_time) OVER (PARTITION BY warehouse_id, cluster_number ORDER BY act_end_time ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS latest_start_time_after_end
    FROM query_history_enriched
),
savings_info AS (
    SELECT
        ranked_queries.query_id,
        act_start_time,
        act_end_time,
        ranked_queries.warehouse_id,
        ranked_queries.cluster_number,
        latest_start_time_after_end,
        warehouse_size,
        warehouse_type,
        is_active,
        valid_from,
        previous_valid_from,
        valid_to,
        event_reason,
        user_name,
        role_name
    FROM ranked_queries
    JOIN warehouse_cluster_status_enriched ON warehouse_cluster_status_enriched.warehouse_id = ranked_queries.warehouse_id
        AND warehouse_cluster_status_enriched.cluster_number = ranked_queries.cluster_number
        AND ranked_queries.act_end_time::date = warehouse_cluster_status_enriched.valid_from::date
        AND (warehouse_cluster_status_enriched.valid_from BETWEEN ranked_queries.act_end_time AND ranked_queries.latest_start_time_after_end)
    WHERE
        latest_start_time_after_end > act_end_time
        AND is_active = FALSE
        AND valid_from::date BETWEEN $startDate AND $endDate
),
enriched_savings_info AS (
    SELECT
        savings_info.*,
        warehouse_credits_map.credits_per_hour / 3600 AS credits_per_second,
    FROM savings_info
    INNER JOIN warehouse_credits_map ON savings_info.warehouse_size = warehouse_credits_map.warehouse_size
        AND savings_info.warehouse_type = warehouse_credits_map.warehouse_type
),
savings_data AS (
    SELECT
        valid_from::date AS DATE,
        sum(credits_per_second * greatest((TIMEDIFF('seconds', greatest(previous_valid_from, act_end_time), valid_from) - greatest((60 - TIMEDIFF('seconds', previous_valid_from, greatest(previous_valid_from, act_end_time)) ), 0) ), 0)) AS potential_credit_savings
	FROM enriched_savings_info
    GROUP BY 1
)
SELECT SUM(potential_credit_savings) AS potential_credit_savings_weekly,
potential_credit_savings_weekly* 365/(DATEDIFF('day', $startDate, $endDate)+1) AS potential_credit_savings_annually, potential_credit_savings_annually * 3.30 AS dollar_savings_annually FROM