Pages Data Dictionary | Yext Hitchhikers Platform

select round(avg(build_duration_seconds), 2) -- Optionally round the build time to nearest 100th of a second
from prod_pages.public.builds
where status = 'SUCCESS'
and site_id = 12345

and build_duration_seconds is not null;

select
    status,
    avg(artifact_size) 
from prod_pages.public.builds
where status is not null
and artifact_size is not null;

select 
    build_id,
    site_id,
    status,
    artifact_size,
    queued_at_timestamp, 
    started_at_timestamp,
    completion_timestamp,
    build_duration_seconds
from prod_sharing.pages.builds

select 
    site_id, 
    count(*) as generations
from prod_sharing.pages.generations
where completed_timestamp > dateadd(day, -90, current_date)
and generation_type = 'UPDATE'
and is_production
and status = 'SUCCESS'
group by 1 order by 2 desc;

select 
    businesses.business_id,
    name,
    sites.site_id,
    site_name,
    deploy_id,
    generation_type,
    is_production,
    pages_generated,
    duration_seconds, 
    start_timestamp,
    build_id
from prod_sharing.pages.generations
join prod_sharing.public.businesses using (business_id)
join prod_sharing.pages.sites using (site_id)
where completed_timestamp > dateadd(day, -365, current_date)
and status = 'FAILURE';

select 
    distinct event_type_name, 
    count(*) as function_invocations
from prod_sharing.pages.generation_function_invocations
group by 1 order by 2 desc;

select * 
from prod_sharing.pages.generation_function_invocations
where event_type_name = 'ON_URL_CHANGE'
and timestamp > dateadd(day, -90, current_date);

select 
    invocation_id,
    business_id,
    plugin_external_id,
    function_name,
    argument_json,
    error
from prod_sharing.pages.generation_function_invocations
join prod_sharing.public.function_invocations using (invocation_id)
where event_type_name = 'ON_PAGE_GENERATE'
and error is not null
and timestamp > dateadd(day, -90, current_date)

select 
    sites.creation_timestamp, 
    site_name,
    concat('https://yext.com/s/', businesses.business_id, '/yextsites/', site_id) as site_link,
    businesses.business_id, 
    name as account_name, 
    site_id, 
    sites.repo_uuid, 
    hostname, 
    published_deploy_id
from prod_pages.public.sites
join prod_platform.public.businesses using (business_id)
where creation_timestamp > dateadd(month, -6, current_date)

order by 1 desc;

select count(*)
from prod_sharing.pages.pages_errors 
join prod_pages.public.sites using (site_id)
where date(pages_errors.timestamp) = current_date();

select 
    timestamp,
    name,
    concat('https://yext.com/s/', businesses.business_id, '/yextsites/', pages_errors.site_id) as site_link,
    businesses.business_id,
    site_name,
    pages_errors.site_id,
    error_type,
    repo_uuid
from prod_sharing.pages.pages_errors 
join prod_platform.public.businesses using (business_id)
join prod_sharing.pages.sites using (site_id)
group by
    1,2,3,4,5,6,7,8
order by 1 desc;