with spans as
  (select
     spanid,
     traceid,
     clientzone
   from
     zipkin.zipkin_span_index
   where
     ds = '2020-01-23'
     and clientservice like 'gizmoduck/prod/gizmoduck/%'
  ),
annotations as
(
  select
      spanid,
      traceid,
      Max(stringV) as gizmoduck_method,
      Max(intV) as query_width
   from
      zipkin.zipkin_flat_annotation
   where
      ds = '2020-01-23' and
      endpoint.servicenamejob = 'gizmoduck' and
      (label='query_width' or label='method')
    group by spanid, traceid
)

select
  tfeuri as enpdoint,
  clientzone,
  gizmoduck_method,
  avg(query_width) as avg_query_width,
  (count(DISTINCT(t2.spanid))/count(DISTINCT(t1.traceid))) as rpc_per_trace,
  count(DISTINCT(t1.traceid)) as num_traces
from
  zipkin.zipkin_trace_index t1
    join spans t2 on t1.traceid = t2.traceid
    join annotations t3 on t1.traceid = t3.traceid
where
    t1.ds = '2020-01-23'
    and tfehost = 'api.twitter.com'
    and isprodonly = true
group by
  tfeuri,gizmoduck_method,clientzone
order by
  num_traces desc
limit 20