Spaces:
Running on Zero
Running on Zero
File size: 3,193 Bytes
3574fa8 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 | drop view if exists public.analytics_generation_signals;
drop view if exists public.analytics_download_events_inferred;
drop view if exists public.analytics_generation_outcomes;
create view public.analytics_generation_outcomes as
select
g.generation_id,
g.timestamp as generation_timestamp,
g.source,
g.caller_app,
g.pipeline,
g.analytics_opt_in,
g.status,
g.error_bucket,
exists (
select 1
from public.analytics_download_events d
where d.generation_id = g.generation_id
) as has_download
from public.analytics_generation_events g;
create view public.analytics_download_events_inferred as
select
d.id,
d.generation_id,
d.timestamp,
d.product,
d.caller_app,
d.source,
d.pipeline,
d.tool_name,
d.analytics_opt_in,
d.format,
d.anonymous_id,
d.qr_payload_full,
d.seed,
d.created_at,
case
when d.tool_name like '%_standard' then 'standard'
when d.tool_name like '%_artistic' then 'artistic'
when d.source = 'mcp' and d.tool_name like '%_1' then 'standard'
when d.source = 'mcp' and d.tool_name not like '%_1' then 'artistic'
else d.pipeline
end as pipeline_inferred
from public.analytics_download_events d;
create view public.analytics_generation_signals as
with ordered_generations as (
select
g.id,
g.generation_id,
g.timestamp,
g.product,
g.caller_app,
g.source,
g.pipeline,
g.tool_name,
g.analytics_opt_in,
g.status,
g.error_bucket,
g.anonymous_id,
g.prompt_full,
g.qr_payload_full,
g.settings_full,
g.created_at,
lead(g.timestamp) over (
partition by g.source, g.anonymous_id, g.pipeline, coalesce(g.caller_app, '')
order by g.timestamp
) as next_generation_timestamp
from public.analytics_generation_events g
), generation_with_downloads as (
select
g.generation_id,
g.timestamp,
g.product,
g.caller_app,
g.source,
g.pipeline,
g.analytics_opt_in,
g.status,
g.error_bucket,
g.anonymous_id,
g.prompt_full,
g.qr_payload_full,
g.settings_full,
g.next_generation_timestamp,
exists (
select 1
from public.analytics_download_events d
where d.source = g.source
and coalesce(d.caller_app, '') = coalesce(g.caller_app, '')
and d.anonymous_id = g.anonymous_id
and d.timestamp >= g.timestamp
and d.timestamp <= g.timestamp + interval '10 minutes'
) as has_download_within_10m
from ordered_generations g
)
select
generation_id,
timestamp,
product,
caller_app,
source,
pipeline,
analytics_opt_in,
status,
error_bucket,
anonymous_id,
prompt_full,
qr_payload_full,
settings_full,
has_download_within_10m,
next_generation_timestamp,
case
when error_bucket = 'infra_limited' then 'infra_limited'
when status = 'success' and has_download_within_10m then 'happy'
when status = 'success'
and next_generation_timestamp is not null
and next_generation_timestamp <= timestamp + interval '10 minutes'
and not has_download_within_10m then 'unhappy'
when status = 'error' then 'error'
else 'neutral'
end as outcome_signal
from generation_with_downloads;
|