CREATE OR REPLACE VIEW compute_optimizer_lambda_options AS ( SELECT TRY(date_parse(lastrefreshtimestamp_utc, '%Y-%m-%d %H:%i:%s')) lastrefreshtimestamp_utc , accountid accountid , functionarn arn , TRY("split_part"(functionarn, ':', 4)) region , TRY("split_part"(functionarn, ':', 3)) service , TRY("split_part"(functionarn, ':', 7)) name , 'lambda' module , 'lambda' recommendationsourcetype , finding finding , CONCAT( (CASE WHEN (findingreasoncodes_ismemoryoverprovisioned = 'true') THEN 'Memory-Over ' ELSE '' END), (CASE WHEN (findingreasoncodes_ismemoryunderprovisioned = 'true') THEN 'Memory-Under ' ELSE '' END), (CASE WHEN (findingreasoncodes_isinsufficientdata = 'true') THEN 'InsufficientData ' ELSE '' END), (CASE WHEN (findingreasoncodes_isinconclusive = 'true') THEN 'Inconclusive ' ELSE '' END) ) reason , lookbackperiodindays lookbackperiodindays , currentperformancerisk as currentperformancerisk , cast(NULL as varchar(1)) errorcode , cast(NULL as varchar(1)) errormessage , CONCAT( numberofinvocations , ';', current_costtotal , ';', currentconfiguration_timeout, ';', functionversion, ';') ressouce_details , CONCAT( utilizationmetrics_durationaverage, ';', utilizationmetrics_durationmaximum, ';', utilizationmetrics_memoryaverage, ';', utilizationmetrics_memorymaximum, ';' ) utilizationmetrics , 'Current' option_name , cast(NULL as varchar(1)) option_from , cast(NULL as varchar(1)) option_to , recommendationoptions_1_estimatedmonthlysavings_currency currency , try_cast(NULL as double) as monthlyprice , try_cast(NULL as double) as hourlyprice , 0E0 estimatedmonthlysavings_value , 0E0 estimatedmonthly_ondemand_cost_change , GREATEST( CASE WHEN recommendationoptions_1_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_1_estimatedmonthlysavings_value as double) ELSE 0E0 END, CASE WHEN recommendationoptions_2_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_2_estimatedmonthlysavings_value as double) ELSE 0E0 END, CASE WHEN recommendationoptions_3_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_3_estimatedmonthlysavings_value as double) ELSE 0E0 END ) as max_estimatedmonthlysavings_value_very_low , GREATEST( CASE WHEN recommendationoptions_1_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_1_estimatedmonthlysavings_value as double) ELSE 0E0 END, CASE WHEN recommendationoptions_2_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_2_estimatedmonthlysavings_value as double) ELSE 0E0 END, CASE WHEN recommendationoptions_3_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_3_estimatedmonthlysavings_value as double) ELSE 0E0 END ) as max_estimatedmonthlysavings_value_low , GREATEST( CASE WHEN recommendationoptions_1_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_1_estimatedmonthlysavings_value as double) ELSE 0E0 END, CASE WHEN recommendationoptions_2_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_2_estimatedmonthlysavings_value as double) ELSE 0E0 END, CASE WHEN recommendationoptions_3_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_3_estimatedmonthlysavings_value as double) ELSE 0E0 END ) as max_estimatedmonthlysavings_value_medium , CONCAT( currentperformancerisk, ';', -- '-' currentconfiguration_memorysize, ';', current_costaverage, ';', current_costaverage, ';', utilizationmetrics_durationaverage, ';', '', ';', utilizationmetrics_durationmaximum, ';' ) option_details , cast('' as varchar(1)) as tags FROM compute_optimizer_lambda_lines WHERE (functionarn LIKE '%arn:%') UNION SELECT TRY(date_parse(lastrefreshtimestamp_utc, '%Y-%m-%d %H:%i:%s')) lastrefreshtimestamp_utc , accountid accountid , functionarn arn , TRY("split_part"(functionarn, ':', 4)) region , TRY("split_part"(functionarn, ':', 3)) service , TRY("split_part"(functionarn, ':', 7)) name , 'lambda' module , 'lambda' recommendationsourcetype , finding finding , cast(NULL as varchar(1)) reason , lookbackperiodindays lookbackperiodindays , currentperformancerisk as currentperformancerisk , cast(NULL as varchar(1)) errorcode , cast(NULL as varchar(1)) errormessage , cast(NULL as varchar(1)) ressouce_details , cast(NULL as varchar(1)) utilizationmetrics , 'Option 1' as option_name , cast(NULL as varchar(1)) option_from , cast(NULL as varchar(1)) option_to , recommendationoptions_1_estimatedmonthlysavings_currency currency , cast(NULL as double) monthlyprice , cast(NULL as double) hourlyprice , try_cast(recommendationoptions_1_estimatedmonthlysavings_value as double) as estimatedmonthlysavings_value , try_cast(recommendationoptions_1_estimatedmonthlysavings_value as double) as estimatedmonthly_ondemand_cost_change , GREATEST( CASE WHEN recommendationoptions_1_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_1_estimatedmonthlysavings_value as double) ELSE 0E0 END, CASE WHEN recommendationoptions_2_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_2_estimatedmonthlysavings_value as double) ELSE 0E0 END, CASE WHEN recommendationoptions_3_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_3_estimatedmonthlysavings_value as double) ELSE 0E0 END ) as max_estimatedmonthlysavings_value_very_low , GREATEST( CASE WHEN recommendationoptions_1_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_1_estimatedmonthlysavings_value as double) ELSE 0E0 END, CASE WHEN recommendationoptions_2_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_2_estimatedmonthlysavings_value as double) ELSE 0E0 END, CASE WHEN recommendationoptions_3_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_3_estimatedmonthlysavings_value as double) ELSE 0E0 END ) as max_estimatedmonthlysavings_value_low , GREATEST( CASE WHEN recommendationoptions_1_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_1_estimatedmonthlysavings_value as double) ELSE 0E0 END, CASE WHEN recommendationoptions_2_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_2_estimatedmonthlysavings_value as double) ELSE 0E0 END, CASE WHEN recommendationoptions_3_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_3_estimatedmonthlysavings_value as double) ELSE 0E0 END ) as max_estimatedmonthlysavings_value_medium , CONCAT( '', ';', --no performance risk recommendationoptions_1_configuration_memorysize, ';', recommendationoptions_1_costlow, ';', recommendationoptions_1_costhigh, ';', recommendationoptions_1_projectedutilizationmetrics_durationexpected, recommendationoptions_1_projectedutilizationmetrics_durationlowerbound, ';', recommendationoptions_1_projectedutilizationmetrics_durationupperbound, ';' ) option_details , cast('' as varchar(1)) as tags FROM compute_optimizer_lambda_lines WHERE functionarn LIKE '%arn:%' UNION SELECT TRY(date_parse(lastrefreshtimestamp_utc, '%Y-%m-%d %H:%i:%s')) lastrefreshtimestamp_utc , accountid accountid , functionarn arn , TRY("split_part"(functionarn, ':', 4)) region , TRY("split_part"(functionarn, ':', 3)) service , TRY("split_part"(functionarn, ':', 7)) name , 'lambda' module , 'lambda' recommendationsourcetype , finding finding , cast(NULL as varchar(1)) reason , lookbackperiodindays lookbackperiodindays , currentperformancerisk as currentperformancerisk , cast(NULL as varchar(1)) errorcode , cast(NULL as varchar(1)) errormessage , cast(NULL as varchar(1)) ressouce_details , cast(NULL as varchar(1)) utilizationmetrics , 'Option 2' as option_name , cast(NULL as varchar(1)) option_from , cast(NULL as varchar(1)) option_to , recommendationoptions_2_estimatedmonthlysavings_currency currency , cast(NULL as double) monthlyprice , cast(NULL as double) hourlyprice , try_cast(recommendationoptions_2_estimatedmonthlysavings_value as double) as estimatedmonthlysavings_value , try_cast(recommendationoptions_2_estimatedmonthlysavings_value as double) as estimatedmonthly_ondemand_cost_change , GREATEST( CASE WHEN recommendationoptions_1_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_1_estimatedmonthlysavings_value as double) ELSE 0E0 END, CASE WHEN recommendationoptions_2_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_2_estimatedmonthlysavings_value as double) ELSE 0E0 END, CASE WHEN recommendationoptions_3_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_3_estimatedmonthlysavings_value as double) ELSE 0E0 END ) as max_estimatedmonthlysavings_value_very_low , GREATEST( CASE WHEN recommendationoptions_1_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_1_estimatedmonthlysavings_value as double) ELSE 0E0 END, CASE WHEN recommendationoptions_2_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_2_estimatedmonthlysavings_value as double) ELSE 0E0 END, CASE WHEN recommendationoptions_3_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_3_estimatedmonthlysavings_value as double) ELSE 0E0 END ) as max_estimatedmonthlysavings_value_low , GREATEST( CASE WHEN recommendationoptions_1_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_1_estimatedmonthlysavings_value as double) ELSE 0E0 END, CASE WHEN recommendationoptions_2_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_2_estimatedmonthlysavings_value as double) ELSE 0E0 END, CASE WHEN recommendationoptions_3_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_3_estimatedmonthlysavings_value as double) ELSE 0E0 END ) as max_estimatedmonthlysavings_value_medium , CONCAT( '', ';', --no performance risk recommendationoptions_2_configuration_memorysize, ';', recommendationoptions_2_costlow, ';', recommendationoptions_2_costhigh, ';', recommendationoptions_2_projectedutilizationmetrics_durationexpected, recommendationoptions_2_projectedutilizationmetrics_durationlowerbound, ';', recommendationoptions_2_projectedutilizationmetrics_durationupperbound, ';' ) option_details , cast('' as varchar(1)) as tags FROM compute_optimizer_lambda_lines WHERE functionarn LIKE '%arn:%' AND recommendationoptions_2_estimatedmonthlysavings_currency <> '' UNION SELECT TRY(date_parse(lastrefreshtimestamp_utc, '%Y-%m-%d %H:%i:%s')) lastrefreshtimestamp_utc , accountid accountid , functionarn arn , TRY("split_part"(functionarn, ':', 4)) region , TRY("split_part"(functionarn, ':', 3)) service , TRY("split_part"(functionarn, ':', 7)) name , 'lambda' module , 'lambda' recommendationsourcetype , finding finding , cast(NULL as varchar(1)) reason , lookbackperiodindays lookbackperiodindays , currentperformancerisk as currentperformancerisk , cast(NULL as varchar(1)) errorcode , cast(NULL as varchar(1)) errormessage , cast(NULL as varchar(1)) ressouce_details , cast(NULL as varchar(1)) utilizationmetrics , 'Option 3' as option_name , cast(NULL as varchar(1)) option_from , cast(NULL as varchar(1)) option_to , recommendationoptions_3_estimatedmonthlysavings_currency currency , cast(NULL as double) monthlyprice , cast(NULL as double) hourlyprice , try_cast(recommendationoptions_3_estimatedmonthlysavings_value as double) as estimatedmonthlysavings_value , try_cast(recommendationoptions_3_estimatedmonthlysavings_value as double) as estimatedmonthly_ondemand_cost_change , GREATEST( CASE WHEN recommendationoptions_1_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_1_estimatedmonthlysavings_value as double) ELSE 0E0 END, CASE WHEN recommendationoptions_2_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_2_estimatedmonthlysavings_value as double) ELSE 0E0 END, CASE WHEN recommendationoptions_3_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_3_estimatedmonthlysavings_value as double) ELSE 0E0 END ) as max_estimatedmonthlysavings_value_very_low , GREATEST( CASE WHEN recommendationoptions_1_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_1_estimatedmonthlysavings_value as double) ELSE 0E0 END, CASE WHEN recommendationoptions_2_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_2_estimatedmonthlysavings_value as double) ELSE 0E0 END, CASE WHEN recommendationoptions_3_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_3_estimatedmonthlysavings_value as double) ELSE 0E0 END ) as max_estimatedmonthlysavings_value_low , GREATEST( CASE WHEN recommendationoptions_1_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_1_estimatedmonthlysavings_value as double) ELSE 0E0 END, CASE WHEN recommendationoptions_2_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_2_estimatedmonthlysavings_value as double) ELSE 0E0 END, CASE WHEN recommendationoptions_3_estimatedmonthlysavings_currency != '' THEN TRY_CAST(recommendationoptions_3_estimatedmonthlysavings_value as double) ELSE 0E0 END ) as max_estimatedmonthlysavings_value_medium , CONCAT( '', ';', --no performance risk recommendationoptions_3_configuration_memorysize, ';', recommendationoptions_3_costlow, ';', recommendationoptions_3_costhigh, ';', recommendationoptions_3_projectedutilizationmetrics_durationexpected, recommendationoptions_3_projectedutilizationmetrics_durationlowerbound, ';', recommendationoptions_3_projectedutilizationmetrics_durationupperbound, ';' ) option_details , cast(NULL as varchar(1)) as tags FROM compute_optimizer_lambda_lines WHERE functionarn LIKE '%arn:%' AND recommendationoptions_3_estimatedmonthlysavings_currency <> '' )