Query: codebuild_project_build_greater_then_90_days

with latest_codebuild_build as (
  select
    project_name,
    region,
    account_id,
    min(date_part('day', now() - end_time)) as build_time
  from
    aws_codebuild_build
  group by
    project_name,
    region,
    account_id
),
codebuild_projects as (
  select
    arn,
    name,
    region,
    account_id,
    title,
    tags,
    _ctx
  from
    aws_codebuild_project
  group by
    name,
    tags,
    arn,
    title,
    region,
    account_id,
    _ctx
)
select
  p.arn as resource,
  case
    when b.build_time is null then 'alarm'
    when b.build_time < 90 then 'ok'
    else 'alarm'
  end as status,
  case
    when b.build_time is null then p.title || ' has no builds.'
    else p.title || ' was build ' || build_time || ' day(s) before.'
  end as reason
  
  , p.region, p.account_id
from
  codebuild_projects as p
  left join latest_codebuild_build as b on p.name = b.project_name and p.region = b.region and p.account_id = b.account_id;